**Please note that this post has been updated to include more recent information.
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.
Pivot Tables facilitate summarizing and thereby analyzing data. 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 the Excel workbook, and return the data as a Pivot Table report. What you’d get initially would look something like this:
Essentially you have a list of the fields available from the database to which you’ve linked. This allows you to easily report on information found within your ERP solution without having to re-key data. 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:
Once again, simple drag and drop will allow me to change the content and even layout of this report instantaneously. A major benefit to linked reporting is that the data is always live. A simle 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 on a go forward based. In a future post, I’ll explore this a little further and also try to answer any posted questions.