Trick That Pivot Table

You're the boss of your spreadsheet

Author

Eamonn Cottrell
May 14, 2025 • Estimated Reading Time: 7 minutes

In today’s email:

  1. Creating Pivot Tables

  2. Tricking Pivot Tables

  3. Creating Pivot Charts

SPONSOR

Got Sheet is powered by beehiiv, my go-to newsletter operating system for online businesses. Try for free here.

MAIN ARTICLE

Why Filter Views Don’t Work

Filter views are great. Until they’re not.

I had a Got Sheet subscriber reach out the other day with a filter view + chart issue. They were trying to dynamically update data in a chart based on a filtered view of their data.

It would work initially, but when the filter changed, the chart would not update.

Filter views are great for saving a particular filtered view without affecting anybody else’s views when you share your workbook with them.

However, they won’t update the data in a chart. To do this, I suggested a combination of Pivot Tables and Dropdown lists.

The result was quick to build and satisfying to use…

The Data Set

The data set we are working with is very simple to illustrate the point. We have consultants, regions and revenue.

We want to set things up so that our chart can change between two views

  • Revenue by consultant

  • Revenue by region.

I’ve set up the three columns and formatted as a table. This will work in both Sheets and Excel, but I’ll highlight the differences as they crop up below. (I like Sheets a little more for this.)

Trick the Pivot Table

So, yes, we’re going to have to trick the pivot table. We want it to update dynamically, and there’s not a built-in function to let this happen. So, we’ll create a helper column in our dataset based off of a Dropdown list selector.

We need a simple dropdown list first with the options of either Region or Consultant.

Set Up the Dropdown

We’ll start by creating a simple Dropdown list with two options:
Region and Consultant

Place this in a cell like F1—we’ll reference it next.

Add the Helper Column

Next, add a new column in your dataset called Group.

In this column, we’ll write an IF formula to dynamically pull either Region or Consultant based on the dropdown selection.

Assuming:

  • Column A = Region

  • Column B = Consultant

  • Cell F1 = your dropdown selection

Use this formula in your new Group column so that it will either display the Region or the Consultant based on our dropdown selection.

=IF($F$1="Region", A2, IF($F$1="Consultant", B2, ""))

Drag this formula down the column. Now we’re ready for the pivot table.

Create the Pivot Table

Go to Insert - Pivot Table. Sheets will automatically detect the table range. Then, we insert the pivot table on the same sheet so we can view everything together.

insert pivot table

In the Pivot Table options:

  • Set Rows to the Group column

  • Set Values to the Revenue column

When Region is selected, it’ll group revenue by region. When Consultant is selected, it’ll switch views automatically.

Here’s what our pivot table will look like with the Region selected.

pivot table in sheets

This is the part where I prefer Google Sheets over Excel. By default, Sheets will dynamically update the pivot table data every time we change the dropdown selection. In Excel, you must manually refresh the pivot table data.

Pivot Chart

Google Sheets doesn’t have a native "pivot chart" like Excel—but we can fake it:

  1. Select all the data from your pivot table

  2. Go to Insert → Chart

⚠️ Important: You must manually select a static range large enough to accommodate both possible views.

data range for a chart

For example:

  • Revenue by Region might only need 4 rows

  • Revenue by Consultant might need 6

Select a chart range that includes extra rows. Blank ones will be ignored.

Also: turn off the Grand Total row in your pivot table to avoid skewing the chart.

Why not multiple pivot tables and charts?

Pure preference.

For this, we wanted a simple dynamic selector. But, for dashboard style information, it may be best to go ahead and create the two pivot tables and accompanying charts.

Quick Comparison

Method

Pros

Cons

Single pivot + dropdown

Clean UI, one chart

More setup, chart range needs padding

Multiple pivots + charts

Easy to configure, always accurate

Cluttered UI, no toggle functionality

NEXT STEPS

Whenever you’re ready, here’s how I can help:

  1. Business tech stack (FREE)
    My recommendations for software and tools I use to create content and run my online business.

  2. Sponsor Got Sheet
    Got Sheet teaches business operators, teachers and entrepreneurs how to get good at spreadsheets. Interested in sponsoring an issue or a series of Got Sheet newsletters? Reach out to get more information.

  3. Personal budget tool
    As a Got Sheet subscriber, I want you to have a 50% discount on the personal finance system I update every year.

  4. YouTube

    If you aren’t subscribed yet, come on over to my YouTube channel where I make all my spreadsheet, coding and productivity tutorials

HAPPY SPREADSHEETING!

Enjoyed this issue?
Subscribe below or Leave a testimonial

Cheers, Eamonn
Connect on LinkedIn, YouTube & Twitter.

Reply

or to participate.