How To Create A Pivot Table In Google Sheets – A Step By Step Guide

Step By Step Pivot Table Google Sheets Tutorial

A Google Sheets pivot table is a powerful tool available to use with a free Google Sheets account! If summarizing rows and rows of data has you overwhelmed, you don’t have to be. A pivot table can seem like a difficult tool to master, but with a little bit of guidance to get you started it may become one of your favorite ways to codify information. (I know it’s one of mine!)

So what IS a pivot table in Google Sheets? Simply put it’s a summary table of information linked to your source data. It can show you summarized averages, totals, percentages, etc. grouped according to values that you’ve entered in a list of data.

In my in-office career days I was a bit obsessed with excel spreadsheets. I used them for everything I could, primarily because I loved the countless ways that information can be organized, calculated, and summarized when it’s entered into a spreadsheet. I LOVED pivot tables. They were one of my favorite tools built into the excel software. I used them often to analyze extremely large lists of information and draw decision-guiding conclusions from what would otherwise be a lengthy list of administrative data.

I just recently discovered that while a little bit more limited in their design, pivot tables can now be created in Google Sheets! Of course this got me excited because Google Sheets is a free online software available to all of you my readers – so now I can share my love of pivot tables with all of you, no software purchase required!

In this post I’m going to talk you through some of the basic steps for how to make a pivot table in Google Sheets as well as share with you a video tutorial that I created to demonstrate creating a pivot table in Google Sheets with a sample set of sales information that I compiled.

(Just a note – if you are not familiar with Google Sheets, be sure to check out my more basic tutorial on how to build a spreadsheet to track income and expenses first. That tutorial outlines some of the basics of creating a spreadsheet and entering formulas.)

Below are step by step instructions to get you started playing with and creating your own Google Sheets pivot tables. The instructions are general instructions for creating ANY Google Sheets pivot table. However, all of the screen shots are from the below sample bulk list spreadsheet.

Google Sheets bulk list screen shot — (This list continued with many more rows)

I then created the below pivot table from the information contained in the bulk list spreadsheet above. (That’s just one example of how concisely a pivot table can summarize large lists of information!)

Read below for step by step instructions on how to create your own Google Sheets pivot table.

Instructions For Making A Pivot Table In Google Sheets:

