Quick Excel Tip of the Day: Intro to Pivot Tables
Looking to move your Excel reports from impressive to brilliant? Follow these quick excel tips that separate the experts from the rookies.
Pivot tables are a function in excel that is one of the less visible tips I'll share but it is one of the most used functions in excel by the pros. In this article I will focus on why Pivot Tables are so useful and tomorrow article will demonstrate how to start working with them.
What is a Pivot Table?
A Pivot Table is a function within Microsoft Excel that allows users to work very flexibly with a set of data. Once you load the data into the pivot table you can very quickly flip columns to rows, aggregate levels of data and rearrange summary views very quickly.
A Pivot Table is a great way to quickly provide different summaries of data if you are working with a hierarchy. A good example of this would be raw sales data by month by store. Using a pivot table you can quickly aggregate sales by district, region, country, month sort top regions within a country etc. They are incredibly flexible to not only produce these summaries but allow the analyst to quickly rearrange the data and begin looking for conclusions.
What are some Benefits of Pivot Tables?
Not only are Pivot Tables great for rearranging and summarizing data but they also improve the sustainability of the reports and allow the analyst to work more efficiently.
In this sense, sustainability is the ability for the report to accommodate new data as the business changes without having to redesign anything. Pivot Tables are excellent in an environment where new data is expected to become available and that new data will need to be included in reporting. In our example above, if the sales report is produced in January, when February passes and sales data is earned people will want to see that new data included.
Pivot Tables can quickly accommodate this by either extending the range of cells that it is including in the pivot table or if you're really cleaver - by linking it directly into a Microsoft Access data source. Either way, when the root data source grows you simply refresh the pivot table and it re-produces the analysis with the new data included.
Have you ever had to produce tailored reports for different segments of a business? In the example above, this would be something like producing a monthly sales report for each region. If it is a regional VP they are requesting a one page review of their regions sales performance without needing to see the other regions.
Pivot Tables also make this easier since the data source includes all regions you can simply copy and paste the Pivot Table showing or hiding only the relevant regions in each tab of the excel report. This is a great to quickly produce tailored reports without having to redesign anything from one region to the next.
These are just a few benefits to using Pivot Tables in your reports, I tried not to get too technical here because they can be intimidating for beginners that are unfamiliar with them.
Stay tuned tomorrow and I will give some tips on how to create effective pivot tables.