What is a Pivot Table? How to Use Them?

If you analyze data for a living or you’ve recently been asked to understand or arrange complex statistics, you may want to consider using a pivot table. No matter the amount of data, pivot tables can make your life far easier. 

Pivot tables can also be exceptionally valuable tools for analyzing data when buying or selling businesses and can be used for various functions. I’ll explore what a pivot table is and how to use them.

What is a Pivot Table?

Picture this scene: You open your spreadsheet to find what feels like thousands of numbers jumbled up. There are multiple columns and row labels that you need clarification on. Rather than spending hours making sense of the underlying data, you can use a pivot table instead. 

A pivot table is a tool that allows you to extract meaning from a large data set by letting you select the data and group it in varying ways so you can draw conclusions. 

But why is it called a “pivot” table? Well, because it allows you to pivot the data sources and see it from a different perspective. You’re not changing anything about the data range; you’re reshuffling it for better data visualization. 

What are Pivot Tables Used For?

You’re probably wondering what you can use pivot tables for. Data analysts will use this tool to analyze their original data to uncover new findings quickly. Let’s look at six possible scenarios where pivot table data might be helpful. 

1. Analyzing sales data by product, region, and time period

Data analysts commonly use pivot tables to see their total sales data in a new light. They can summarize and analyze sales data to uncover new patterns in a matter of seconds. At the same time, sales managers can quickly identify their best-performing products to optimize inventory levels and create a data-driven approach to marketing. 

2. Tracking marketing campaign performance

Without understanding how your customer base interacts with your products, you won’t be able to create a marketing campaign that truly packs a punch. Luckily, a pivot table provides easy steps toward aggregating sales data for the purposes of marketing. 

With your existing rows and columns full of valuable data points, you can analyze your campaign performance by segmenting click-through rates (CTR), conversation rates, and your return on investment (ROI). 

3. Identifying customer trends

Pivot tables can help you uncover valuable customer trends by examining your data from different angles. Whether it’s purchase behavior or demographics, pivot tables help you to identify patterns that may go unnoticed in your raw data.

4. Managing financial data

Financial professionals rely on pivot tables to make sense of their complex financial data. Typically using Excel templates, adding pivot tables helps them to create insightful financial reports and quickly spot trends.

5. Measuring employee productivity

HR departments sometimes use pivot tables to analyze employee productivity quickly using individual and team performance data. 

6. Forecasting future sales

In business strategy, forecasting future sales is vital. Pivot tables help analysts study past sales to recognize patterns contributing to their future strategies. 

Related: How to use data to drive up business value?

What Tools Can You Use to Create Pivot Tables?

Pivot tables are available on many spreadsheet applications, the most popular being Microsoft Excel and Google Sheets. We’re covering five tools you can use to create your pivot table automatically and offering a how-to guide for each one. 

1. Microsoft Excel

Creating pivot tables in Excel is easy when you follow these steps. This will work in all versions of Excel. 

Step one: Insert your pivot table

• Select the data you want to analyze using your mouse click to drag your cursor over the data. 
• You can also select all the data on your worksheet by clicking the triangle at the top left corner of your existing worksheet. 
• Once you’ve selected the data, click “Insert” and select “Pivot Table” in the top menu. 

Step two: Drop variables into the correct box

• Now you’ve selected the PivotTable Builder, you’ll have access to four boxes: “Filters,” “Table Columns,” “Rows,” and “Values.” 
• From here, you can arrange your variables to create your output. 
• You can drag and drop your variables into the columns and rows boxes depending on what information you want to uncover. 
• Let’s say you want to find patterns in your sales data depending on your products and timescales. 
• To uncover this, you’d drag your “Product” data into the “Rows” box and “Dates and Times” into the “Columns” box. 

Step 3: Set up the calculation

• Once you’ve arranged your variables, you’ll need to specify the calculation you want to apply to your subset of data. 
• This is done by inputting a calculation in the “Values” box.
• After dragging a variable into the “Values” box, click on the drop-down menu next to it. Standard calculations are “SUM” and “AVERAGE.” 
• For example, if you want to determine the sum of your sales, choose “SUM.”
• After you finish the pivot table editor, you can sort it from highest to lowest. 
• To do this, right-click on the data within the pivot table, select “Sort,” and then choose “Sort Largest to Smallest.”
• You’re now ready to create an Excel pivot table.

2. Google Sheets

Using pivot tables in Google Sheets is a handy way to analyze complex data. Let’s go through our guide on using pivot charts for your tables in Google Sheets:

Step 1: Insert the pivot table

• Start by going to your Google Sheets Dashboard and opening your Google spreadsheet containing the data you want to analyze. 
• Click and drag your cursor to select all the data. 
• You can also quickly select all the data by clicking the top left corner of the spreadsheet or by using the keyboard shortcut CTRL + A.
• Once your data is selected, go to the top menu and click “Insert.” 
• From the dropdown menu, select “Pivot table.” 
• This will initiate the process of creating your pivot table fields.

