How to analyze data using Excel PivotTables

Learn how to use one of the most powerful tools Microsoft Excel has to offer.

By Helen Bradley, PC World |  Software, Microsoft Excel

If you have Excel 2010, you can use the new Slicers tool to make your PivotTable even easier to work with. Slicers let you select the data to show in the PivotTable in a more visual way. To add a Slicer, click inside the PivotTable, and choose PivotTable Tools > Options > Insert Slicer. Click the checkbox for each Slicer to add to the worksheet, and click OK. In my case, I added them for Quarter, Category, and Consultant.

To filter the PivotTable, click an item in the Slicer to see that data in the PivotTable. To view multiple items at once, Ctrl-click each item you want to see. The items you're viewing will be colored in the Slicer. When the icon in the top right of a Slicer is active, it indicates that the Slicer is filtering the data in the PivotTable; click the icon to remove that filter.

You can format Slicers by selecting a Slicer, choosing Slicer Tools > Options, and then clicking a Slicer Style to use for each of the Slicers in turn.

Update a PivotTable When Data Changes

If you make any changes to the data in the original list from which you created the PivotTable, you need to update the PivotTable to reflect these changes. To do so, just right-click the PivotTable and click Refresh.

Next Page: Create a PivotChart Based on Your PivotTable

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

Originally published on PC World |  Click here to read the original story.
Join us:






Ask a Question