Google sheets pivot table hide collapse button

I'm trying to get around the Pivot Table Editor always displaying by using the protected sheet feature, but this disables the ability to expand and collapse my pivot table fields.

Can anyone help me with a really basic collapse and expand script / macro?

I've tried recording the action itself but that hasn't worked.

• google-apps-script
• google-sheets
• pivot-table

Pᴇʜ

55k9 gold badges46 silver badges68 bronze badges

asked Jun 11, 2021 at 21:51

3

• Hello @JonGoodwin! Could you please share the code you are using so that we can better help you? And please remember to remove or anonymize any private information.

  Jun 14, 2021 at 6:44

• It's not really code, I'm trying to record the macro by clicking on the pivot table, but it doesn't seem to recognise me collapsing the field /** @OnlyCurrentDoc */ function CTCCostPlusCollapseProject() { var spreadsheet = SpreadsheetApp.getActive(); spreadsheet.getRange('B13').activate(); };

  Jun 14, 2021 at 16:00

• I have been researching your setup, but I can't fully replicate it. If you don't have any code, please share your actual Sheet and an example of your desired Sheet in order to reproduce your question.

  Jun 21, 2021 at 13:38

Did you know that you can automate metadata analysis in Google Sheets? It’s easy using a nifty feature called a pivot table.

With Google Sheets, you can customize a pivot table with just a few clicks! Functions, calculations, and other analyses are even simpler with pivot tables.

In this guide, we’ll teach you how to use a pivot table to summarize and analyze your data in Google Sheets.

An overview of what we’ll cover: 

• Importance of pivot tables in Google Sheets
• How to Create a Pivot Table in Google Sheets
• Analyzing the data on a pivot table
• Different ways to analyze the data of pivot table
• Adding filters to a pivot table
• Using slicers

So let’s dive in! 

Importance of Pivot Tables

When analyzing data from multiple sources at once on Google Sheets, it can be difficult to keep track of all your data. When your data is massive enough, it’s impossible to see the big picture without summarizing and analyzing it into metadata.

Crunching all the numbers by hand is time-consuming, even with automated formulas and functions. But there’s a better and faster way to optimize your data: pivot tables. 

When implemented correctly, Google Sheets pivot tables interpret your data and present it concisely so that you can understand the big picture at a glance.

How to Create a Pivot Table in Google Sheets

How does it work? Let’s demonstrate with some sample data.

Let’s say that we have a data set containing information about seventeen different students from a school sports event. 

We have each student’s team, class, age, height (in centimeters), weight (in kilograms), and points they won for their teams in the competition. 

Let’s create a pivot table in Google Sheets to analyze and optimize this data! 

In the top bar menu, go to Insert → Pivot table. 

Once you create a pivot table, you’ll need to set a Data range for the values that you want to analyze. This range must include all cells that you want included in your pivot table analysis.

🚨 Note: When selecting your data range, make sure to include the headings of your data values as well. 

Next, you can choose the destination to add your new pivot table. You can either insert it into the existing sheet, or you can choose to add it to a new sheet. 

I recommend inserting the pivot table into a New sheet as it will provide sufficient space to analyze the data. 

Once done, click on Create. 

After you set up the pivot table parameters correctly, you’ll be able to see the pivot table in a new sheet on your screen. 

But this pivot table doesn’t display much useful information yet — let’s configure it to summarize, analyze, and optimize our data!

Analyzing Data in a Pivot Table

Once we have a blank pivot table set up, we’ll choose how we want our data to appear on the table. 

We have customizable Rows and Columns on the table according to our data values. Once we set the values of Rows and Columns, the pivot table will populate itself with the intermediate. 

You can configure your pivot table with the sidebar on the right side of your screen, which contains the Pivot table editor. (If you close this window, you can reopen it by selecting any cell inside your pivot table.)

This editor will usually give you some Suggested values for your table customizations. These values should have you covered for most conventional analyses. 

But in case you are working with an unusual data set (and because the Google AI is good but not perfect), it’s important to know how to manually insert values as well. 

For this example, let’s say that we want to analyze the distribution of points among both teams and individual students at the school sports event.

To do this, we need a pivot table that displays the number of points scored, organized by both the student who scored them and the team for which they were scored.

Configuring Rows in a Pivot Table

Let’s start by configuring this pivot table’s Rows. The step will use a metric from your original data set as a row header for each row of your pivot table.

Click on the Add option beside the Rows section. You’ll see all the different headers from your original dataset listed here. 

We’re interested in two characteristics of the points score: the student who earned them, and the team for which they were earned. To address the first characteristic, we’ll choose Name as the parameter for our rows. 

Additionally, we’ll also keep the Show totals option checked. This is so we can also check the total score of each team (or the total of whatever value you’re analyzing in your pivot table). 

