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:
Facebook

Twitter

Pinterest

Tumblr

LinkedIn

Google+

SoftwareWhite Papers & Webcasts

Webcast On Demand

HP DevOps KnowledgeVault

Sponsor: HP

See more White Papers | Webcasts

Answers - Powered by ITworld

ITworld Answers helps you solve problems and share expertise. Ask a question or take a crack at answering the new questions below.

Join us:
Facebook

Twitter

Pinterest

Tumblr

LinkedIn

Google+

Ask a Question