If you ever have worked with data beyond a simple list, then you are familiar with the standard grid of rows and columns known as a spreadsheet. Commonly opened with Microsoft Excel or Google Sheets, spreadsheets can be used for all types of data. If you have used one of these tools more than once, then you have probably at least heard of the mythical "pivot table"... A mysterious tool that can immediately answers any question but can only be summoned by Spreadsheet Ninjas!
Well, we are here to tell you that:
- Yes, pivot tables really are that powerful because they turn your boring grid into an interactive report, allowing you to group and slice your data by simply dragging things around.
- Pivot tables should be easy! It's unfortunate that Pivot tables are considered out of reach for the average user. It's been estimated that only 5% of Excel users use a pivot table. At Gigasheet, where everyone is a data scientist, we want everyone to use pivot tables, not just the Ninjas!
Below, we are going to show you how to create pivot tables the easy way!
What is a Pivot Table?
A pivot table is a tool that allows you to quickly summarize data by grouping it into rows and columns. Rather than just seeing individual rows of data, the values in a pivot table are actually calculations, most commonly a total, but they can also be count, average, etc.
As they say, a picture is worth a thousand words. Here we see a file that contains raw data on Olympic medalists.
It appears that each row contains data about an athlete's medal count by year. For instance, the very first rows shows that Michael Phelps won 8 golds in 2008. The man's a fish!
But what if we want to see how many gold medals were won by the United States in each individual sport in 2008? That's where the magic of a pivot table comes in!
A pivot table can quickly answer that question, and many others:
Here we see our boring raw data transformed into a nice summary table.
- Rows now contain individual countries. The pivot table has taken all of the unique values in the "Country" column and grouped them into individual rows.
- Columns now contain individual sports. The pivot table has taken all of the unique values in the "Sport" column and grouped them into individual columns.
- The Grid now contains calculated values. We call these aggregations, and they represent the sum of all individual medals won by all the various athletes.
The pivot table has transformed my boring data into summarized data. Before each individual row was a single data point:
Michael Phelps won 8 gold medals in 2008.
In a pivot table, a row represents grouped and summarized data, telling us stories about the data as a whole:
The United States won 31 gold medals in swimming and 2 in gymnastics in 2008.
This quick summarization and easy insights are why we believe that, yes, pivot tables really are that powerful!
So How Do I Create a Pivot Table?
The reason that people believe that pivot tables are reserved for Spreadsheet Ninjas is that pivot tables require set up in both Microsoft Excel and Google Sheets. It's actually not that complicated of a process, but if you don't do it every day it's definitely something that would be easy to forget. Then you have to teach yourself how to do it again, and the options appear daunting, so you just say "Fuggedaboutit" and leave pivots to the Ninjas.
Before we show you how to make pivot tables the easy way, let's remind you how it's done in both Microsoft Excel and Google Sheets. We will use the same Olympic data referenced above.
How to Create a Pivot Tables in Microsoft Excel
Click Insert -> Pivot Table, and select From Table / Range
Excel will try to guess what data you want to pivot. If you want to change the selected range, click the up arrow, and change the selection.
Then, you choose where you want the pivot table to be placed. Generally a new worksheet, or tab, is a fine place for it to land so you don't need to change anything. If you choose Existing Worksheet, you can hit the up arrow to select a cell where the table will be placed. It must be placed outside the source data.
Hit OK.
A new worksheet will be inserted with a blank pivot table. All of the fields are listed in the top box, and the 4 parts of a pivot table (Filters, Rows, Columns, and Calculated Values) are below, waiting for you to drag columns down into the box. We will explain this in the next section!
How to Create a Pivot Table in Google Sheets
As you will see, creating pivot tables in Google sheets is very similar to Microsoft Excel.
Click Insert and select Pivot Table
Sheets will try to guess what data you want to pivot. If you want to change the selected range, click the grid icon, and change the selection.
Next, you choose where you want the pivot table to be placed. Generally, a new worksheet, or tab, is a fine place for it to land, so you don't need to change anything. If you choose Existing Sheet, a box will appear and you can click the grid icon to choose where to place the pivot table. It must be placed outside the source data.
Hit Create.
A new worksheet will be inserted with a blank pivot table. While Excel lists fields on the top, Sheets lists all available fields to the far right. The 4 parts of a pivot table (Filters, Rows, Columns, and Calculated Values) are in the center waiting for you to drag fields into the boxes. We will explain this in the next section!
Creating a pivot table in Microsoft Excel or Google Sheets is not difficult, but you can see that it is just complicated enough that you could easily forget the process if you don't do it often.
The 4 Parts of a Pivot Table
Now that have pivot tables created, how do we use them? No matter the application, pivot tables have the same basic parts.
1. Filters
Filters are exactly what they sound like, ways to filter out data. Drag columns into this box that you want to filter on. In our example above, we were focused on gold medals in 2008.
Thus, you would drag the "Year" column into this box and select only "2008". In Excel, you hit the triangle and select "2008". In Sheets, you click Status and select "2008"
2. Calculated Values
Calculated values are what is going to show up in the grid, aka the body, of the pivot table. Drag the column that you want to summarize into this box. In our example above, we were focused on gold medals, so move "Gold" into the calculated values box, referred to as Sum of Values in Excel and simply Values in Sheets.
The default calculation is sum, which will total up all of the gold medals. However, if you dragged a column with text into this box, the default calculation would be count. For instance, dragging "Athlete" into the values field would count all of the distinct athletes' names.
You can also change the calculation to other things besides sum or count, such as average, min or max. In Excel this is done by hitting the triangle and selecting Value Field Settings. Then change the Subtotals choice to Custom and make a selection. In Sheets, just change the Summarize By option to a different formula.
3. Row Groupings & 4. Column Groupings
These are listed together because they are essentially the same thing, groups of data based on the values of a selected column. Groupings pull all of the distinct values out of source column and use them to create the summary lines. The only difference between the two is that one shows up as rows and the other shows up as columns.
Let's say we choose to create a grouping based on country. If you want to see the countries listed as rows, drag "Country" to the Rows box. The country names will be listed out vertically in rows. Alternately, if you want the countries listed horizontally across the top as columns, drag "Country" to the Columns box and the data flip flops. Here is what it looks like both ways:
The real power comes when you add both a row grouping and a column grouping. Then you can summarize data cross two dimensions. This brings us back to our original pivot, where we group by country and sport.
This data is filtered on "2008", summarizing gold medals, with "Country" row groupings and "Sport" column groupings.
Create Pivot Tables the Easy Way with Gigasheet
At Gigasheet, our mission is to make Big Data available to everyone. We can do that because our product makes it REALLY easy to explore and analyze data. Pivot tables in Gigasheet are easier to use than in Microsoft Excel or Google sheets, and here is why:
No Set Up Because Everything is Already a Pivot Table
Earlier we established that the barrier to using pivot tables was simply the fact that you have to set them up. We even showed you how to do so in both Microsoft Excel and Google Sheets. What if we told you that everything in Gigasheet is already in a pivot table? That would mean that you are now a Spreadsheet Ninja! Congrats.
Let's dive into the 4 Parts of a Pivot Table again, but this time in Gigasheet:
1. Filters are More Powerful
Gigasheet makes it easy to create filters that contain layers after layers. Simply click the Filter button and a window appears, walking you through filter creation. Add logic using AND / OR operators to really dig into the data that you need.
Here is an example of a simple, yet complex filter that we could create on our Olympic medal data set. This would give us anyone who won a medal in 2008 from the US, India, or Canada.
2. Easy Row Grouping
Gigasheet makes grouping data front and center, because after filtering, this is the best way to explore data. Simply click the Group button to bring up the grouping pop up.
Select the column to group by, or select several, creating groups within groups. It's all very easy to do!
The groups will show up along the top. Drag them left and right to change the order.
3. Changing the Calculated Value formula is a single click away
Gigasheet makes it easy to change the calculation that is occurring on your grouped data. Simply click on the field and select a different value from the drop down.
4. Add a Column Grouping with a single toggle
If you want to add the column grouping, say by sport, just click the Pivot Mode toggle
Then drag a column to the Column Labels box.
And voila!
Just like in Microsoft Excel and Google Sheets, columns can be dragged between Row Groups, Column Groups and Sum of Values using the column menu at the right.
Gigasheet can Pivot Big Data
Not only is Gigasheet easy to use, but it is also very powerful. Don't let our buttery smooth front end deceive you... there is a monster database running behind the scenes! And that monster loves to eat pivot tables for breakfast.
- Microsoft Excel is limited to 1,048,576 Rows
- Google Sheets is limited to 10M cells, essentially the Excel row limit with only 10 columns
But Gigasheet can handle up to a Billion Rows in a single sheet. And since our sheets are already pivots, that means Gigasheet can pivot on a billion rows.
You can try it today for free!
Open the Olympic Medals data sample used in this blog and try it yourself.
The ease of a spreadsheet with the power of a data warehouse.
