What is a Pivot Table and Why Should I Use One?

By Matthew Rathbone on December 07, 2015

A pivot table is a tool found in both Excel and Google Spreadsheets.

The main goal of this handy tool is to summarize and make sense of large amounts of data. They’re also interactive, meaning Google and Microsoft’s pivot table functions help us sift through data to exclude unnecessary points, combine large chunks of information and find big-picture ideas, such as averages and sum totals. In a business setting, we may want to use pivot tables to examine:

  • Total spending on expense reports
  • Average annual profit
  • Most successful store branches and ranking order
  • Our most popular selling products

Pivot tables also offer the ability to quickly sort data in a variety of ways, which saves time and energy when examining different areas of our data.

Example of a Pivot Table at Work

For example, if we ran a chocolate business (yum) and had a spreadsheet that mapped out chocolate sales, purchases, shop locations, price and profit, we could use a pivot table to demonstrate a variety of different figures.

Here’s what our spreadsheet might look like placeholder

We could use a pivot table to highlight the most popular chocolate by choosing to group by chocolate name and sum the total sales. This gives us a chart of total sales per chocolate that we can order by sales amount to find the most popular.

That might look something like this:

basic pivot

In this example, it let us identify that while we sell fewer Bacon Chocolate bars than Milk Chocolate bars, they make us more money, which is really good to know.

If we wanted to restrict our research to sales within a particular time period, we could easily ad a filter for “order date.” This could be accessed through the filtering tool in the pivot table.

filtered pivot

While this example is fabricated, I hope you can see the power of pivot tables in exploring all sorts of data. The best part is that they only take a minute or two to build.

Wrap Up

Pivot tables help us consolidate and summarize information to make a ton of data much more readable. It allows us to easily shift and combine information to identify trends and drill down specific findings us are looking for. When large Excel files or Google Spreadsheets seem unmanageable, pivot tables make sense of the mess.

Pivot Table Resources

Here’s a great video walkthrough of how to build a pivot table by Jon Flynn

Using Pivot Tables for Reporting?

Using pivot tables to explore data is great, but once you start trying to share the insights with colleagues, customers, and partners it can become time consuming to keep on top of everything. If you need to share reports, you might want to check out our reporting product, we make reporting simple and effective.

Still Writing SQL in a Terminal?

Beekeeper is a free and full featured SQL IDE with query autocomplete, error detection, dataset previews, and result visualization. We support SparkSQL, Hive, Postgres and more!

Create your account »

Matthew Rathbone bio photo

Matthew Rathbone

CEO of Beekeeper Data. British. Data nerd.

Big Data Blog Email Twitter Github Stackoverflow