Configuring Columns in a Pivot Table

Next, we’ll set up the Columns. Your column configuration will apply a metric from your original data set as column headers for your pivot table.

Since we want to see the distribution of points across the three teams, we’ll choose Team as our column parameter. 

Again, we’ll keep the totals checked. Additionally, you can also choose whether you want to organize the data in ascending or descending order. We’ve kept it in Ascending order for all our tables. 

Pivot Table Values in Google Sheets

Once the Rows and Columns are set up, we’ll also choose what parameter we want to add to the Values section. This is what metric from our original dataset will populate the data cells in our pivot table.

Since we’re calculating the points secured by each student and team (our Rows and Columns), we’ll choose Points as the value parameter.

Additionally, we’ve kept the default SUM of the values as the indication in the grand total of Rows and Columns. 

You could also set it up as average, product, maximum, or minimum as per your pivot table analysis requirements. 

Once the parameters are set up correctly, you’ll be able to see the completed table on the left side of your screen. 

As we had already chosen to see the total values, you can see the total points scored by each student, each team, and collectively by all teams and students. 

In just one quick snapshot, we can see the total points made by each member for each team. 

We can also see which student scored the most points, which team made the most points, and the total score made by all teams together. 

Additionally, after you create the pivot table, you can still alter the values of the data range that you chose previously from the pivot table editor. 

Note that if the data in your original data set changes, you won’t need to set up all the table parameters again! It will update dynamically as long as your data range still covers all the data.

Let’s also see different ways to analyze this data! 

Different Ways to Analyze the Data of Pivot Table

Suppose we want to check the percentage of the team’s points scored by each student. We can do this by adjusting the parameters for our Values.  

We’ll choose to Show as % of column instead of the default option to display all points scored as a percentage of the total points in that column. 

Let’s try another example. Let’s say that we want to add data about the points scored by each class without removing the students’ names and teams. 

To do this, we’ll add the Class parameter to the Rows section of the pivot table editor without exchanging or removing the Name parameter.  

Once done, we can see the table modifies itself according to the new parameter and shows the data in the same manner. 

Now, the pivot table shows the names of the students categorized according to their class.

By adding another Row parameter, we can see the points scored by each class, each team, and each student — all in a single glance. 

You can even hit the minus button ( – ) to expand or collapse each class list!

Even with the secondary row parameters hidden, the total scores for each class (and each team per class) will still be reflected on the table. 

You can expand back the data whenever you need it again by clicking back on the plus icon ( + ). 

Adding Filters to a Pivot Table

If you want even more customizations on your pivot table, read on! 

You can add filters to customize and narrow your view of the data even further. 

For example, suppose you want to analyze only the data of the students who are at least 20 years old.

Let’s see how to add filters in such a case. 

Navigate to the Filters section on the pivot table editor and click on Add. Choose the Age parameter. 

Once done, you’ll see that it’s currently Showing all items. Click on the dropdown to modify the items that you want to include in your pivot table. 

In this case, you can deselect all age values less than 20. (Or, you can clear all selections and select only values greater than or equal to 20.)

Once done, click on Ok. 

The pivot table will update to display data from only the chosen age group. 

The total points and names of the students will also change accordingly.

Using Slicers

Similar to the filters, another handy feature is a slicer. 

To add a slicer, select the entire range of your data set by clicking on the top left cell of your sheet and navigate to Data → Add a slicer. 

Next, you’ll need to select a column. This gives you different options to slice your data. Based on your use case, select the one that fits the best.

For example, in our data we’ll choose to slice the data using the number of Points scored by each student. 

Once we choose our column, we can click on the filet icon. 

This further gives you the option to sort the data set based on a number of parameters. Let’s go ahead and select Filter by condition. 

Next, select the condition as per which you would want to filter your data. In our case, we’ll choose the option of Greater than or equal to. 

This is because we want to sort the students based on the points they scored individually.

Let’s suppose we only want to sort out the students who scored 80 or above points in the sports tournament. 

To do that, we’ll add 80 as the value in this condition set. 

And that’s it. This will sort the data as per our filter. 

This feature can be really helpful when you have sizable data and you want to filter a specific part of it.

Summary 

So that’s all you need to know about pivot tables in Google Sheets!

This is especially useful when you have a massive amount of data that you want to summarize and analyze quickly. 

Additionally, once you start creating different customized tables on your Google Sheets using your website data, you can even analyze that data using Google Analytics to create a better version of your site!

Marketers, what are your favorite use cases for pivot tables? Do you have any favorite tips or tricks for pivot table analysis? Let us know in the comments below! 

How do I hide the expand collapse button in a pivot table?

Can you hide expand collapse buttons?

How do I hide pivot options in Google Sheets?

How do I remove expand collapse in Excel?