Working with Pivot Tables Parts 1 and 2

​Part 1: Tabular Formatting for Excel Pivot Tables

(Applies to Excel versions 2007 and newer)

This Tech Tip was submitted by Sue Powers, Director of Institutional Effectiveness, Champlain College.

Do you use pivot tables to summarize data, but find that the format can be frustrating? For example, do you need to see student IDs and first and last names on one row as you would in a regular report? If so, you will find that Excel makes creating this layout quick and easy.

See the example in Figure 1. This shows a pivot table with ID, first name, and last name in the “Row Labels” box, and a sum of credits in the “Values” box.

To change this to a more readable format, use the Tabular format.

fig1part1.JPG

When you work with Pivot Tables, you’ll see a new menu on the menu bar labeled PivotTable Tools with the Options and Design tabs.fig2part1.JPG

 

On the Design tab – in the Layout group to the left, select Report Layout, then “Show in Tabular Form.” Next, select Subtotals in the Layout group, and choose “Do Not Show Subtotals.” The result will be single rows of data like this:fig3part1.JPG

Part 2: Getting Details from Excel Pivot Tables 

(Applies to Excel versions 2007 and newer)

You’ve created a great Pivot Table that summarizes data into an easy-to-comprehend table. You’re presenting the data to a group of Deans, and things are moving along smoothly. Then, someone asks the question, “Who are the people in that group?” It doesn’t matter which group they are talking about - you can quickly show them the details on any of the pivot information. Excel provides a quick method of pulling data out of a Pivot Table by any one of the summarized cells.

See the example in Figure 1. This shows a pivot table summarized by Divisions in the “Row Labels” and Gender in the “Column Labels,” with a Count of ID (students) in the “Values.”

When someone asks the question, “Who are those 115 students who aren’t in a division?” simply double-click on the cell. This will open a new sheet with the details of just those 115 students.

 

You can also show that the students are non-degree students. You can then work with this new sheet of data as an independent worksheet. It is not connected to the Pivot Table or the original data set in any way.

fig2part2.JPG

Note: If you want to keep this new sheet of data, you should insert a header line at the top, or rename the sheet to something descriptive, so you remember what set of data it is - for example: “Students with No Assigned Division.”

 
 

 Comment

 
To add a comment, Sign In
There are no comments.