Data-led: When to use a Pivot Table

What is a pivot table?

A pivot table is a way of grouping data into aggregates. The aim is to organise the data to make it easier to analyse the information and spot patterns and trends. Though the interface and some specifics are different, pivot tables are available in both Excel and Google Sheets.

An example of a Pivot Table in Google Sheets

Why would you want to use them?

There are quite a few benefits to using pivot tables. They can be very simple to set up and provide a quick overview of your data. This allows you to verify your data’s integrity at a top level without having to use a bunch of formulas and potentially confusing interactions. They also let you compare datasets side-by-side easily by selecting multiple datasets at once. 

They generally handle medium-sized databases (less than one million rows due to sheet limitations) without the need to learn a new coding language or use another software, making them very accessible. You can update the range of data while keeping the overall table structure, too. This means that if you update the raw data, the pivot table updates, too (though you do have to refresh the data in Excel, whereas Google Sheets does this automatically). If you create some pivot charts attached to this output, you can create a full dashboard which updates as soon as your data does, snazzy! 

One of the ways we use pivot tables at NOVOS is after we perform our keyword research and clustering. They give us great insight into which categories we should focus on for a client at a top level without having to spend a lot of time manipulating the data with formulas. 

So why wouldn’t you want to use them?

To truly master using pivot tables, it can take a lot of time and is often described as overwhelming when first exploring them as an option. 

Pivot tables can sometimes miss some of the more important details within the dataset itself. Things like outliers often get missed and not taken into account, skewing the output of the pivot table. It’s also difficult to manipulate the data from the output of a pivot table, so any further calculations or formulas you may want to apply need extra steps to make it manageable. 

Sometimes you want to perform a specific calculation or apply a formula to your dataset, and a pivot table can be very limiting when it comes to this with preset aggregations and calculations like sum and mean.

The last thing to consider is they can be quite processor intensive for computers to load, especially with larger datasets, multiple inputs or multiple other calculations running.

Summary – When to use Pivot tables

The best time to use a pivot table is when you have a computer that can handle it, you have already performed proper cleaning steps (like removing outliers and amending spelling errors), and you’re looking to either validate your data or analyse it to spot trends. If you struggle with the finer details of using formulas in conjunction with each other, are looking to make a dashboard, or your data is static, a pivot table is a solid option.

If you have a solid working knowledge of formulas and how they interact, you may want to create similar summaries without using pivot tables to allow for further analysis, ensure the data is accurate and allow for more complex synergy between the formulas.

An example of combining formulas to create the same result as the previous Pivot Table Example

To learn more about our data-led approach to SEO strategy, explore our services page or visit our get in touch page to speak with us directly.