Excel Pivot Tables – Best article

Pivot tables are one of the most powerful and useful features in Excel. With very little effort, you can use a pivot table to build good-looking reports for large data sets. This article is an introduction to Pivot Tables and their benefits, and a step-by-step guide with sample data.

The beauty of pivot tables is they allow you to interactively explore your data in different ways. Grab the sample data and give them a try. Learning Pivot Tables will pay you back again and again.

What is a pivot table?

You can think of a pivot table as a report. However, unlike a static report, a pivot table provides an interactive view of your data. With very little effort (and no formulas) you can look at the same data from many different perspectives. You can group data into categories, break down data into years and months, filter data to include or exclude categories, and even build charts.

Contents

Step by step tutorial

To understand pivot tables, you need to work with them yourself. In this section, we’ll build several pivot tables step-by-step from a set of sample data. With experience, the pivot tables below can be built in about 5 minutes.

Sample data

The sample data contains 452 records with 5 fields of information: Date, Color, Units, Sales, and Region. This data is perfect for a pivot table.

Sample sales data already in an Excel Table

Data in a proper Excel Table named “Table1”. Excel Tables are a great way to build pivot tables, because they automatically adjust as data is added or removed. 

Note: I know this data is very generic. But generic data is good for understanding pivot tables – you don’t want to get tripped up on on a detail when learning the fun parts.

Insert Pivot Table

1. To start off, select any cell in the data and click Pivot Table on the Insert tab of the ribbon:

Click the button at Insert > Pivot Table

Excel will display the Create Pivot Table window. Notice the data range is already filled in. The default location for a new pivot table is New Worksheet.

2. Override the default location and enter H4 to place the pivot table on the current worksheet:

Create Pivot Table window

3. Click OK, and Excel builds an empty pivot table starting in cell H4.

New empty pivot table staring at cell H4

Note: there are good reasons to place a pivot table on a different worksheet. However, when learning pivot tables, it’s helpful to see both the source data and the pivot table at the same time.

Excel also displays the PivotTable Fields pane, which is empty at this point. Note all five fields are listed, but unused:

Fields pane for new empty pivot table

To build a pivot table, you  drag fields into one the Columns, Rows, or Values area. The Filters area is used to apply global filters to a pivot table.

Note: the pivot table fields pane shows how fields were used to create a pivot table. Learning to “read” the fields pane takes a bit of practice. See below for more examples.

Add fields

1. Drag the Sales field to the Values area.

Excel calculates a grand total, 26356. This is the sum of all sales values in the entire data set:

Grand total of all data in data set

2. Drag the Color field to the Rows area.

Excel breaks out sales by Color. You can see Blue is the top seller, while Red comes in last:

Breakdown by color

Notice the Grand Total remains 26356. This makes sense, because we are still reporting on the full set of data.

Let’s take a look at the fields pane at this point. You can see Color is a Row field, and Sales is a Value field:

Pivot table fields pane - sales by color

Number formatting

Pivot Tables can apply and maintain number formatting automatically to numeric fields. This is a big time-saver when data changes frequently.

1. Right-click any Sales number and choose Number Format:

Right-click and select Number Format

2. Apply Currency formatting with zero decimal places, the click OK:

Currency number format with zero decimal places

In the resulting pivot table, all sales values have Currency format applied:

Pivot table with Currency format applied

Currency format will continue to be applied to Sales values, even when the pivot table is reconfigured, or new data is added.

Sorting by value

1. Right-click any Sales value and choose Sort > Largest to smallest.

Right-click and select Sort > Largest to smallest

Excel now lists top-selling colors first. This sort order will be maintained when data changes, or when the pivot table is reconfigured. 

Breakdown by color, top selling colors first

Refresh data

Pivot table data needs to be “refreshed” in order to bring in updates. To reinforce how this works, we’ll make a big change to the source data and watch it flow into the pivot table.

1. Select cell F5 and change $11 to $2000.

2. Right-click anywhere in the pivot table and select “Refresh”.

Notice “Red” is now the top selling color, and automatically moves to the top:

Pivot table after data refresh

3. Change F5 back to $11 and refresh the pivot again.

Note: changing F5 to $2000 is not realistic, but it’s a good way to force a change you can easily see in the pivot table. Try changing an existing color to something new, like “Gold” or “Black”. When you refresh, you’ll see the new color appear. You can use undo to go back to original data and pivot.

Second value field

You can add more than one field as a Value field.

1. Drag Units to the Value area to see Sales and Units together:

Breakdown by color with Sales and Units

Percent of total

There are different ways to display values. One option is to show values as a percent of total.

1. Remove the Units from the Values area

2. Add the Sales field *again*  to the Values area.

3. Right-click the second instance and choose “% of grand total”:

Right-click select Show values as > percent of total

The result is a breakdown by color along with a percent of total:

Pivot table - breakdown by color with percentage

Note: the number format for percentage has also been adjusted to show 1 decimal.

Here is the Fields pane at this point:

Pivot table fields pane - sales by color with percentage

Group by date

Pivot tables have a special feature to group dates into units like years, months, and quarters.

1. Remove the second Sales field (Sales2). 

2. Drag the Date field to the Columns area.

3. Right-click a date in the header area and choose “Group”:

Right click a date and select group

4. When the Group window appears, group by Years only (deselect Months and Quarters):

Date grouping settings - group by Years only

We now have a pivot table that groups sales by color and year:

Two-way pivot table - sales by color and year

Notice there are no sales of Silver in 2016 and 2017. We can guess that Silver was introduced as a new color in 2018.

Here is the Fields pane at this point:

Pivot table fields pane - sales by color and by year

Two-way Pivot

1. Drag the Date field out of the columns area

2. Drag Region into the Columns area.

Excel builds a two-way pivot table that breaks down sales by color and region:

Two-way pivot table - sales by color and region

3. Swap Region and Color (i.e. drag Region to the Rows area and Color to the Columns area). 

Excel builds another two-dimensional pivot table:

Two-way pivot table - sales by region and color

Again notice total sales ($26,356) is the same in all pivot tables above. Each table presents a different view of the same data, so they all sum to the same total.

The above example shows how quickly you can build different pivot tables from the same data. You can create many other kinds of pivot tables, using all kinds of data.

Key Pivot Table benefits

Simplicity. Basic pivot tables are very simple to set up and customize. There is no need to learn complicated formulas.

Speed. You can create a good-looking, useful report with a pivot table in minutes. Even if you are very good with formulas, pivot tables are faster to set up and require much less effort.

Flexibility. Unlike formulas, pivot tables don’t lock you into a particular view of your data. You can quickly rearrage the pivot table to suit your needs. You can even clone a pivot table and build a separate view.

Accuracy. As long as a pivot table is set up correctly, you can rest assured results are accurate. In fact, a pivot table will often highlight problems in the data faster than any other tool.

Formatting. A Pivot table can apply automatically apply consistent number and style formatting, even as data changes.

Updates. Pivot tables are designed for on-going updates. If you base a pivot table on an Excel Table, the table resize as needed with new data. All you need to do is click Refresh, and your pivot table will show you the latest.

Filtering. Pivot tables contain several tools for filtering data. Need to look at North America and Asia, but exclude Europe? A pivot table makes it simple.

Charts. Once you have a pivot table, you can easily create a pivot chart.

More pivot table resources

 

Leave a Reply