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 that stores all your inventory and sales data in a database format that can be accessed via Excel. You would 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:
Click Image to Enlarge
So you have a list of the fields available from the database to which you’ve linked. You can now use simple drag and drop to determine what you’d 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 2008. It took me less than 30 seconds from start to get this:
Click Image to Enlarge
Once again, simple drag and drop will allow me to change the content and even layout of this report instantaneously. In a future post, I’ll explore this a little further and also try to answer any posted questions.
[...] a Slice(r) with Excel 2010 As discussed in a previous post, Excel Pivot Tables are powerful tools for analyzing and reporting on data from your accounting [...]