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
Total Comments: 16
 
Dina posted on 1/15/2013 4:33 PM
Nice demonstration. Easy to follow.
Angela posted on 1/15/2013 4:36 PM
This is a good tip! I also use the tabular or outline formats when I need a unique count from a group that contains multiple records. For example, by placing the student number in column A and degrees awarded in column B, you can use Column A to obtain the unique student count, even though students may have received multiple degrees.
Gary posted on 1/15/2013 4:42 PM
Even to somebody like me who is not a fan of Excel, I've got to admit pivot tables are a nice tool. Thanks for covering these basics. Mini tip: memorize this key sequence [Alt+n > v > t > Enter] to create a new PT.
Mary Jo posted on 1/15/2013 5:08 PM
Excel provides some powerful ways to display data. This is a helpful and useful tip!
Erin posted on 1/15/2013 5:17 PM
I love pivot tables and always get excited when I learn something new in their functionality. Thanks for this tip!
Terry posted on 1/15/2013 5:57 PM
This comes in very handy. It also makes it possible to take the data directly to a database.
Bamby posted on 1/15/2013 8:02 PM
Great tip!
Laura posted on 1/16/2013 8:10 AM
Great tip. I don't use pivot tables nearly enough.
Onecia posted on 1/16/2013 8:58 AM
Vary nice! I do have a question. When you double click on a number in a pivot table to open the details (the 115 in the example above), the resulting detail sheet automatically has filtering on the column headers. Does anyone know of an option so that the sheet does not automatically contain the filtering? I know how to manually remove the filtering but it would be helpful to learn how to remove the auto-filtering.
Jim posted on 1/16/2013 9:29 AM
It's really important to become familiar with all of the "Options" and "Design" menu items that come up when you click in a pivot table. One annoying thing about the default options is that some columns in the pivot table report may be boldface when you really want plain text. It's not well documented, but you can get around that by selecting the "Design" menu and try un-checking either the "row headers" or "column headers" options.
Michael posted on 1/16/2013 9:40 AM
Good Tips. I use Pivot Tables a lot, and it is always nice to try and get converts by showing things they can do that they thought might not have been possible.
Reuben posted on 1/16/2013 9:47 AM
This is a great topic. I undervalued pivot tables for too long and need to add them to my arsenal of data-display tools. Thanks for posting. I hope to see this discussion broadened in the future.
Nan posted on 1/16/2013 11:05 AM
This is very helpful. I should use more pivot tables.
Pallabi posted on 1/16/2013 11:34 AM
Very useful! Thanks for sharing.
Michelle posted on 1/16/2013 12:33 PM
Thanks, Sue. I had been using Pivot Tables but the format has always bugged me. Your tip has been very useful to me.
Emily posted on 1/17/2013 10:57 AM
Thanks for the formatting tip and all of the screenshots- very helpful!