How To Create, Edit And Refresh Pivot Tables In Google Sheets

Spreadsheets are a great way for data geeks to organize, display, and analyze information, but for “the rest of us” they can sometimes be a bit baffling. Pivot tables are an exceptionally powerful tool within spreadsheets for presenting data, but they can be very confusing for novice users, particularly when you consider that the interfaces for creating the tables pretty much assume you already know everything there is to know about them. If, like me, this is not the case for you, then hopefully you will find this article informative and helpful. I will present a basic tutorial on creating, editing, and refreshing pivot tables in Google Sheets.

First, let’s talk about what a pivot table is. A pivot table summarizes the data from another table by performing some kind of manipulation on the original data (such as summing, counting, or averaging), and then groups the summarized data in a useful way. That definition was probably not terribly helpful if you don’t already know how all of this works. I wrote it, and I’m still confused myself. Instead of trying to define it, let’s create a data table and then build a pivot table based on it, so that we can more clearly understand how this concept works.

Let’s say we have a real estate office, and we sold a number of houses last year in a few different neighborhoods. So we would like to create a table of how much money each house sold for. Here’s our table (obviously just a short version, as a real table would have a lot more data, but let’s keep it simple):

We’d like to take this information and figure out which neighborhoods have the highest average sale prices. We could do it by hand, and it would be easy for this little table, but imagine if we had hundreds of sales to look at. So how do we easily turn this sales information into a useful pivot table that will show us the average sales price by neighborhood?

Create pivot tables in Google Sheets

1. Select all the data (including the headers) by selecting the bottom- right corner cell and pressing Ctrl + A.
   
2. Select Data and then Pivot tables from the menu.
   
3. Google Sheets will create a new sheet with a blank grid.
4. Select which rows, columns, values, and filters to use from the right menu. Note that Sheets suggests a pre-made table for us; in fact, it’s exactly what we want! Select “Average of Sale Price for Each Neighborhood”.
   

And here it is!

Google Sheets will now show the corresponding data in the pivot table. You can select any kind of dataset you like and create results as Grand Totals. You can compare instances, values, how many times a value appears, SUMs, and more, all by selecting the type of report in the right pane. Just click inside the pivot table to bring up the reporting pane, and play with the values there, and you can see all the different ways that you can configure your tables. We can easily make this table show us the minimum or maximum sales price in a neighborhood, or how many houses sold per neighborhood, or any of a number of other ways to visualize the data.

What if we want to change our pivot table?

Edit pivot tables in Google Docs

You can edit a pivot table just like you would any other spreadsheet. You just have to edit the data being used in a particular field for it to be reflected in the table.

1. Open the Google Sheet with data being used by the pivot table.
2. Edit the data you need to change as required.
   
3. The pivot table will automatically update.
   

It’s important not to change the data within the pivot table itself as this will corrupt the table, and then you’ll need to start the entire process over again. You only need to modify the data within the sheet being used by the table. The table takes care of itself.

Refresh a pivot table in Google Sheets

You don’t usually need to manually refresh a pivot table in Google Sheets. When you update the data being pulled by the table, the pivot table should dynamically update. No user action is needed to refresh the table.

There may be times when this doesn’t happen correctly and that is usually because you have a filter running on the table.

1. Open the Google Sheet with the pivot table.
2. Select a column header so the Report editor menu appears on the right.
3. Check the filter field for each column to make sure there are none. You should see ‘Add field’ by the Filter heading if there are no filters running.
   

If there are filters, select the little gray ‘X’ on the right of the window to remove them. You can always bring them back should you need to.

Add a chart to a pivot table in Google Sheets

As well as collating data in a pivot table to display data in a usable way, you can also use charts as a graphic demonstration of your findings. Here’s how to integrate a chart into a pivot table.

1. Open the Google Sheet with the pivot table.
2. Select all columns you want to include in the chart.
   
3. Select Insert and Chart.
   
4. Select the chart type and edit colors, the legend, and whatever aspects you need to change.
   

The chart appears immediately, and like the table, will dynamically update as you change the core data. Now you not only have a cool pivot table to impress your boss, but some pretty looking charts too! And now all of your coworkers will ask you how you did it and if you can do it for them, so maybe keep a flyswatter on hand.

Got any other pivot table tips to share? Tell us about them below if you do.