BUILDING PIVOT TABLES
In data processing, a pivot table is a data summarization tool found in data visualization programs such as
spreadsheets or business intelligence software. Among other functions, a pivot table can automatically
sort, count, total or average the data stored in one table or spreadsheet, displaying the results in a second
table showing the summarized data. Pivot tables are also useful for quickly creating unweighted cross
tabulations. The user sets up and changes the summary's structure by dragging and dropping fields
graphically. This "rotation" or pivoting of the summary table gives the concept its name.
The biggest challenges we encounter in data-driven decision support are:
1) Gathering accurate, useful data,
2) Having the data easily accessible for analysis,
3) Asking the right questions of available data, and 4) finally, carrying out the analysis correctly with
appropriate tools.
Three key reasons for organizing data into a Pivot Table are:
1. Summarizes data into a compact, understandable format.
2. Helps find relationships in data.
3. Organizes the data in a format that’s easy to chart.
Below are the responses to a survey of 20 Company XYZ customers taken to determine the level of support for
a label change that has been proposed by the marketing department. You are to use EXCEL’s “pivot table”
options to produce summary tables for these responses.
Sex Age group Support ? Education Income
1 M 21-25 YES COLL 25000
2 M 26-40 NO HS 38000
3 F 21-25 NO COLL 21000
4 M 41-65 YES HS 68000
5 F over 65 YES NO HS 34000
6 F 21-25 NO COLL 23000
7 M over 65 YES COLL 46000
8 F 21-25 YES HS 102000
9 F 26-40 YES HS 85000
10 F 26-40 NO COLL 68000
11 M 21-25 NO NO HS 37000
12 F 41-65 NO COLL 22000
13 M 21-25 NO HS 76000
14 M over 65 YES NO HS 41000
15 F 41-65 YES HS 26000
16 M 41-65 NO COLL 67000
17 M 26-40 YES COLL 92000
18 F 41-65 NO HS 45000
19 F over 65 YES HS 39000
20 F 21-25 YES COLL 28000
1. Use EXCEL’s Pivot Table Report to produce a summary table like the one below, which shows a count of
respondents classified by age and support for the change:
Support change?
NO YES Grand Total
Age group
21-25 4 3 7
26-40 2 2 4
41-65 3 2 5
over 65 0 4 4
Grand Total 9 11 20
STEP 1:
Enter the Data in a new Excel worksheet.
Choose the Insert tab on the ribbon at the top of the screen, then select Pivot Table from the Tables
group.
Enter the range of the data you entered on the worksheet (e.g., A5:E25) or use your mouse to
highlight the data range. (Include the column labels, but don’t include the first column of
numbers…1,2,3…)
STEP 2:
This range may already be automatically entered for you. Be sure the box for existing worksheet is checked,
then click on the “location” box and enter the location of the cell on your worksheet where you want your
table to appear. Click OK.
STEP 3:
From the Pivot Table Field List that now appears on the right of your screen, use the mouse to drag
the “age group” label to the Row Lables box. Similarly, drag the “support” label to the Column
Labels box. Drag the “age group” label (again) to the Values box.
STEP 4:
To eliminate the “row labels” and “column labels” drop down menus from your table (if they appear),
right click on any cell in the main body of the table, then select Pivot Table Options from the list.
Choose the Display tab, then be sure the “Display field captions and filter dropdowns” box is
unchecked. Click OK.
2. Produce a table like the one below which shows the average income for respondents classified by
education and support for the proposed change.
Average of Income Support change
NO YES Grand Total
Education
COLL 40200 47750 43555.56
HS 53000 64000 59875.00
NO HS 37000 37500 37333.33
Grand Total 44111.11 53272.73 49150.00
STEP 1:
Enter the Data in a new Excel worksheet.
Choose the Insert tab on the ribbon at the top of the screen, then select Pivot Table from the Tables
group.
Enter the range of the data you entered on the worksheet (e.g., A5:E25) or use your mouse to
highlight the data range. (Include the column labels, but don’t include the first column of
numbers…1,2,3…)
STEP 2:
This range may already be automatically entered for you. Be sure the box for existing worksheet is checked,
then click on the “location” box and enter the location of the cell on your worksheet where you want your
table to appear. Click OK.
STEP 3:
From the Pivot Table Field List, use the mouse to drag the “education” label to the Row Lables
box. Similarly, drag the “support” label to Column Labels box. Drag the “income” label to the
Values box.
STEP 4:
Right click on any one of the cells in your table. In the menu that appears, pick Value Field
Settings. Click the Summarize by tab, then choose Average. Click OK.
3. Produce a table like the one shown below, which is based on the table you produced in Exercise 1. In this
case, cell values are shown as percentages of the total, rather than simple counts, making the cell entries joint
probabilities. (For example, P(Age 21-25 AND No Support) = 20%)
Support change
Age group NO YES Grand Total
21-25 20.00% 15.00% 35.00%
26-40 10.00% 10.00% 20.00%
41-65 15.00% 10.00% 25.00%
over 65 0.00% 20.00% 20.00%
Grand Total 45.00% 55.00% 100.00%
STEP 1:
Enter the Data in a new Excel worksheet.
Choose the Insert tab on the ribbon at the top of the screen, then select Pivot Table from the Tables
group.
Enter the range of the data you entered on the worksheet (e.g., A5:E25) or use your mouse to
highlight the data range. (Include the column labels, but don’t include the first column of
numbers…1,2,3…)
STEP 2:
This range may already be automatically entered for you. Be sure the box for existing worksheet is checked,
then click on the “location” box and enter the location of the cell on your worksheet where you want your
table to appear. Click OK.
STEP 3:
From the Pivot Table Field List that now appears on the right of your screen, use the mouse to drag
the “age group” label to the Row Lables box. Similarly, drag the “support” label to the Column
Labels box. Drag the “age group” label (again) to the Values box.
STEP 4:
To eliminate the “row labels” and “column labels” drop down menus from your table (if they appear),
right click on any cell in the main body of the table, then select Pivot Table Options from the list.
Choose the Display tab, then be sure the “Display field captions and filter dropdowns” box is
unchecked. Click OK.
STEP 5:
RIGHT CLICK on any cell in the main section of the table (not the first row or column that shows
labels). Select Value Field Settings. In the box that appears, click the Show values as tab. Click on the
down arrow next to Show Values As to display the list of options. Select % of Grand Total from the
list. Click OK. The modified table should appear.
4. Build a table like the one shown below, which is based on the table you built in Exercise 1. In this case, cell
values are shown as percentages of the column totals, making the cell entries conditional probabilities. (For
example, P(Age 21-25 GIVEN No support) = 44.44%.)
Support change
Age group NO YES Grand Total
21-25 44.44% 27.27% 35.00%
26-40 22.22% 18.18% 20.00%
41-65 33.33% 18.18% 25.00%
over 65 0.00% 36.36% 20.00%
Grand Total 100.00% 100.00% 100.00%
STEP 1:
Enter the Data in a new Excel worksheet.
Choose the Insert tab on the ribbon at the top of the screen, then select Pivot Table from the Tables
group.
Enter the range of the data you entered on the worksheet (e.g., A5:E25) or use your mouse to
highlight the data range. (Include the column labels, but don’t include the first column of
numbers…1,2,3…)
STEP 2:
This range may already be automatically entered for you. Be sure the box for existing worksheet is checked,
then click on the “location” box and enter the location of the cell on your worksheet where you want your
table to appear. Click OK.
STEP 3:
From the Pivot Table Field List that now appears on the right of your screen, use the mouse to drag
the “age group” label to the Row Lables box. Similarly, drag the “support” label to the Column
Labels box. Drag the “age group” label (again) to the Values box.
STEP 4:
To eliminate the “row labels” and “column labels” drop down menus from your table (if they appear),
right click on any cell in the main body of the table, then select Pivot Table Options from the list.
Choose the Display tab, then be sure the “Display field captions and filter dropdowns” box is
unchecked. Click OK.
STEP 5:
RIGHT CLICK on any cell in the main section of the table (not the first row or column that shows
labels). Select Value Field Settings. In the box that appears, click the Show values as tab. Click the
down arrow next to Show Values As to display the list of options. Select % of Column. Click OK.
The modified table should appear.
5. Following the pattern above,
a) Build a summary table of simple counts, showing “Sex” in the rows and “Age Group” in the columns.
b) Modify the table to “% of Grand total” in each cell. (Joint probabilities)
c) Modify the table to show “% of column” in each cell. (Conditional probabilities)
d) Modify the table to show “% of row” in each cell. (Conditional probabilities)
6. Following the pattern above,
a) Build a summary table of simple counts, showing “Support Change” in the rows and “Sex” in the columns.
b) Modify the table to “% of Grand total” in each cell. (Joint probabilities)
c) Modify the table to show “% of column” in each cell. (Conditional probabilities)
d) Modify the table to show “% of row” in each cell. (Conditional probabilities)
