How to Analyze Data Using Excel PivotTables
Create a PivotChart Based on Your PivotTable
You can easily chart the data from your PivotTable; doing so creates a PivotChart that Excel automatically links to the PivotTable. Any changes you make in the chart's organization will be reflected in the PivotTable, and vice versa.
To create a PivotChart, click anywhere in the PivotTable, choose Insert, and select a Column chart (a simple chart will work best). The PivotChart will appear in the sheet alongside the PivotTable, and it will display the data from the PivotTable.
When you later update the data in the PivotTable by using Slicers, or by changing the table layout, the chart will automatically change to match.
Drill Down to View the Data
PivotTables have a built-in drill-down feature. If you want to explore a particular value in your PivotTable, such as a total, double-click it. When you do, you'll see a new sheet containing all the data that contributed to the value you double-clicked. This sheet allows you to analyze the data and understand it better.
Each time you double-click a value in a PivotTable, a new sheet appears in your workbook. It can get crowded with these extra sheets if you don't remove them when you are finished with them. To do so, right-click a sheet's tab and click Delete.
Use Other Calculations on Your PivotTable Data
By default, Excel sums the value fields in a PivotTable, but you can change this setting so that it makes other calculations, such as the maximum, minimum, and average. For instance, if you want to see the average of the values in the PivotTable, right-click an item in the data area, choose Summarize Values By, and click Average. The heading in the top left of the table will change to indicate that you are viewing the average values, and Excel will recalculate the PivotTable values to show averages. To undo the change and see the data totaled, right-click a value, choose Summarize Values By, and click Sum.
In this discussion I've merely scratched the surface of what you can achieve with PivotTables and PivotCharts in Excel. With knowledge of these basic tools, however, you can easily create a PivotTable to summarize and analyze your own data.
If you liked this article, you might also be interested in these stories: