{"id":101,"date":"2016-09-24T17:31:32","date_gmt":"2016-09-25T00:31:32","guid":{"rendered":"http:\/\/miriamposner.com\/classes\/dh101f16\/?page_id=101"},"modified":"2016-11-02T11:46:10","modified_gmt":"2016-11-02T18:46:10","slug":"summarize-values-with-excel","status":"publish","type":"page","link":"http:\/\/miriamposner.com\/classes\/dh101f16\/tutorials-guides\/data-visualization\/summarize-values-with-excel\/","title":{"rendered":"Summarize Values with Excel"},"content":{"rendered":"
\n

In this exercise, we’ll look at ways to deal with\u00a0nominal values<\/em> 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\u00a0count\u00a0<\/em>the values. Then we can plot the number of records in each category.<\/p>\n

In Excel, a pivot table<\/strong> summarizes data values. For example, given a list of students\u2019 majors, a pivot table can tell us how many students have declared an English major, a communication major, and so on. Here, we\u2019ll 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.<\/p>\n

Open this\u00a0spreadsheet in Excel and then follow the steps below to build a chart of your data.<\/p>\n

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.<\/em><\/p>\n<\/div>\n

\n

1. Pick a column<\/h3>\n
\"media_1445452093696.png\"<\/div>\n
\n

To start with, we\u2019ll look at one variable (major) in isolation. Use your mouse to click on Column B.<\/p>\n<\/div>\n<\/div>\n

\n

2. Turn your column into a pivot table (1)<\/h3>\n
\"media_1445452151918.png\"<\/div>\n
\n

From the Data<\/strong> menu, select PivotTable. <\/strong>In the window that pops up, leave the options as they are and click OK<\/strong>.<\/p>\n<\/div>\n<\/div>\n

\n

3. Turn your column into a pivot table (2)<\/h3>\n
\"media_1445452428630.png\"<\/div>\n
\n

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

\n

4. Turn your pivot table into a chart (1)<\/h3>\n
\"media_1445452553307.png\"<\/div>\n
\n

Now that your majors are summarized, it\u2019s 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<\/strong> tab.<\/p>\n<\/div>\n<\/div>\n

\n

5. Turn your pivot table into a chart (2)<\/h3>\n
\"media_1445452784616.png\"<\/div>\n
\n

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

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.<\/p>\n<\/div>\n<\/div>\n

\n

6. Make a pivot table that compares two values<\/h3>\n
\"media_1445453044164.png\"<\/div>\n
\n

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\u00a0PivotTable Builder<\/strong>, select the options indicated in the image above. (Tip: For \u201cRow Labels,\u201d it\u2019s generally good to select the column that has more values in it, and for \u201cColumn Labels,\u201d that column that has fewer values.)<\/p>\n<\/div>\n<\/div>\n

\n

7. Make a chart that compares two values (1)<\/h3>\n
\"media_1445453375963.png\"<\/div>\n
\n

Select the cells indicated in the image above. Then click on the Charts<\/strong> tab, just as you did in Step 4.<\/p>\n<\/div>\n<\/div>\n

\n

8. Make a chart that compares two values (2)<\/h3>\n
\"media_1445453481904.png\"<\/div>\n
\n

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

\n
\n
\n
\n
\n

If you’re having trouble selecting data for your pivot table…<\/em><\/h1>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n
\n
\n

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.<\/strong>” I’m not sure exactly what is causing the error, but the following fix seems to take care of it.<\/p>\n<\/div>\n

\n
\n

Instead of selecting your columns and then creating a pivot table, do it the other way around<\/h2>\n
\n

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

<\/div>\n<\/div>\n<\/div>\n<\/div>\n

\"instead-of-selecting-your-columns-and-then-creating-a-pivot-table-do-it-the-other-way-around\"<\/a><\/p>\n

<\/div>\n<\/div>\n","protected":false},"excerpt":{"rendered":"

In this exercise, we’ll look at ways to deal with\u00a0nominal values in our data. Remember, nominal data is categorized with labels, but those labels don’t correspond to quantitative values. So … Continue reading “Summarize Values with Excel”<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"parent":92,"menu_order":0,"comment_status":"closed","ping_status":"closed","template":"","meta":{"_eb_attr":"","footnotes":""},"class_list":["post-101","page","type-page","status-publish","hentry"],"_links":{"self":[{"href":"http:\/\/miriamposner.com\/classes\/dh101f16\/wp-json\/wp\/v2\/pages\/101","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/miriamposner.com\/classes\/dh101f16\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"http:\/\/miriamposner.com\/classes\/dh101f16\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"http:\/\/miriamposner.com\/classes\/dh101f16\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/miriamposner.com\/classes\/dh101f16\/wp-json\/wp\/v2\/comments?post=101"}],"version-history":[{"count":0,"href":"http:\/\/miriamposner.com\/classes\/dh101f16\/wp-json\/wp\/v2\/pages\/101\/revisions"}],"up":[{"embeddable":true,"href":"http:\/\/miriamposner.com\/classes\/dh101f16\/wp-json\/wp\/v2\/pages\/92"}],"wp:attachment":[{"href":"http:\/\/miriamposner.com\/classes\/dh101f16\/wp-json\/wp\/v2\/media?parent=101"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}