Creating Pivot Tables in Microsoft Excel
How to get from A to B in a few easy steps using the Pivot Table wizard.
From A….
…To B
Microsoft Excel provides many useful means of representing data. You are probably familiar with features that allow you to do the following:
- reorder data using the sort feature
- create totals using the sum feature
- create percentages by dividing a particular number by an overall total
- show changes over time using simple subtraction and division
- put numbers into graphs for visual consumption
An additional feature of Excel which you may be less aware of is called Pivot Tables. If the results of your query (the information on the screen after you have clicked “submit query”) comprise multiple pages, the query is a good candidate for pivot tables. The steps are simple:
- Create and submit your DSS query
- Download the query to Excel using the “DownLoad Result” button at the bottom of the query results page
- The results automatically open in Excel
- Find the “Data” menu and go to the “PivotTable” option
- Follow the wizard steps for creating a table
If you are familiar with Excel, following the five steps
outlined above should help you create a Pivot Table. If you are not very familiar with Excel, the attached pages may
be helpful to you. They will walk you
through the process using a series of screen captures which mirror what you
will see on your computer.
Instructions for what to do upon opening the Excel Pivot
Table wizard are also included.
1. Create and submit your DSS query
You may have to scroll down to see the Submit Query button.
2. Download the query to Excel using the “DownLoad Result” button at the bottom of the query results page
3. The results automatically open in Excel
Excel does not have to be open when you begin the DownLoad—it will open automatically. Toggle to Excel to view results.
4. Find the “Data” menu and go to the “PivotTable” option
5. Follow the wizard steps for creating a table
The default selection “Microsoft Excel list or database” is what you want. Simply click the Next button.
The second step in the wizard should also automatically provide the default that you want. When nothing in the open Excel document is highlighted, the wizard automatically assumes the range of cells for which data is entered. Simply click Next.
It is easiest to use the New worksheet default. However, you will need to specify the Layout—to do this, select the Layout button. This will bring up all the “variables” (types of data in your spreadsheet) and allow you to select which variables you want included and in what way. This page shows the layout screen before selections have been made.
To select variables for the table, click on them and drag them as desired onto the blank layout. Below are the selections which would reconstruct the sample table for ethnicity which has been provided to you.
The variable which is dragged into the Data area of the table requires an additional step. After you drag it into place, if it doe not already indicate that it will provide the Sum (often the default is Count), double click on it and select Sum.
Now that your table layout specifications are complete, select OK.
Now select the Options button to tweak the table just a little bit more.
In general, you can leave the options selections alone, but you may want to deselect the Grand totals. In this example, Grand totals for rows is not a valid figure since the aggregate number of students across multiple years is never used in college discourse. Point to this item and click to deselect it. Then click OK.
After specifying the Layout and Options, click Finish and the base Pivot Table is complete. You’re almost done!
The Pivot Table has dynamic elements. Click on the arrows to change selects. In the example below “All” has been changed to the Art Department, Fall Term, Credit. View cell E8 where a formula (optional) is being added within Excel to provide a percentage profile of 2000 enrollees. The formula shows the cell for African Americans enrolled in Art, Fall 2000 (cell D8) and the number of total enrollees in Art, Fall 2000 (D$16)[1].
Before dragging the formula, change the cell format to Percentage. Go to the Format menu and select Cells. “Percentage” is the seventh option down on the list.
Finally, drag the cell formula, via the lower right hand corner of the cell, down through the remaining rows (in this case, through row 15).
As a double check, it is useful to sum the column to make sure that it totals properly (in this case, 100%). Make sure that only data cells are included in the sum (E7 should not be included in the sum here—E7 is the title cell which contains “2000%.” Excel may default to including this in the sum—for the sum range to begin with E8).
Graphics can supplant or add to the final data presentation. Below is an example of what a final layout might look like.
|
DEPARTMENT |
Art |
|
|
|
|
TERM |
Fall |
|
|
|
|
CR/NC |
Credit |
|
|
|
|
|
|
|
|
|
|
|
1998 |
1999 |
2000 |
2000% |
|
African
American/Non Hispanic |
53 |
59 |
74 |
4.0% |
|
American
Indian/Alaskan Native |
16 |
12 |
18 |
1.0% |
|
Asian/Pacific
Islander |
443 |
454 |
455 |
24.6% |
|
Filipino |
69 |
75 |
63 |
3.4% |
|
Hispanic/Latino |
247 |
198 |
215 |
11.6% |
|
Other Non
White |
36 |
43 |
45 |
2.4% |
|
Unknown/No
Response |
190 |
154 |
131 |
7.1% |
|
White Non
Hispanic |
855 |
889 |
846 |
45.8% |
|
Grand
Total |
1909 |
1884 |
1847 |
100.0% |
