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

In the PivotTable Field List on the right side of the screen, click the Quarter, Category, and Fees checkboxes; this is the data you need to answer the question "How much did we earn from Development for each quarter in 2011?" When you do this, Excel creates a PivotTable for you. The data might not be arranged exactly to your liking, but that's an easy fix.

If you want to see the categories as columns and the quarters as rows, for instance, drag Quarter to the Row Labels box and drag Category to the Column Labels box. You can now read the answer to your question in the PivotTable, because column B now contains the results for fees earned from Development for the four quarters individually, as well as the grand total for the year.

Next Page: How to Format a PivotTable

Format a PivotTable

Although the PivotTable answers the question, it could be formatted more neatly. To format the numerical data, right-click a value and select Number Format.

Over in the Category list, choose Number, and then set up your desired number format; in my example, I added a thousand separator and set decimal places to zero. Click OK, and Excel formats the data--all of the numbers in the table--accordingly. To make the PivotTable itself more attractive, click inside it, choose PivotTable Tools > Design, and click a style in the PivotTable Styles Gallery.

Answer Multiple Questions With One PivotTable

Having created a PivotTable, you can now use that same table to answer other questions. For example, to answer the question "How much did Davidson earn for Development for each quarter?" click in the table to redisplay the PivotTable Field List, and click Consultant to add it to the Row Labels box. If the PivotTable Field List disappears from the screen, right-click inside the PivotTable and click Show Field List.

Even though you can read the answer already in the PivotTable, you can also filter the PivotTable to hide unwanted data. Select one of the consultants' names, and then click the Row Labels drop-down arrow in the PivotTable. Deselect the checkboxes for all but Davidson. If you see the quarters listed in the drop-down box, go back and select a consultant before clicking the drop-down arrow.

Now choose one of the categories (Development or Support), click the Column Labels drop-down arrow, and deselect all but Development. This action filters the PivotTable so that it shows only the information that answers the question. In this way, you can use your created PivotTable over and over again to answer all the questions you have about your data.

Perform Smarter Filtering With Slicers

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






Answers - Powered by ITworld

Ask a Question