Pivot Tables are an amazing reporting feature in Excel and using them allows you take large amounts of data and aggregate it. This makes it easier to do quick analysis and look at trends and patterns without having to do complex or tedious methods or manually sifting through the data.
You can also create multiple pivot table reports on a single data set. When the data changes or you add more data, just refresh one of the pivot table reports (right-click, refresh), and all the remaining pivot tables in the workbook also refresh and display new aggregate calculations.
It is recommended that you consolidate and group data into column categories and convert the data set into a table object (insert, table). For example, instead of having each month as a separate column (Jan, Feb, Mar etc.), consolidate the data under a single column called “Months”. Another example would be, instead of having a separate column for each quarter (Q1, Q2 etc.), consolidate the data under a single column called “Quarters”. In other words, collect the data vertically instead of spreading out the data horizontally. Let the pivot table perform the function of producing the columns and aggregate calculations. In the data world, this is called “normalizing the data”. Having vertical data also makes it easier to perform other functions such as sorting and filtering.
Figure 1 below shows a commonly constructed report. While this appears neat and organized it is not ideal for data collection and produces more work if new data is to be included.
Can you spot a few issues with the data setup in Figure 1?
Solution: Get rid of the blank rows that split the data into multiple data sets, consolidate the months under a single column, and convert the data into a table object. Additionally, identify the data with a year.
Figure 2 below shows the correct data structure for general analysis and also the required structure for aggregate calculations. The pivot table reporting tool will aggregate the data and allow you to spread out the columns with the calculations.
Other advantages/functions in pivot tables:
More advanced users can produce grouped and tiered calculations, apply slicers to filter the data for the desired items, and also create pivot charts to further analyze the data. Using the Filter quadrant in pivot tables, data can also be separated into multiple sheet tabs, while maintaining the pivot tables reporting structure.
Simple pivot table reports showing aggregate calculations:
(The data set has hundreds of rows but only 7 columns, and there is now also a “Year” column to identify the data for multiple years, unlike previous structure that only allowed for one year at a time, if you were to spread out the months in a separate column, i.e., 12 columns).
Final Thoughts: Pivot tables are not only powerful and fairly easy to use but also have robust functionality to do quick analysis and reporting. They are also dynamic with the data source and avoid redundant work or manually having to update formulas. There were no formulas written manually in the pivot table reports. Pivot tables allow you to also construct reports and charts, which give you a look and feel of dashboards. They allow you to get an overview of the entire dataset in a relatively small effort.
