Make a pivot table with Excel

pivot table summarizes data values. For example, given a list of students’ majors, a pivot table can tell us how many students have declared an English major, a Communication major, and so on. Here, we’ll use a pivot table first to learn which types of grants were awarded in Iowa in 2015, and next to understand the correlation of city with grant type.

1. See what happens without a pivot table (1)

When a person investigates column C, “Grant Type,” they’ll tend to understand that the data is categorical; that is, the data is divided into groups. Therefore, a meaningful data visualization should probably show how many individual records are in each group.

Yet Excel doesn’t immediately understand this. You can see what I mean by first selecting the entire column C, “Grant Type.” (You can select the column by using your cursor to press the “C” above the words “Grant Type.”)

2. See what happens without a pivot table (2)

Now click on the Insert tab within the Excel window. From the array of chart types, select the column chart and then select 2D Column.

3. See what happens without a pivot table (3)

Not really what we wanted, is it? Excel is trying to find numeric values for each of these grant types; it doesn’t understand that the values are categorical.

Please delete the graph you just created by clicking on it and then pressing the delete key. We’ll start again, this time building a pivot table.

4. Pick a column

To start with, we’ll look at one variable (Grant Type) in isolation. Use your mouse to click on Column C.

5. Turn your column into a pivot table (1)

From the Data menu in the TOP TOOLBAR, select Summarize with PivotTable. In the window that pops up, leave the options as they are and click OK. Excel will open your PivotTable in a new worksheet, within the same Excel file.

6. Turn your column into a pivot table (2)

Excel will open a new worksheet and you’ll see a blank table, along with the PivotTable Builder. The PivotTable Builder allows you to select which values you’d like to summarize.

In all honesty, this is the part of building a pivot table that confuses everyone, including me. It’s helpful to visualize what a useful table summarizing grant types would look like. Each row would be a type of grant. For each type of grant, we’d see a value; that is, the number of each.

Click on the word Grant Type on the PivotTable Builder and drag it, first into the Rows area and next into the Values area. Hey, look, the grant types are summarized for you!

7. Turn your pivot table into a chart

From the PivotTable Analyze tab, select PivotChart. This selects a chart for you. Now you have a chart!

8. Make a pivot table that compares two values

We actually have multiple columns in our dataset, so why not see how two of them correlate with each other? Follow steps four and five above, except this time, in step one, select both columns C and D (Grant Type and County). When you get to the PivotTable Builder, select the options indicated in the image above.

9. Make a chart that compares two values (1)

Instead of letting Excel pick our chart for us, let’s build a stacked bar chart. Select the cells indicated in the image above. (That is, select all the cells that contain values, except the Grand Total row.) Then click on the Insert tab within the Excel window.

10. Make a chart that compares two values (2)

From the Charts options, select Column, and then 2DStacked Bar.

11. You did it!

Now you can see not only how many grants each county has received, but how many of each kind!