Pivot tables in Google Sheets typically update dynamically. But if you have an issue, here’s how to refresh pivot tables in Google Sheets. Show
Pivot tables are a powerful tool that allows you to summarize data, find patterns and reorganize information. A lot of places of business use them for data analysis in apps like Excel and Google Sheets. You don’t typically need to refresh data in a pivot table in Google Sheets, as pivot tables typically refresh themselves when the source data is changed. That isn’t always the case, however. Google Sheets might fail to update if you’re working in a large spreadsheet with a lot of data. If this happens to you, here’s what you’ll need to do to refresh pivot tables in Google Sheets. One of the easiest ways to refresh a pivot table in Google Sheets is by refreshing the browser you’re using. To do this:
That’s it. Your spreadsheet should update the values you added or removed from the pivot table. The pivot table should dynamically update if you update the data it pulls from the table. Check Your Data RangesAnother reason your data may not be appearing correctly in your table is because the data range that your pivot table is pulling data from is incorrect. If you’ve already added your data, but add a new row of data below it, it won’t show up. Any data rows you add following the creation of the pivot table will not be reflected. To fix the issue of data not in range in a pivot table, do the following:
The table should include the new data and the new row or rows. If it still doesn’t update, refresh your browser as shown in step one. Check Your FiltersAnother reason the pivot table isn’t updating is if you apply a filter, and new items won’t appear. For instance, if you use a filter to a cell or cells in your pivot table, it might hide or display different results. To fix the filters issue in a Google Sheets pivot table:
Working With Data in Google SheetsNow you know how to refresh pivot tables in Google Sheets. Typically, your table in Google Sheets should update dynamically, but if it doesn’t, try refreshing your browser first. If that doesn’t work, try one of the other tips above. Of course, Google Sheets isn’t the only way to create spreadsheets. For example, if you have Excel, check out how to get the most from pivot tables in the Microsoft app. If you haven’t made one before, read about creating a pivot table in Microsoft Excel. For Google Sheets, you can create a checklist or insert an image in a Google Sheets spreadsheet. Also, take a look at using Google Sheets to track stocks.
A buddy hit me up asking how he is supposed to redock the pivot table editor within sheets, I for the life of me could find a way. Does anyone happen to know how to dock the pivot table editor the way it should be? thanks for any info guys! Whether you’re an experienced Excel user and wondering “ Are there pivot tables in Google Sheets?” or are a new user and wondering how they work, we have you covered. Pivot tables are an easy way to show succinct data from a bloated spreadsheet and this pivot table Google Sheets guide will go through how to make them and why they’re useful. The concept of pivot tables in Google Sheets may seem confusing at first, but once you get the hang of it, you’ll find your data analysis skills rise to a different level. This tutorial is aimed at not only introducing Google Sheet pivot tables and their use to beginners but also helping intermediate-level users up their pivot table skills with some additional tips and tricks. Read on to learn how to do a pivot table in Google Sheets. What Is a Google Sheet Pivot Table and Why Do I Need It?A pivot table is a table composed of columns and rows that can be moved around (pivoted), allowing you to group, isolate, expand and aggregate your data in various ways, and in real-time. As such, pivot tables help you analyze your data through various angles, giving you deeper insights into relationships between elements of your data. If that sounded complex, let’s break it down with an example. Take a look at the data set below:
The above dataset is large, looks complex, and it’s quite hard to make any sort of inference from it. A pivot table could help you condense a data set like this to get a better view and understanding of relationships in the data. For example, a pivot table could help you answer questions like:
As you can see, asking these questions lets you understand your data better and helps you make more informed decisions. Pivot tables can organize your data such that you can easily get answers to questions like these. At this point, you might be thinking, ‘Why not just use formulas to answer these questions?’. The answer is that pivot tables get your information extracted much faster than formulas. Since they are flexible (and ‘pivot’-able), they let you turn your data around to quickly and easily get your answers. Moreover, pivot tables reduce the chances of human error, giving you more accurate information. The Benefits of Using a Pivot TableThere are several reasons to use a pivot table, here are a few:
The Pivot Table Google Sheets TutorialNow that you understand the basic idea behind pivot tables, let us see how you can create one. The best way to understand the process is by example. We are going to use the following data sample (which you can download from this link), and create various pivot tables from it to answer some of our questions. To create a pivot table from the above data, follow the steps shown below:
At the beginning, your pivot table would be blank as shown in the image below. It will start filling up once you start adding rows, columns and values to the table (explained in step 6). To the right of your Google Sheets window, you should see a Pivot table editor sidebar. This sidebar displays the following:
Suggested Pivot TablesGoogle Sheets provides a few ideas for pivot tables based on your selected data. For example, for our sample data, Google Sheets suggests the following:
Clicking any of these suggestions will automatically create your initial pivot table. For example, let’s click on the second suggestion: “Count of Country for each Region”:
Looking at the above table lets us know at a glance how many countries operate from each geographical region. Creating a Custom Pivot TableThe suggestions provided by Google Sheets might not suit your needs, so you also have the option to build your own pivot table manually. Let us try to build a custom pivot table to analyze the following: “How many units of each item type were sold on the different sales channels before the year 2014?” Let’s break this question down to understand exactly what we want our pivot table to show. We want:
Now that we know what we want, let’s start building our pivot table. Inserting RowsTo display the list of Item Types in each row, click on the ‘Add’ button next to ‘Rows’ in your Pivot table editor. Select ‘Item Type’ from the dropdown.
The pivot table will obtain the names of all the Item Types from your data table and display them as shown below:
Note that the pivot table has displayed the list of items in alphabetical order and has removed any duplicates. Inserting ColumnsTo display the list of Sales channels across each column, click on the ‘Add’ button next to ‘Columns’ in your Pivot table editor. Select ‘Sales Channel’ from the dropdown.
The pivot table will obtain the names of the different Sales channels (‘Online’ and ‘Offline’) from your data table and display them as shown below:
Our spreadsheet pivot table is already starting to take shape. Inserting ValuesNow it is time to populate the pivot table’s cells with the number of Units sold for each Item type through each Sales channel. For this, click on the ‘Add’ button next to ‘Values’ in your Pivot table editor. Select ‘Units Sold’ from the dropdown.
You should now see the sum of units sold for each item on each sales channel.
Note that the Units Sold have been summed up and displayed in each cell. So for Baby Food, the pivot table summed up all the units sold Online and displayed them in cell C3. If you want to see the average number of units sold instead of total, simply click on the dropdown below ‘Summarize By’ (under Units Sold) and select ‘Average’. Note that the pivot table also displays the Grand total of Units sold for each channel (which it automatically calculates) at the bottom of the table. The Grand Total per item is also displayed on the right-most column of the table. Adding FiltersWe are already getting a lot of insights into our data from our created pivot table in Google Sheets. The last thing to do now is to narrow down our results so we see only the total number of units sold before sthe year 2014. For this we can use filters. To add a filter to your pivot table, follow the steps outlined below:
That’s all! We now have a pivot table that displays the sum of units sold for each Item type on each Sales Channel before the year 2014. How to Create a Pivot Chart in Google SheetsA pivot chart is simply a chart made from a pivot table. Like pivot tables, a pivot chart helps you get a focused visualization of the important parts of your data. Moreover, since it is based on a pivot table, it offers the same dynamism that a pivot table offers. Now there is no direct option for creating pivot charts in Google Sheets. You always need to first create a pivot table and then build the pivot chart from it. Here are the steps to create a pivot chart from an existing pivot table:
Here’s how the pivot chart based on our sample pivot table looks:
Once your chart is ready, there are a few things that you need to note.
Google Sheets Advanced Pivot TablesNow that you’re familiar with the basics of creating a pivot table and, subsequently, a pivot chart, let’s move to the level of Google Sheets Advanced Pivot Tables. Let’s move forward and look at some questions you might come across as you practice and start getting better at your pivot table skills. How to Refresh a Pivot Table in Google SheetsA common question about pivot tables is ‘How do I refresh my pivot table in Google Sheets after making changes to the original dataset?’ This is a question most Excel users might ask. However, unlike Excel, Google Sheets automatically refreshes pivot tables when the original dataset gets changed, so they don’t need to be manually refreshed. There may still be situations where you find the data in the pivot table not getting updated. In such cases, check out our tutorial on “How to Refresh Pivot Table in Google Sheets”, where we speak in detail about the possible causes and solutions for this. How to Create a Pivot Table in Google Sheets MobileGoogle Sheets Mobile users might find it difficult to create a pivot table from the app. This is because this feature is not yet available on the app. But if you still want to use your phone to create a pivot table, there’s an alternative route.
How to Hide the Pivot in Google Sheets Table EditorA lot of Google Sheets users would like to have the pivot table editor hidden once they’ve created a pivot table. However, at this point, there really is no way to hide the pivot table editor in Google Sheets from view. Every time you select a cell of your pivot table, the editor sidebar will appear, and there’s not much that you can do other than to simply close it. Create a Google Sheets Pivot Table from Multiple Other Sheets (and with a Dynamic Source Data Range)Often we have our data spread out over multiple sheets or tables. However, a single pivot table can only be created from one data span. So inherently, it is not possible to use data from different tables to create a single pivot table. However, there is a workaround if you really need to use data from multiple sheets in one pivot table. You can use an array to combine all the data tables into one common table, after which, you can use this array (or combined table) to create your pivot table! Let’s take an example to understand how to do this. Let’s say you have data spread out over two sheets. Say Sheet1 has the following 15 rows:
Say Sheet2 has the following 15 rows:
Now the first step is to create an array that combines both tables. Each table has entries ranging from cells A2:G16. So in a new sheet, add the column headers in the first row, and in the second row, enter the following formula: ={Sheet1!A2:G16;Sheet2!A2:G16}Now this will display all the rows from both the tables, and you can easily create a fresh pivot table from this.
Here’s a pivot table created from this list:
There’s one drawback with this formula though. If your aim is to create a pivot table with a Dynamic Source data range, then this will not work. This is because your formula is limited to just the cells between A2 and G16 from each sheet. So if you add a new row to any of the sheets, this will not get updated in your array, and thereby your pivot table. To solve this problem, you can remove the row numbers from the ending cell references in the formula, so that they contain only the column name. So instead of A2:G16, reference the cells A2:G. This way, no matter how many new rows you add or remove from your original data, the pivot table always remains up-to-date. This means instead of the above formula, you can use the formula shown below: =SORT({Sheet1!A2:G;Sheet2!A2:G})Notice we put the array inside a SORT function. This is because the references A2:G will refer to all the rows of Sheet1 and Sheet2. This means all the blank rows of these sheets will also get included in the array. To avoid that, we used a SORT function. This will ensure that all the blank rows of the resultant array are moved to the end, and the non-blank rows are all visible at the top of the array.
We now have a Google Sheets pivot table with a dynamic range!
Another thing you need to do in order to make your pivot table dynamic is to add a filter to it that displays only those rows that are not empty. This ensures that even if you delete a few rows, you don’t end up with blank rows in your pivot table. For example, take a look at what happens to your pivot table when you delete row 3 from Sheet1:
Here’s the resultant pivot table:
The first row of the pivot table is now blank because the row containing Item type =”Cereal” does not exist anymore. To make sure blank rows like these are not displayed in your pivot table, follow the steps shown below:
This should now get rid of any blank rows in your pivot table, and keep your pivot table fully dynamic.
ConclusionA pivot table Google Sheets addition to your spreadsheets will help you look at your data from different angles and perspectives. They condense your data such that you get maximum information with minimal distractions. In this tutorial, we introduced you to pivot tables and showed you how to create one with a simple example. We also showed you how to create a pivot chart in Google Sheets from a pivot table and answered some questions that are commonly asked by Google Sheets users when creating pivot tables. We hope this step-by-step tutorial was informative and helpful for you. |