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 create a chart with the PivotTable selected, it appears as a PivotChart that illustrates what the PivotTable displays.

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

If you double-click a value in a PivotTable to drill down, Excel extracts the values that contributed to it, placing them into a new sheet.

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

You can set a PivotTable to show you calculations other than Sum. Here it shows Averages.

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:

How to Create Advanced Microsoft Excel Spreadsheets

Work Faster in Microsoft Excel: 10 Secret Tricks

Make Data Entry Easier in Microsoft Excel: 10 Tricks

10 Secrets for Creating Awesome Excel Tables

Subscribe to the Best of TechHive Newsletter

Comments