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. If you’d like to start at a broader level before you dive into Pivot Tables, be sure to check out our Spreadsheet 101 and Advanced Google Sheets Features articles. 

‍

‍

Pivot Table Use Cases

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. 

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. 

‍

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

4. After that, you can edit your Pivot Table by right-clicking in the table and clicking “Show Field List.” 
5. 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). 
   

Google Sheets

For Google Sheets users (like me), if you know the basics of building a Pivot Table in Excel, you’ll automatically be proficient in building one in Google Sheets.

Here are some easy instructions for building Pivot Tables in Google Sheets: 

1. After clicking somewhere in your data, go to the “Data” tab in Google Sheets
2. Click “Pivot table” in the middle of the dropdown menu (yes, the only difference in naming convention between Excel and Google Sheets is a space) 
3. A Pivot Table editor will open on the right side of your screen and, like Excel, you’ll be able to customize your Pivot Table.
   

Other Helpful Resources

Now that you’ve learned how to build a Pivot Table, feel free to read our articles on Popular APIs and Unstructured Data to learn about expanding the types of data in your spreadsheets (and making your Pivot Tables even better as a result). 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.  

• Microsoft Support Pivot Table Guide
• Ben Collins Pivot Tables in Google Sheets
• Excel Easy Update Pivot Table Guide