Analyzing Data with Excel TablesWelcome to your Basic Training Series. I put this series together to provide my Jet Essentials clients access to the information you need to get started with reporting! Make sure to try out the practice exercises after you've watched all the videos! If you need help, I'm available at firstname.lastname@example.org.
Don't miss the follow up Basic Training series, sign up for our bi-monthly newsletter.
Do you use Tables?For many of my clients, data analysis starts the same way, regardless of whether they're in Accounting, Sales, AP, AR, or Inventory. First you find the data in your ERP (NAV, GP etc.) then you copy the data into Excel. From there, analysts will usually use custom formulas to create new measures across columns, or maybe add VLOOKUPs to add custom fields to the table.
Did you know Excel 2007 and later has a feature that allows you to build formatted "Tables" (Excel > Insert > Table) out of the data you copy into Excel? Older versions of Excel called it a "List". Advantages of using Excel Tables include the ability to add column filters, provide grand totals and add color formatting. If you're getting really fancy you can look at adding Slicers and Pivot Charts to create robust Dashboards.
Seeing Detail versus Summarizing DataIf you think of copying data straight out of a table in your ERP as a "Data Dump" or detail report, then using Excel's Pivot Table to aggregate by a specific field (ex. total sales grouped by Country) would be a "Summary Report."
Pivot tables may sound intimidating, but they are easy to use. Select your Excel Table, then insert a Pivot Table (Excel > Insert > Tables > Pivot Table). Now, Just drag and drop field names into a row or column. Then drop a Numeric field into the Values area to aggregate. It takes a little practice, but this opens up a wealth of new and dynamic analysis options.
Note: usually the default is to SUM value fields, but you can also COUNT, MIN, MAX and AVG.
Examples of Pivot Tables:
Use Jet Essentials' NL("Table") to Automate Reporting in Excel
Analysis against one ERP table
Example: Sales Transactions. You may want to quickly analyze the sales from your Customer table to learn more about sales during a period. You may want to know who the sales reps are, who their customers are, what country the customer is in, and what the sales were for the period. All data is extracted from just one table in your ERP.
Your Next StepsThese videos are designed to get you started and give you just a TASTE of what's possible. If you want to know more about improving performance, working with FlowFields and FlowField filters, or just need a refresher on how to make the most of Pivot Tables, I'm just an email away! In fact, if you go to our website, www.onyxreporting.com, you might be able to chat with me right now!
I look forward to hearing from you!