{"id":316,"date":"2022-01-28T03:54:29","date_gmt":"2022-01-28T03:54:29","guid":{"rendered":"http:\/\/miriamposner.com\/classes\/is270s23\/?page_id=316"},"modified":"2022-01-28T04:01:58","modified_gmt":"2022-01-28T04:01:58","slug":"make-a-pivot-table-in-excel","status":"publish","type":"page","link":"http:\/\/miriamposner.com\/classes\/is270s23\/organizing-data\/make-a-pivot-table-in-excel\/","title":{"rendered":"Make a pivot table in Excel"},"content":{"rendered":"\n

You can watch me do this on video<\/a>.<\/em><\/p>\n\n\n\n

1. Tell Excel you want to make a pivot table<\/h2>\n\n\n\n

To begin your pivot table, first click ANYWHERE in the data table on your spreadsheet. Next, select the Insert<\/strong> tab near the top of your Excel window (1). (Remember that your version of Excel may look a little different!) Then, click on the Pivot Table <\/strong>button.<\/p>\n\n\n\n

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

2. Select some options for your pivot table.<\/h2>\n\n\n\n

Next, you’ll see a pop-up window. First, when the pop-up asks you which data you want to analyze, leave the answer just as it is. It should read Table1<\/strong>. This means that you want the pivot table to include all the categories in your table.<\/p>\n\n\n\n

Second, you’ll be asked where you want to put your pivot table. You can create a pivot table on the same worksheet your data is on, or you can create a pivot table on a new worksheet. I find it less confusing to work with a pivot table on a new worksheet. So select New Worksheet<\/strong>.<\/p>\n\n\n\n

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

3. Learning about the pivot table workspace<\/h2>\n\n\n\n

Excel adds a new worksheet and you will see a number of options related to the creation of your pivot table. The PivotTable Fields<\/strong> options are the most important. You will create your pivot table by dragging field names into the blank boxes below.<\/p>\n\n\n\n

On the left, you’ll notice a box that reads, “To build a report, choose fields…” That’s where your pivot table will appear.<\/p>\n\n\n\n

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

4. Columns, rows, filters, values??<\/h2>\n\n\n\n

Let’s take a little detour to talk about those confusing-sounding labels on your pivot table workspace. It will take a little bit of trial and error for you to get comfortable using them; it can be confusing to figure out which field name should go where.<\/p>\n\n\n\n

The Field Names<\/strong> correspond to the columns in your dataset.<\/p>\n\n\n\n

In general, Rows<\/strong> are best for time increments, like months, years, etc. In the Columns<\/strong> box, you’ll usually put the categories you want to distinguish among your records. For example, I’m interested in knowing how many records I have in each color. So in the next step, Color<\/strong> is the field name I’ll drag into the Columns <\/strong>box. Values<\/strong> are the metrics you’re measuring: in this case, the number of records for each color.<\/p>\n\n\n\n

If you’re having trouble getting your head around this, don’t worry! Everyone finds it confusing, and everyone agrees that you’ll need to practice and play with the options in order to figure out the best configuration.<\/p>\n\n\n\n

It’s a little easier to see what I mean when you try it yourself, which you’ll do in the next step.<\/p>\n\n\n\n

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

5. Drag field names into the boxes<\/h2>\n\n\n\n

We have date information, so let’s put that in the Rows<\/strong> box. To do that, click on the Date<\/strong> field name and drag it into the Rows<\/strong> box. Our categories are the Colors<\/strong>, so let’s drag the Colors <\/strong>field name into the Columns <\/strong>box.<\/p>\n\n\n\n

And we’ll do one more thing: We need to tell the pivot table what it should count. So let’s click on the Colors<\/strong> field name a SECOND time and drag it again, this time into the Values<\/strong> box. This tells Excel that you want to count up the number of records in each Color<\/strong> category.<\/p>\n\n\n\n

Side note: If you want to delete a field name from one of the boxes, uncheck the box next to its name in the list of field names.<\/p>\n\n\n\n\n\n

6. You have a pivot table!<\/h2>\n\n\n\n

Now, you’ll see that Excel has neatly summarized all of your values, showing you how many colors occurred by year. It was even smart enough to subdivide the data by quarter, as you’ll see if you click on the tiny plus signs just to the right of the year labels.<\/p>\n\n\n\n

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

7. Let’s make a quick chart (1)<\/h2>\n\n\n\n

Now that you’ve made a pivot table, it’s easy to compare values on a chart! First you need to select the contents of your table. You can do that efficiently by clicking on the cell containing 2016 (on my computer, it’s cell A5), holding down the shift key, and clicking on the number above the grand total on the bottom right of your table.<\/p>\n\n\n\n

Make sure you don’t include the grand total row or column in your chart.<\/p>\n\n\n\n

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

8. Let’s make a quick chart (2)<\/h2>\n\n\n\n

Now that you’ve selected the values in your table, click on the Insert<\/strong> tab within the Excel window and click on the Column<\/strong> chart type. From the drop-down menu, select the 2D Clustered Column<\/strong>.<\/p>\n\n\n\n

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

9. Admire your chart<\/h2>\n\n\n\n

Now it’s easy to see how many of which color appear in your data each year. (Of course, the graph’s colors are nonsensical, but you can change those by double-clicking on the color names in the legend and then changing the column colors in the Format chart area <\/strong>tab on the left.)<\/p>\n\n\n\n

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

10. See if you can…<\/h2>\n\n\n\n
  1. alter the chart so that each column represents a year and the colors appear on the X (horizontal) axis<\/li>
  2. create a table that shows the sum of sales per color, by year<\/li>
  3. create a table that displays the regions on the X axis and the years as columns<\/li>
  4. create a filter that allows you to display results by color<\/li><\/ol>\n\n\n\n

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

    You can watch me do this on video. 1. Tell Excel you want to make a pivot table To begin your pivot table, first click ANYWHERE in the data table… Continue reading Make a pivot table in Excel<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"parent":328,"menu_order":0,"comment_status":"closed","ping_status":"closed","template":"","meta":{"_eb_attr":"","footnotes":""},"class_list":["post-316","page","type-page","status-publish","hentry","entry"],"_links":{"self":[{"href":"http:\/\/miriamposner.com\/classes\/is270s23\/wp-json\/wp\/v2\/pages\/316","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/miriamposner.com\/classes\/is270s23\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"http:\/\/miriamposner.com\/classes\/is270s23\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"http:\/\/miriamposner.com\/classes\/is270s23\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/miriamposner.com\/classes\/is270s23\/wp-json\/wp\/v2\/comments?post=316"}],"version-history":[{"count":0,"href":"http:\/\/miriamposner.com\/classes\/is270s23\/wp-json\/wp\/v2\/pages\/316\/revisions"}],"up":[{"embeddable":true,"href":"http:\/\/miriamposner.com\/classes\/is270s23\/wp-json\/wp\/v2\/pages\/328"}],"wp:attachment":[{"href":"http:\/\/miriamposner.com\/classes\/is270s23\/wp-json\/wp\/v2\/media?parent=316"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}