Legos are the best way to learn Excel PivotTables

07

June 2018

Anthony M. Wagner
Excel Yourself

 

Of almost any Excel feature, I think PivotTables are among the most useful. I also think that they’re among the most confusing and misunderstood.

People often ask me for help with their spreadsheets, and I often find myself thinking, “If only they knew how to use a PivotTable, their life would be so much easier”.

Let’s finally end the confusion, and dive into this terrific tool!

Get the freebies!

I’m offering two freebies with this post:

  1. The spreadsheet I use throughout this post (so you can follow along)
  2. A bonus video that covers creating a PivotTable in more detail

Let’s play with Legos

Let’s imagine that you have a pile of Legos in front of you. You have dozens of red, green, blue, purple, and yellow bricks in one big heap.

You decide that you’re going to use those Legos to build a house. So, you take a few of each color and get to work.

Ultimately, you decide to use all of your colors except for purple.

Once you’re done, a friend comes up to you and says that they love your house. They’d really love to build one just like it, but they don’t know how many Legos to buy.

So, you tell them that you’ll make up a materials list for them!

You start by dismantling your house and separating all of the blocks into piles by color. You end up with a pile for each red, green, blue, and yellow. (Remember, none for purple because you didn’t use any!)

You count up each color.

Then you make a list for your friend.

Here, your rows are the colors, and in each row you have a value (a sum) for how many you’ve used. You’ve also filtered out purple, because you didn’t use any!

We’ll get to those bolded words in a minute

Without knowing it, you just created a basic, manual PivotTable!

Let’s take a closer look

The job of a PivotTable in Excel is to condense and summarize data.

In our Lego example, data will be represented by the individual Lego purchases. One purchase = one piece of data.

In Excel, data typically exists in a table: That is, it consists of a row of headers, and then related data in the rows beneath that.

Let’s look at some sample Lego data:

Here, you can see that we have a number of Lego purchases (I realize that this is slightly absurd, but go with me!).

Our headers are Date Bought, Lego Color, Quantity, and Used in House. The data is all of the information that falls in the rows beneath the headers.

You’ll notice that, in the Used in House column, the colors we actually used have an X in them; the one’s we didn’t (purple) don’t.

Linking Lego terms to PivotTable terms

In the description of the materials list at the end of our example, recall that I bolded a few words: RowsValuesSum, and Filter.

Let’s look at the list once more, but this time, let’s add those labels to it:

Thinking about our sample data in Excel, here’s a summary what we know so far:

Lego Term PivotTable Term
 Lego Purchases Data
Lego Color Rows
Quantity Values & Sum
Used in House Filter

In layman’s terms, we’re saying:

  1. We want a list of colors
  2. We want the total number of each color
  3. We only want to see the colors we actually used

How do we put this into Excel? Let’s find out!

Creating your first PivotTable

Once you know what you want to do in English, it becomes much simpler to tell Excel how to do it.

  1. First, be sure your data is in the format I described above (columns of related data with headers at the top)
  2. Select your data
  3. On the Insert tab in the ribbon, select PivotTable
  4. In the dialog box that opens, decide where you want your PivotTable to be placed. Typically, I leave it set to the default New Worksheet.

          Note: You don’t need to do anything with the Select a table or range option because you’ve already done it in step 2 above. Overall here, you can probably leave everything set to the default options!

  5. Select OK
  6. You’ll end up with something that looks like this:

Congrats, you’ve created your first PivotTable!

See how it’s done

Watch this quick clip to see how to create a PivotTable:

 

 

Get the freebies!

I’m offering two freebies with this post:

  1. The spreadsheet I use throughout this post (so you can follow along)
  2. A bonus video that covers creating a PivotTable in more detail

Adding data to your PivotTable

That was the easy part! And, because we already took the time to lay out what we want to accomplish with our PivotTable, this part will be easy too 😉

Recall our materials list from before:

Remember those terms we talked about (RowsValues, and Filters)? Here’s where they come in handy.

First, be sure that you see the Field List pane along the right side of the window. It opens by default when you create a new table.

If you don’t see it, click anywhere within your PivotTable. Then, in the Analyze tab in the ribbon, click Field List to turn it on.

You’ll see two distinct sections:

  1. The top section contains the headers for each column
  2. The bottom section has four boxes with those magic words we keep talking about!

You’ll notice a box that says Columns. It’s rare that I actually use this box, and for the purposes of this tutorial, we’re going to skip it.

 

Once you get the hang of the basics, I encourage you to play around with it!

All you have to do now is drag the appropriate column header from the top section to its matching box at the bottom!

Dragging Lego Color to Rows box

This chart (the one we used above) will come in handy here!

Lego Term PivotTable Term
 Lego Purchases Data
Lego Color Rows
Quantity Values & Sum
Used in House Filter

The Individual Legos row is just the data you’re working with. The three rows that follow (Lego Color, Quantity, and Used in House) are where you’ll focus.

We’re almost there! Just a couple more steps before it’s perfect.

Values & filters

Updating the value field settings

For this example, we wanted to know how many of each color we used. You might notice that, after you finish creating the Table, the numbers don’t look right.

In this example, by default, Excel set the value to Count. This this means is that, instead of adding up the actual numbers from our data, it’s looking at how many times each piece of data appears in the list.

If you look at the data, you’ll see that Red appears three times, blue appears twice, etc., and that’s what the PivotTable is telling us.

Counts can be very useful, but in this example, we’re looking to add up the actual values.

In Excel’s terms, we need the PivotTable to use Sum instead of Count.

  1. In the Values section of the Field List pane, click on the small triangle next to Count of Quantity (it might be truncated, but in this example, it’s the only option there!)
  2. In the drop-down, choose Value Field Settings
  3. In the dialog box, select Sum
  4. Select OK

Now our numbers look correct!

There’s just one last setting to tweak:

Filtering our results

Last up, recall that we didn’t use any purple in our house. Currently though, our PivotTable says that we used 25 bricks of purple. That’s not right.

Remember the Used in House column? This is where it comes in handy.

  1. Above the PivotTable, locate the Used in House box
  2. Select the down triangle
  3. In the box that opens, click the X
  4. Click OK

You just told Excel to only show data from the rows that have an in the Used in House column! Or, in other words, you filtered out any data without that X.

You did it!

Congrats! You now have a materials list you can print and give to your friend!

…or a table of how much money you spent from different categories.

…or a table of the number of times you saw a student over the last semester (based on a log file).

…or a table of grade point averages.

These are just a few examples of the unlimited possibilities you can accomplish with this incredible feature!

Need help?

As always, if you have questions or get stuck, please let me know in the comments below! I’d love to help!

Also, if you’re particularly proud of a PivotTable you create, I’d love to see it! Drop a screenshot in the comments 🙂

Get the freebies!

I’m offering two freebies with this post:

  1. The spreadsheet I use throughout this post (so you can follow along)
  2. A bonus video that covers creating a PivotTable in more detail

What’s next?

Here are three of our latest posts we think you’ll like:

FREE! The Ultimate Excel Cheat Sheet for School Counselors

Our one-page guide covers 8 simple Excel hacks all school counselors can start using right now. Download it for free!

One moment, please!