Pinterest Pixel

Unleashing the Power of Pivot Tables in Excel: A Practical Guide

Excel’s pivot tables are a game-changer when it comes to analyzing and summarizing data efficiently. Whether you’re... read more

Download Excel Workbook
John Michaloudis
Posted on

Overview

Unleashing the Power of Pivot Tables in Excel: A Practical Guide

Excel’s pivot tables are a game-changer when it comes to analyzing and summarizing data efficiently. Whether you’re working with sales figures, survey responses, or any other large dataset, pivot tables provide a dynamic and intuitive way to extract valuable insights. In this article, we’ll explore the functionality of pivot tables in Excel through practical examples, demonstrating how they can transform raw data into meaningful information.

Now, let’s explore each of these steps individually!

Download the Excel Workbook below to follow along and start Unleashing the Power of Pivot Tables in Excel –
download excel workbookPivot-Table.xlsx

Pivot Tables Superpower: Answering Questions
Consider the example below. Let’s say we wanted to answer the question What is the amount sold by each salesperson? Answering it could be time consuming and difficult because each salesperson appears on multiple rows, and we would need to total all of their different orders individually. This is where a pivot table will come in handy.
Unleashing the Power of Pivot Tables in Excel: A Practical Guide
With Pivot Tables, we can easily calculate and summarize our data. Our final Pivot Table will look like this:
Unleashing the Power of Pivot Tables in Excel: A Practical Guide

Creating a Pivot Table
Now, let’s create the pivot table.

Select the dataset, including all four fields.
Unleashing the Power of Pivot Tables in Excel: A Practical Guide

Go to the Insert tab and click on PivotTable.

In the PivotTable dialog box, ensure that the correct range is selected and choose where you want to place the pivot table. For this example, choose a New Worksheet.
Unleashing the Power of Pivot Tables in Excel: A Practical Guide

Click OK to create the pivot table.

In the PivotTable Field List pane on the right, drag and drop the fields into the desired areas:

Drag the Salesperson field to the Rows area.
Unleashing the Power of Pivot Tables in Excel: A Practical Guide

Drag the Order Amount field to the Values area.
Unleashing the Power of Pivot Tables in Excel: A Practical Guide

If you want to change the summary calculation, you can click on the dropdown arrow next to Sum of Order Amount in the Values area, select Value Field Settings, and choose a different calculation, such as average or count. Furthermore, you can change the number format to reflect the currency of your data.
Unleashing the Power of Pivot Tables in Excel: A Practical Guide

Unleashing the Power of Pivot Tables in Excel: A Practical Guide

Your pivot table is now set up. It will display the Sum of Order Amount by each Salesperson, along with its’ grand total.
Unleashing the Power of Pivot Tables in Excel: A Practical Guide

Reminder: If you need to change the data in your source worksheet, the PivotTable won’t update automatically. To ensure the PivotTable reflects the latest data, you need to manually update it by selecting the PivotTable and then go to the Analyze tab, then click the Refresh button.

Adding Columns to the Pivot Table
To add columns to a pivot table, follow these steps:

Select the pivot table in Excel.

In the PivotTable Field List pane on the right, locate the Columns area.

Drag the desired field, such as Month, from the PivotTable Field List and drop it into the Columns area.
Unleashing the Power of Pivot Tables in Excel: A Practical Guide

The pivot table will automatically update, adding a column for the selected field. In this case, the column would represent different the different months.
Unleashing the Power of Pivot Tables in Excel: A Practical Guide

Excel will adjust the pivot table layout to include the new columns, providing a comprehensive view of the data across the added dimensions.

Changing a Row or Column
To change a row field, click and drag the desired field from the Rows area in the PivotTable Field List pane to the Columns area. For example, you can drag the Month field from the Rows area to the Columns area.
Unleashing the Power of Pivot Tables in Excel: A Practical Guide

To change a column field, click and drag the desired field from the Columns area in the PivotTable Field List pane to the Rows area. For example, you can drag the Month field from the Columns area to the Rows area.
Unleashing the Power of Pivot Tables in Excel: A Practical Guide

As you drag and drop the fields, the pivot table will automatically update to reflect the changes, reorganizing the data accordingly. The row and column fields will be swapped, resulting in a different layout for the pivot table.

There you have it! The pivot table feature in Excel empowers users to efficiently summarize and analyze extensive datasets. By quickly organizing, grouping, and calculating data, it lets users gain new perspective on the data.

If you like this Excel tip, please share itEmail this to someone

email

Pin on Pinterest

Pinterest

Share on Facebook

Facebook

Tweet about this on Twitter

Twitter

Share on LinkedIn

Linkedin

Founder & Chief Inspirational Officer at MyExcelOnline.com

John Michaloudis is a former accountant and finance analyst at General Electric, a Microsoft MVP since 2020, an Amazon #1 bestselling author of 4 Microsoft Excel books and teacher of Microsoft Excel & Office over at his flagship Academy Online Course.

Get Video Training

Advance your Microsoft Excel & Office Skills with the MyExcelOnline Academy!

Dramatically Reduce Repetition, Stress, and Overtime!
Exponentially Increase Your Chances of a Promotion, Pay Raise or New Job!

Learn in as little as 5 minutes a day or on your schedule.

Learn More!