Summarize values with Excel

In this exercise, we’ll look at ways to deal with nominal values in our data. Remember, nominal data is categorized with labels, but those labels don’t correspond to quantitative values. So unlike other values — like “number of minutes” or “age” — they can’t be directly charted as numerical values. Instead, we first have to count the values. Then we can plot the number of records in each category.

In Excel, a 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 understand the majors represented in our class, and next to understand the correlation of major with gender in our class.

Open this spreadsheet in Excel and then follow the steps below to build a chart of your data.

Note: If you’re getting this error: ”This command requires at least two rows of source data. You cannot use the command on a selection in only one row,” please scroll to the end of this tutorial for a fix.

1. Pick a column

media_1445452093696.png

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

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

media_1445452151918.png

From the Data menu, select PivotTable. In the window that pops up, leave the options as they are and click OK.

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

media_1445452428630.png

Excel will open a new worksheet and you’ll see a blank table, along with thePivotTable Builder. The PivotTable Builder allows you to select which values you’d like to summarize. In this case, we selected Majors in the previous step. Click on the word Majors on the PivotTable Builder and drag it, first into the Row Labels area and next into the Values area. Hey, look, the majors are summarized for you!

4. Turn your pivot table into a chart (1)

media_1445452553307.png

Now that your majors are summarized, it’s pretty easy to turn them into a chart. Use your mouse to select the cells indicated in the image above. Then click on the Charts tab.

5. Turn your pivot table into a chart (2)

media_1445452784616.png

From the Chart tab, select Bar and then Stacked Bar. Check it out, you have a chart! Play with the coloring and formatting options to make the chart look the way you want.

Remember, the more variation you introduce to your graph (e.g., color, shape, size), the harder it is to understand. So if you add an element, be sure it’s meaningful.

6. Make a pivot table that compares two values

media_1445453044164.png

We actually have two columns in our dataset, so why not see how they correlate with each other? Follow steps one and two above, except this time, in step one, select both columns A and B. When you get to the PivotTable Builder, select the options indicated in the image above. (Tip: For “Row Labels,” it’s generally good to select the column that has more values in it, and for “Column Labels,” that column that has fewer values.)

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

media_1445453375963.png

Select the cells indicated in the image above. Then click on the Charts tab, just as you did in Step 4.

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

media_1445453481904.png

From the Charts tab, select Bar, and then Stacked Bar. Now you can see not only how many students have declared which major, but what proportion of these majors are male versus female!

If you’re having trouble selecting data for your pivot table…

A couple people have been getting the following error when they attempt to build a pivot table after selecting multiple columns: “This command requires at least two rows of source data. You cannot use the command on a selection in only one row.” I’m not sure exactly what is causing the error, but the following fix seems to take care of it.

Instead of selecting your columns and then creating a pivot table, do it the other way around

Start by clicking on Insert and then Pivot Table. Then, with your cursor in the Table/Range text box, click on the first column you want in your pivot table. Then, hold down shift and click on the second column. When you’re done, just click OK, and your pivot table should work!

instead-of-selecting-your-columns-and-then-creating-a-pivot-table-do-it-the-other-way-around