Access a list of data in your Google Sheets account – You can’t create a pivot table in Google Sheets without first having a list of information that you want to create the pivot table from. This can be any list of information but some examples would be a list of all your sales or a list of your inventory by date.
Highlight all of the columns that you want to include in your pivot table and select Data>Pivot Table
Choose where you want your pivot table to be and select “Create” – You can choose to place your pivot table on a new sheet (new tab) or on your existing one. I almost always select “New sheet” here to avoid having too much information overlapping on one page.
Add the rows that you want to include in your pivot table – This is where things can start to seem really confusing. It might help to just play around with adding different things here until you figure out the groupings of data that work for your needs. If you want your Google Sheets pivot table to group by month or date this is a great place to do that. Simply click the “Add” button next to the rows header on your screen and select which column headers from your list of data that you want to have in the rows of your pivot table summary.
Choose ascending or descending for your rows and select row options – You can choose for each row value whether to have the pivot table display the values in ascending or descending order. Simply select this from the drop down menu that shows up after you add the row. If your table has months you will most likely want to have them in descending order. If you have dates for specific days you most likely will want to have them in ascending order. You can also select here what you want your rows to be sorted by although there is often only one option in that drop-down.
Choose the order of your rows – If you hover your cursor over the box pertaining to one of your row values you can click and drag it above or below your other row values in order on the table. It might help to just play around with different orders until you get your values aligned in an order that best depicts a summary of your data.
Add the columns that you want to include in your pivot table – Click add next to the columns header in your pivot table menu to add any of your list data into columns on your table. In many cases you may not even need to enter anything into the column section to create an effective summary table, but if you prefer column formatting to rows you can put those values here instead. (In my sample pivot table I don’t have any values added to this column section.)
Choose ascending or descending for your columns and select column options – You can choose for each column value whether to have the pivot table display the values in ascending or descending order. Simply select this from the drop down menu that shows up after you add the column. If your table has months you will most likely want to have them in descending order. If you have dates for specific days you most likely will want to have them in ascending order. You can also select here what you want your columns to be sorted by although there is often only one option in that drop-down.
Choose the order of your columns – If you hover your cursor over the box pertaining to one of your column values you can click and drag it above or below your other column values in order on the table. It might help to just play around with different orders until you get your values aligned in an order that best depicts a summary of your data.
Add the values that you want to populate your pivot table with – This is where your Google Sheets pivot table will really start to take shape! When you click “add” next to values in your pivot table menu you can select which values in your list you want to see summarized beside your rows (or under your columns). For example, in my sample spreadsheet I selected to add the quantity, unit price, average, and total profit here. That means that those values will show up corresponding to each date and month in my pivot table. You can even add one column from your bulk list more than once into the values section if you want. (I’ve done that in my sample pivot table.) Sometimes this is helpful if you want to see that column as a sum in one part of your pivot table and as an average in another. There is also a Google Sheets pivot table calculated field option available to add when you add in your values, but that option gets a lot more complex. (It’s an option I’ve rarely, if ever, used when creating my pivot tables.)
Choose what you want your values summarized by in your table – from the “Summarize by” drop-down menu beneath each of your values added you can choose how you want to see them in your table. There are a lot of different options, but I most commonly use sum, average, or count here. As an example, if you have dates as your row values then selecting sum here will add up all of the values on that date into a total. Or selecting count will count how many entries there are for that date. Or selecting average will average all of the entries on that date from your bulk list. You choose how you want to summarize each value that you add to your table and can even add the same value twice in this section but summarize it different each time. (For example, average profit and sum of profit in my example.)
Select how you want your values to be displayed – I most commonly just leave this option as default, but in the drop-down menu under “Show as” you can choose whether you want your value to be displayed as a percent of a row or column grouping or as a percentage of your grand total.
Select whether to add your values as rows or columns to your pivot table – In the drop-down menu directly beside the “Values as” header in your pivot table menu you can also decide whether to have your table values populated into columns or rows in your table. Again, you might just want to play with this to see which option best displays your summary data.
Select whether you want any filters on your table – the Google Sheets pivot table filter is an option you may not even need, but if you want to exclude any specific entries on your bulk list from being part of your pivot table you can enter that here. For example, if you don’t want any values less than 5 to be counted in your summary or if you don’t want any entries from the date 1/5 to be counted – you can enter those specific filters here by clicking “add” and selecting the right category from your bulk list. You can then filter that category with the drop-down menu that appears for it.
Change any desired titles in your table – Your table is basically complete after all of these steps, but if you want to change any of the titles that appear for rows or columns to make things easier to understand you can do that! Most cell names can be changed and you simply need to click into the cell that you want to rename and type in a new name in the bar across the top of the screen for cell entry. However, sometimes certain title cells cannot be changed. Those will appear with a grey title in the bar across the top when clicked on and it won’t allow you to click into the bar to retype a new name for it.
Add some custom format to your Google Sheets pivot table – Google Sheets pivot table format options are more limited than those built into excel for pivot tables, but you can still change up the look of your table some once you have it put together. One of the easiest ways is to go to “Format” in the top menu in Google Sheets and then select “Theme.” This will then provide you with several pre-set theme options on the right of your screen that you can select to change colors and fonts in your table. You can also click on rows or cells and format them individually like you would in any standard Google Sheets document. However, if you make changes that way just keep in mind that the formatting will not expand or minimize when you click on any “+” or “-” options within your table to see details. Therefore, formatting the cells individually is often not a great option.

Note on how to refresh a pivot table in Google Sheets:

In Google Sheets refresh pivot table is an automatic function. That means if you change the data in your bulk source list at any point it will automatically update your pivot table. You can verify exactly what information your pivot table is linked to or change it by clicking on the small grid right next to your spreadsheet title under the heading “Pivot table editor.”

(At this point I do not see the option to create a Google Sheets pivot table from multiple sheets of source data, so all of your source data would need to be in one list on one sheet.)

–

It may seem a little bit overwhelming at first, but all you need to do to create a great summary pivot table in Google Sheets is to add desired rows, columns, values, and filters in an order that presents your data as desired.

It can actually be pretty fun to play around with selecting different ways to present your values or different orders to move your rows and columns into to create a pivot table that shows you exactly what you need to see from your data.

Sometimes a bulk list of data can feel overwhelming and hard to draw any helpful conclusions from, but funneling the information into a pivot table can make it much more concise and useful! I hope that this tutorial was a helpful guide to get you started playing with pivot tables and learning how to summarize information in a way that provides useful business analysis.