{"id":2811,"date":"2017-11-15T11:36:19","date_gmt":"2017-11-15T19:36:19","guid":{"rendered":"http:\/\/miriamposner.com\/classes\/dh101f17\/?page_id=2811"},"modified":"2017-11-15T11:36:19","modified_gmt":"2017-11-15T19:36:19","slug":"summarize-values-with-excel","status":"publish","type":"page","link":"https:\/\/miriamposner.com\/classes\/dh101f17\/tutorials-guides\/data-visualization\/summarize-values-with-excel\/","title":{"rendered":"Summarize values with Excel"},"content":{"rendered":"
\n

In this exercise, we\u2019ll look at ways to deal with\u00a0nominal values<\/em>\u00a0in our data. Remember, nominal data is categorized with labels, but those labels don\u2019t correspond to quantitative values. So unlike other values \u2014 like \u201cnumber of minutes\u201d or \u201cage\u201d \u2014 they can\u2019t 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\u00a0pivot table<\/strong>\u00a0summarizes 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\u2019re getting this error: \u201dThis command requires at least two rows of source data. You cannot use the command on a selection in only one row,\u201d 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\u00a0Data<\/strong>\u00a0menu, select\u00a0PivotTable.\u00a0<\/strong>In the window that pops up, leave the options as they are and click\u00a0OK<\/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\u00a0Majors<\/strong>\u00a0in the previous step. Click on the word\u00a0Majors<\/strong>\u00a0on the PivotTable Builder and drag it, first into the\u00a0Row Labels<\/strong>\u00a0area and next into the\u00a0Values<\/strong>\u00a0area. 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\u00a0Charts<\/strong>\u00a0tab.<\/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\u00a0Chart<\/strong>\u00a0tab, select\u00a0Bar<\/strong>\u00a0and then\u00a0Stacked 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\u2019s 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\u00a0Charts<\/strong>\u00a0tab, 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\u00a0Charts<\/strong>\u00a0tab, select\u00a0Bar<\/strong>, and then\u00a0Stacked 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\u2019re having trouble selecting data for your pivot table\u2026<\/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: \u201cThis command requires at least two rows of source data. You cannot use the command on a selection in only one row.<\/strong>\u201d I\u2019m 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\u00a0Insert<\/strong>\u00a0and then\u00a0Pivot Table<\/strong>. Then, with your cursor in the\u00a0Table\/Range<\/strong>\u00a0text box, click on the first column you want in your pivot table. Then, hold down\u00a0shift<\/strong>\u00a0and click on the second column. When you\u2019re done, just click\u00a0OK<\/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","protected":false},"excerpt":{"rendered":"

In this exercise, we\u2019ll look at ways to deal with\u00a0nominal values\u00a0in our data. Remember, nominal data is categorized with labels,<\/p>\n","protected":false},"author":1,"featured_media":0,"parent":137,"menu_order":0,"comment_status":"closed","ping_status":"closed","template":"","meta":{"_eb_attr":"","footnotes":""},"class_list":["post-2811","page","type-page","status-publish","hentry"],"_links":{"self":[{"href":"https:\/\/miriamposner.com\/classes\/dh101f17\/wp-json\/wp\/v2\/pages\/2811","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/miriamposner.com\/classes\/dh101f17\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/miriamposner.com\/classes\/dh101f17\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/miriamposner.com\/classes\/dh101f17\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/miriamposner.com\/classes\/dh101f17\/wp-json\/wp\/v2\/comments?post=2811"}],"version-history":[{"count":0,"href":"https:\/\/miriamposner.com\/classes\/dh101f17\/wp-json\/wp\/v2\/pages\/2811\/revisions"}],"up":[{"embeddable":true,"href":"https:\/\/miriamposner.com\/classes\/dh101f17\/wp-json\/wp\/v2\/pages\/137"}],"wp:attachment":[{"href":"https:\/\/miriamposner.com\/classes\/dh101f17\/wp-json\/wp\/v2\/media?parent=2811"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}