{"id":97,"date":"2019-01-04T23:33:57","date_gmt":"2019-01-04T23:33:57","guid":{"rendered":"http:\/\/miriamposner.com\/classes\/dh201w21\/?page_id=97"},"modified":"2019-01-21T23:49:14","modified_gmt":"2019-01-21T23:49:14","slug":"make-a-pivot-table-with-excel","status":"publish","type":"page","link":"http:\/\/miriamposner.com\/classes\/dh201w21\/tutorials-guides\/data-cleaning-and-manipulation\/make-a-pivot-table-with-excel\/","title":{"rendered":"Make a pivot table with Excel"},"content":{"rendered":"\n

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 learn which types of grants were awarded in Iowa in 2015, and next to understand the correlation of city with grant type.<\/p>\n\n\n\n

1. See what happens without a pivot table (1)<\/h3>\n\n\n\n

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

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

\"\"<\/figure>\n\n\n\n

2. See what happens without a pivot table (2)<\/h3>\n\n\n\n

Now click on the Insert<\/strong> tab within the Excel window. From the array of chart types, select the column chart and then select 2D Column<\/strong>.<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

3. See what happens without a pivot table (3)<\/h3>\n\n\n\n

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

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

\"\"<\/figure>\n\n\n\n

4. Pick a column<\/h3>\n\n\n\n
\"\"<\/figure>\n\n\n\n

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

5. Turn your column into a pivot table (1)<\/h3>\n\n\n\n
\"\"<\/figure>\n\n\n\n

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

6. Turn your column into a pivot table (2)<\/h3>\n\n\n\n
\"\"<\/figure>\n\n\n\n

Excel will open a new worksheet and you\u2019ll see a blank table, along with the PivotTable Builder<\/strong>. The PivotTable Builder allows you to select which values you\u2019d like to summarize. <\/p>\n\n\n\n

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<\/em> would be a type of grant. For each type of grant, we’d see a value<\/em>; that is, the number of each.<\/p>\n\n\n\n

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

7. Turn your pivot table into a chart<\/h3>\n\n\n\n
\"\"<\/figure>\n\n\n\n

From the\u00a0PivotTable\u00a0Analyze<\/strong>\u00a0tab, select\u00a0PivotChart<\/strong>. This selects a chart for you. Now you have a chart! <\/p>\n\n\n\n

8. Make a pivot table that compares two values<\/h3>\n\n\n\n
\"\"<\/figure>\n\n\n\n

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<\/strong> and County<\/strong>). When you get to the\u00a0PivotTable Builder<\/strong>, select the options indicated in the image above. <\/p>\n\n\n\n

9. Make a chart that compares two values (1)<\/h3>\n\n\n\n
\"\"<\/figure>\n\n\n\n

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<\/strong> row.) Then click on the\u00a0Insert<\/strong>\u00a0tab within the Excel window.<\/p>\n\n\n\n

10. Make a chart that compares two values (2)<\/h3>\n\n\n\n
\"\"<\/figure>\n\n\n\n

From the\u00a0Charts<\/strong>\u00a0options, select\u00a0Column<\/strong>, and then\u00a02DStacked Bar<\/strong>.<\/p>\n\n\n\n

11. You did it!<\/h3>\n\n\n\n
\"\"<\/figure>\n\n\n\n

Now you can see not only how many grants each county has received, but how many of each kind!<\/p>\n","protected":false},"excerpt":{"rendered":"

A pivot table 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… Continue reading Make a pivot table with Excel<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"parent":95,"menu_order":0,"comment_status":"closed","ping_status":"closed","template":"","meta":{"_eb_attr":"","footnotes":""},"class_list":["post-97","page","type-page","status-publish","hentry","entry"],"_links":{"self":[{"href":"http:\/\/miriamposner.com\/classes\/dh201w21\/wp-json\/wp\/v2\/pages\/97","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/miriamposner.com\/classes\/dh201w21\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"http:\/\/miriamposner.com\/classes\/dh201w21\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"http:\/\/miriamposner.com\/classes\/dh201w21\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/miriamposner.com\/classes\/dh201w21\/wp-json\/wp\/v2\/comments?post=97"}],"version-history":[{"count":0,"href":"http:\/\/miriamposner.com\/classes\/dh201w21\/wp-json\/wp\/v2\/pages\/97\/revisions"}],"up":[{"embeddable":true,"href":"http:\/\/miriamposner.com\/classes\/dh201w21\/wp-json\/wp\/v2\/pages\/95"}],"wp:attachment":[{"href":"http:\/\/miriamposner.com\/classes\/dh201w21\/wp-json\/wp\/v2\/media?parent=97"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}