Let’s talk pivot tables.

Pivot tables make the process of summarizing data super simple: select your data, drag and drop a few variables, and BOOM! Seriously, they are really that straight forward. What used to take time and thought to create now takes mere seconds!

My friends, pivot tables are a game-changer. If you aren’t using them, you are missing out.

Why some people consider them to be an “advanced” Excel skill is beyond me.

In my opinion, pivot tables only require a few things:

  1. Well-structured data (for more about this, click here)
  2. Knowledge of what you want your data to tell you
  3. Basic pivot table mechanics

Let’s dive into each.

Well-structured data

First, your data MUST be structured so that observations are in rows, and variables are in columns.

For example, if you want to track your income and expenses, you should have columns for:

  • Transaction date
  • Transaction amount
  • Transaction description
  • Transaction category
  • Etc.

Each row beneath your column headers will contain the above information for a single transaction.

Knowledge of what you want your data to tell you

Do you want to know how much you spent on each category?

Do you want to know how much you spent in each month?

Do you want to know how much you spent on each category each month?

Do you want to know how much you earned?

Understanding what it is you want your data to tell you is essential when it comes to actually creating your pivot table. But, once you understand this, creating a pivot table is easy!

Basic pivot table mechanics

Both Excel and Google Sheets make building pivot tables simple, although they differ slightly in their approach.

We’re going to walk through the mechanics (and tie the first to points together) using an example:

Suppose you sell spreadsheets through your website (sorry, couldn’t help it!). Of course, you track your expenses and income using the Profit & Loss Tracker, which already comes with a few nifty pivot tables. Score! 

You love seeing how much money your website brings in each month, which the pivot tables make easy to do.

And then, your arch nemesis hacks into your computer and – just to mess with you – deletes all of your pivot tables. 

NO!

How on earth will you rebuild them?

Exel

  1. Select your data => Insert => Pivot Table => OK
  1. Then this happens…
  1. Now, go to the box on the right called “Field Names” and drag items from it into one of the four boxes below using the following overly general guidelines:
    • Dates often work best as columns. If you just want to see a simple list of items, e.g., expense categories and their totals, leave the columns box blank.
    • For basic data analysis, categories or other variables of interest typically go in the rows box.
    • Variables for which you want to exclude certain values can go in the filter box.
    • Variables that represent amounts often go in the values box.

I can already conceptualize a bazillion exceptions to these generalizations. So, the best way to determine what goes where is to just start dragging and dropping things – keeping in mind the question(s) you are asking of your data.

In this case, I’m going to drag the:

  • Month variable to the column box
  • Expense Category variable to the row box
  • Amount variable to the values box
  1. This ^ pivot table gives looks a little rough, but it contains everything it needs. You can:
    • Change its formatting in the Design tab on the ribbon. 
    • Change how the variables are displayed by clicking inside the boxes in the Pivot Table Fields section.
    • Change the look of other elements (check out this post for some ideas).

Here’s what my pivot table looks like after a little formatting:

Google Sheets

  1. Select your data => Data => Pivot Table => New Sheet => Create
  1. Then this happens…
  1. [This commentary was copied from the Excel section, since you probably didn’t read it if you’re reading the Google Sheets tutorial] Now, go to the Pivot Table Editor box on the right => click the “Add” button next to Rows, Columns, Values, and Filters options using the following overly general guidelines:
    1. Dates often work best as columns. If you just want to see a simple list of items, e.g., expense categories and their totals, leave the columns box blank.
    2. For basic data analysis, categories or other variables of interest typically go in the rows box.
    3. Variables for which you want to exclude certain values can go in the filter box.
    4. Variables that represent amounts often go in the values box.

I can already conceptualize a bazillion exceptions to these generalizations. So, the best way to determine what goes where is to just start adding things and then drag and drop them as desired – keeping in mind the question(s) you are asking of your data.

  1. This ^ pivot table gives looks a little rough, but it contains everything it needs. You can:
  1. Change its formatting in the Design tab on the ribbon. 
  2. Change how the variables are displayed by clicking inside the boxes in the Pivot Table Fields section.
  3. Change the look of other elements (check out this post for some ideas).

Here’s what my pivot table looks like after a little formatting:

Want to learn more about pivot tables (and pivot charts)?

Subscribe to my low-volume newsletter!