top of page
horizontal lines
Gigasheet Primary logo
  • Will Andrews

How To Create Pivot Tables The Easy Way

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:

  1. 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.

  2. 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.

Raw olympic medal data

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:

Pivot table showing gold medals by country and sport

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

Inserting a pivot table in Excel

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.