Step 2: Choose where to create the pivot table

• You can choose where to create the pivot table in Google Sheets, with the option to create it in a new or existing sheet. 
• It’s often easiest to select “New Sheet” to create the pivot table field in a separate sheet to keep your original data intact and make your analysis more organized.

Step 3: Customize the pivot table

• Google Sheets provides automated suggestions for your pivot table. 
• While this is convenient, using your own input for more precise analysis is often recommended.
• For more control, click “Add” on the right-hand side of the pivot table editor to create your personalized pivot table.
• Similar to creating a table in Excel, you can manually add variables to the pivot table in the “Rows,” “Columns,” “Values,” and “Filters” sections. 
• Depending on your analysis goals, you can drag and drop variables into these sections to customize the layout.
• After following these steps, you can create multiple pivot tables in Google Sheets.

3. LibreOffice Calc

Creating a pivot table in LibreOffice can help you automatically analyze and summarize your data. Here are some step-by-step instructions to create a pivot table layout.

Step 1: Open LibreOffice Calc

• Launch LibreOffice Calc and position the cursor within a range of cells that contain the data you want to use in your pivot table. 
• This will include your row and column headings and all your columns values. 

Step 2: Insert pivot table

• Navigate to the top menu on LibreOffice and select “Insert.” 
• From the dropdown menu, choose “Pivot Table.” 
• This will open the “Select Source” dialog.

Step 3: Select the data source

• In the “Select Source” dialog, make sure you select “Current selection” and then confirm by clicking the “OK” button. 
• Your data headings will appear as buttons in the “Pivot Table” dialog box. 

Step 4: Arrange fields

• Drag and drop the desired buttons into the “Page Fields” area.
• This will create a button and a list box on top of the pivot table, allowing you to filter the table by the contents of the item you selected.
• Drag and drop buttons into the “Column Fields” area to create the columns of your pivot table.
• Drag and drop buttons into the “Row Fields” area to create the rows of your pivot table.
• Drag buttons into the “Data Fields” area to specify the data you want to display in your pivot table. 

Step 5: Customize data fields

• If you want to adjust the calculation for data fields, double-click your buttons in the “Data Fields” section. 
• It will open the “Data Field” dialog.
• In the “Data Field” dialog, select the calculations you want to use for the data. 
• Hold the “Command” key (Ctrl key on Windows) to make multiple selections while clicking the calculation. 

Step 6: Reorder and remove fields

• You can change the order of the buttons in the “Page Fields,” “Column Fields,” and “Row Fields” areas at any time by moving them to a different position using the mouse. 
• If you want to remove a field from your pivot table, simply drag it back to the area with the other buttons on the right side of the “Pivot Table” dialog.

Step 7. Finalize the pivot table

• Once you’ve configured your pivot table to your liking, exit the “Pivot Table” dialog by pressing the “OK” button.
• The pivot table will be inserted further down in your spreadsheet, and you can interact with it to analyze your data. 
• We’ve now shown you a pivot table example for LibreOffice, and you’re ready to start properly analyzing your source data. 

4. Apple Numbers

Creating a pivot table in Apple Numbers is a great database management tool that helps you summarize your complex numerical values. Let’s take a look at how you can use this tool with a step-by-step guide:

Step 1: Select the data source

• Open your Numbers spreadsheet and elect a table or a range of cells you want to use as the source data for your pivot table.
• If you want to add a pivot table to a new sheet, select a table, then choose “Pivot Table” from the toolbar.

Step 2: Access pivot table options

• In the Numbers menu bar at the top of your screen, choose “Organize.”
• From the “Organize” menu, select “Create Pivot Table.”

Step 3: Choose a pivot table type

After selecting “Create Pivot Table,” you will be presented with the following options:

• On New Sheet:  Creates a pivot table on a new sheet using the entire table as the source data.
• On Current Sheet: Creates a pivot table on the current sheet using the entire table as the source data.
• For Selected Cells on New Sheet: This creates a pivot table on a new sheet using only the selected cells as the source data.
• For Selected Cells on Current Sheet: This creates a pivot table on the current sheet using only the selected cells as the source data.

Step 4: Add and arrange fields

• To populate your pivot table, you need to add and arrange fields. Click “Add and arrange pivot table data” to configure your table.

Step 5: Configure the pivot table

• Now, you can configure your pivot table depending on your unique values. To organize and summarize all your data, you can select which fields to include in the Rows, Columns, and Values sections.

Summing Up

Whether you’re a data analyst or a business strategist, pivot tables can go a long way in making your life easier. Rather than going through all your cells in Excel, pivot tables can help you apply the filters you desire to uncover new findings from your raw data, which can be an invaluable tool for anyone looking to improve their business’s operational efficiency.
Pivot tables can also be used to analyze large datasets, create powerful visualizations to analyze a company’s financials and inventory, and even conduct due diligence for buying and selling businesses.