Mastering Pivot Tables: Adding your Data

Step 1: organize your data. Check.

Step 2: setup a pivot table. Check.

Step 3: add data to your pivot table. It’s about to get real.

Now that you’ve setup a pivot table, you should have a blank slate to run all the analysis your heart desires. The setup happens on the right in your PivotTable fields. If you don’t have this window, head to your PivotTable Analyze tab and make sure that your “Field List” button on the right is selected.

MPT-PivotRibbon

Let’s dissect this PivotTable Fields thingy. Starting at the top left and moving clockwise…

  • Filters – these are any top level filters you want to put on your whole pivot table. Maybe your data has values across multiple counties, but you only want to look at US. This is where you’d add a filter for that.
  • Columns – use a categorical variable like dates here
  • Values – any variable that you want to “do math” to (sum, count, average, etc.) – could be impressions, cost, CPC, CTR, to name a few
  • Rows – another place for categorical variables

MPT-PivotTableFields-Blank

Going back to our sample dataset from Airbnb, let’s say we wanted to count the number of private rooms by neighborhood group in Berlin. We’d add the neighborhood_group as our row, the room_type as our filter, and the ID as our value. The ID will work well as our value because it will serve as a unique counter for each property. Dragging and dropping these variables into the right section of the pivot table fields should yield something like this…

MPT-PivotTableFields-wID

The resulting pivot table needs some work. First of all, because the ID field is only numbers, Excel automatically summed it. Since we want to count it, we’d just have to right click any of the values in the pivot table > summarize values by > Count.

MPT-CountScreenshot

The other thing we need to modify if the filter. We only wanted to know the count of private rooms. Now you can click the filter icon and uncheck the other options to limit the results to only private rooms.

MPT-FilterScreenshot

Now, if we also wanted to add the average price for private rooms for these neighborhoods, we can do it with only a few more clicks. Drag the price field into your value quadrant and it’ll populate in your pivot table. Again, Excel decided to sum this value by default, which isn’t all that helpful. RIght click on any of the price values > summarize values by > average. Now your pivot table should look something like this…

MPT-UnformattedPivot

Finally, let’s cover formatting. Let’s assume this is price in USD – we don’t need to carry it out to 8 decimals, and it would be nice to have that “$” in there. Right click > value field settings. You’ll get a dialogue box that looks like this…

MPT-ToNumberFormat

Now, click numbers on the bottom left corner. From there, you can select your category, like currency, and apply it to improve the format of your pivot table. Looks much better…

MPT-NumberFormats

And there you have it – got your data added to a pivot table and formatted it to make sense in context. Look at all the quick insights you can now draw – like how expensive the average private room is in Charlottenburg! In the next one, we’ll look at grouping. Happy pivoting. 

MPT-FormattedPivot.png

 

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.