In this article:

Jumpstarting Data Analysis with Pivot Tables

A Pivot Table is a spreadsheet feature that can summarize and sort large data sets into coherent reports. Pivot Tables are one of the most powerful spreadsheet tools, and they enable users to accomplish data-heavy tasks efficiently. Though Pivot Tables are mainly associated with Excel, we’ll go through Pivot Table functionality and creation in this article.

Hallway with two rows of black data processors

Pivot Table Use Cases

Google Sheets table with column headers (i.e. Product, Color) and corresponding rows of data

Are you someone that frequently works with large amounts of data? Do you often need to change the statistics that you use to analyze your data and/or the specific data that you need to analyze? If you answered yes to either of these questions, then Pivot Tables can drastically reduce the time you spend analyzing your data and make this analysis much more accurate. 

Large amounts of complicated data are scary to me, and I love Pivot Tables because they help ease my fears (I’m sure you feel this way too). You may be a little skeptical of the unlimited power of Pivot Tables, so let’s start with a simple example. Say I’m a D2C brand that manages a relatively small collection of products with a spreadsheet that looks like the picture on the right. 

As you may have learned from some of our spreadsheets articles, you could easily use AVERAGEIF or COUNTIF functions to average the prices of all of your different colored shirts or count how many different colored shirts you offer. You could also quickly create a Pivot Table that has this functionality and does not require you to write any formulas. The Pivot Table below took 5-10 mouse clicks to create, breaks down my product by color, and averages the price and weight over the product, product type, and total product portfolio. 

Google Sheets pivot table with Product type and corresponding averages/totals filled in

Imagine if, like most DTC brands, you had hundreds of products and kept 10-20 statistics on each product. In that case, knowing how to use a Pivot Table is not only helpful, but necessary for running your business effectively. 

Using Excel functions will work every time, but a Pivot Table is much more effective because you can do the same thing in only a few clicks without the mistakes that come with writing formulas.

Pivot Tables aren’t just limited to DTC brands. Imagine that you are a salesperson with hundreds of leads. Odds are you keep track of your lead demographics, such as age, geography, lead source, lead potential, etc. in a spreadsheet. Maybe every day you analyze different demographics of your lead pool to find high-potential customers. After creating a Pivot Table, you can mix and match data extremely fast without the pain of writing new formulas or creating a new table. The benefits for salespeople and DTC brands extend to every job that frequently needs to evaluate data and needs a tool that is powerful and versatile enough to change statistics and data sources in an instant. 

Building Powerful Pivot Tables

Excel

Now that you are as excited as I am about Pivot Tables, let’s learn how to build them. Here are some easy instructions to follow for building a Pivot Table: 

  1. Select all of the data that you’d like to analyze in Excel 
  2. Go to the “Insert” tab and hit “PivotTable” on the far left. You can then choose where you want to place the Pivot Table in your document. 
"Create PivotTable" menu pop-up on Excel with selected settings, as listed abo

  1. After you hit “OK”, you can use the “PivotTable Fields” box, to edit the composition of the rows and columns of your Pivot Table as well as the summary statistics for your analysis. 
"PivotTable Fields" box on Excel with options to add fields, filters, rows, columns, and values
  1. After that, you can edit your Pivot Table by right-clicking in the table and clicking “Show Field List.” 
  2. If you want to create a graph with your data, go to the “PivotTable Analyze” tab and click “PivotChart” on the far right (Pivot Charts are visual Pivot Tables and are much more flexible than a typical chart). 
Custom chart made from the Excel Pivot Table described above

Other Spreadsheet Tools

For users of cloud-based spreadsheet tools, if you’re already familiar with building Pivot Tables in desktop spreadsheet programs, the process will feel very similar.

Here’s how to build a Pivot Table:

  • Click anywhere within your dataset
  • Open the Data menu and select Pivot table
  • A Pivot Table editor will appear, allowing you to customize rows, columns, values, and filters—just like in most spreadsheet applications.

Another tool that pairs well with Pivot Tables to form a powerful strategy for optimizing your data are Dashboards and our Dashboards 101 article is a great place to start.  

Related Articles

Enhance Your Sheet Mastery Using Logical Functions
How to Hire a Virtual Assistant (The Ultimate Step-By-Step Guide)

Turn PDFs into spreadsheet tables with AI

Use Lido to extract data from contracts, invoices, financials, and more in seconds. Save time, reduce errors.
  • No credit card required
  • 20 free pages