**Updated May 31, 2018.
My previous post outlined the perils of using Excel as an inventory management tool. This time let's look at one of the most powerful and appropriate business uses of Excel - the Pivot Table report. In my experience, this is one of the least understood tools available to those who use Excel to analyze and report on data, and yet for many, it should be the tool of choice. Although Excel should not be used as a standalone inventory management (or data management) tool, when used in conjunction with proper inventory and accounting ERP software, it can provide powerful results. Using Excel Pivot Tables with ERP software eliminates all the downsides of using Excel as a business management reporting tool - there is no need to re-key data which eliminate the chance of human error, and the information is available in real-time.
Pivot Tables
Pivot Tables facilitate summarizing and thereby analyzing data. They are a style/type of report that allows users to easily manipulate data in a way that makes sense for their business. Let's use the example of an ERP system. With the right ERP solution, you can create a live link to your ERP data from within an Excel workbook, and return the data as a Pivot Table report. What you'd get initially would look something like this:
Click to Enlarge
After you've linked the data in Excel from the database of your choosing (in this example, your ERP database), essentially you are left with a list of the fields available from the database to which you've linked. This allows you to easily report on relevant information found within your ERP solution without having to re-key data. Working with the information in the right-hand panel, you can now use simple drag and drop actions to determine what information you would like to analyze in the rows and columns of the Pivot Table, and what data should be summarized. In this example, I'm analyzing sales data by product by country for the year 2015. It took me less than 30 seconds from start to get this:
Click to Enlarge
Once again, simple drag and drop actions will allow me to change the content and even layout of this report instantaneously. One of the major benefits of linked reporting is that the data is always live. A simple right-click and refresh of the data set from within Excel will update the information according to what has been added/modified in your ERP solution. This simplifies the reporting process moving forward. In a future post, I'll explore this a little further and also try to answer any posted questions. Although using Excel Pivot Tables with ERP software is a familiar tool and great way to quickly report on data, one of the benefits of ERP software is access to other robust reporting tools. Work with your ERP partner to learn more about how to create reports using different tools - this will allow you to find the best tool for any given report.
To learn more download the Small Business Reporting Guide now!
