Creating a basic pivot table

By Bill Jelen
2/17/2025

Contents

How to create a basic pivot table

Now that you have a good understanding of the importance of a well-structured data source, let’s walk through creating a basic pivot table.

To ensure that the pivot table captures the range of your data source by default, click any single cell in your data source. Next, select the Insert tab and find the Tables group. In the Tables group, select PivotTable and then choose From Table/Range from the dropdown. Figure 2-12 demonstrates how to start a pivot table.

FIGURE 2.12 Start a pivot table by selecting PivotTable from the Insert tab.

Choosing these options activates the Create PivotTable dialog, shown in Figure 2-13.

FIGURE 2.13 The Create PivotTable dialog.

As you can see in Figure 2-13, the Create PivotTable dialog asks you only two fundamental questions:

Where’s the data that you want to analyze?
Where do you want to put the pivot table?

Here’s how you handle these two sections of the dialog:

Choose The Data That You Want To Analyze—In this section, you tell Excel where your data set is. You can specify a data set that is located within your workbook, or you can tell Excel to look for an external data set. As you can see in Figure 2-13, Excel is smart enough to read your data set and fill in the range for you. However, you should always take note of the range Excel selects to ensure that you are capturing all your data.
Choose Where You Want The PivotTable Report To Be Placed—In this section, you tell Excel where you want your pivot table to be placed. This is set to New Worksheet by default, meaning that your pivot table will be placed in a new worksheet within the current workbook. You will rarely change this setting because there are relatively few times you’ll need your pivot table to be placed in a specific location.

NOTE

Note the presence of another option in the Create PivotTable dialog shown in Figure 2-13: the Add This Data To The Data Model option. You would select this option if you were trying to consolidate multiple data sources into a single pivot table.

The Add This Data To The Data Model option is covered in detail in Chapter 7, “Analyzing disparate data sources with pivot tables,” and in Chapter 10, “Unlocking features with the Data Model and Power Pivot.”

In this chapter, we’ll keep it basic by covering the steps to create a pivot table by using a single source, which means you can ignore this particular option.

After you have answered the two questions in the Create PivotTable dialog, simply click the OK button. At this point, Excel adds a new worksheet that contains an empty pivot table report. Next to that is the PivotTable Fields list, shown in Figure 2-14. This pane helps you build your pivot table.

FIGURE 2.14 You use the PivotTable Fields list to build a pivot table.

CAUTION

The icons for Columns and Rows in Figure 2-14 are reversed. A summer intern at Microsoft inadvertently reversed the icons several versions ago, and no one noticed. After Bill pointed this out to the correct project manager, Microsoft pledged to restore the icons to the correct location. The dark-gray portion of the icon is supposed to show where the data will be displayed when you drop a field here. Any data dropped into the Columns area will display across the top of the report (as illustrated in the icon currently in the Rows area). It feels sort of goofy to show the wrong icons in this book on pivot tables, but that’s the way it appears to us at press time.

TIP

Finding the PivotTable Fields list

The PivotTable Fields list is your main work area in Excel. This is the place where you add fields and make changes to a pivot table report. By default, this pane pops up when you place your cursor anywhere inside a pivot table. However, if you explicitly close this pane, you override the default and essentially tell the pane not to activate when you are in the pivot table.

If clicking on the pivot table does not activate the PivotTable Fields list, you can manually activate it by right-clicking anywhere inside the pivot table and selecting Show Field list. You can also click anywhere inside the pivot table and then choose the large Fields List icon on the Analyze tab under PivotTable Tools in the ribbon.

Adding fields to a report

You can add the fields you need to a pivot table by using the four “areas” found in the PivotTable Fields list: Filters, Columns, Rows, and Values. These areas, which correspond to the four areas of the pivot table, are used to populate your pivot table with data:

Filters—Adding a field to the Filters area enables you to filter on its unique data items. In previous versions of Excel, this area was known as the Report Filters area.
Columns—Adding a field into the Columns area displays the unique values from that field across the top of the pivot table.
Rows—Adding a field into the Rows area displays the unique values from that field down the left side of the pivot table.
Values—Adding a field into the Values area includes that field in the Values area of your pivot table, allowing you to perform a specified calculation using the values in the field.

Fundamentals of laying out a pivot table report

Now, let’s pause a moment and go over some fundamentals of laying out a pivot table report. This is generally the point where most new Excel customers get stuck. How do you know which field goes where?

Before you start dropping fields into the various areas, answer two questions:

“What am I measuring?”
“How do I want to see it?”

The answer to the first question tells you which fields in your data source you need to work with, and the answer to the second question tells you where to place the fields.

Let’s say you wanted to measure the dollar sales by region. This would automatically tell you that you need to work with the Sale Amount and Region fields. How do you want to see it? You want regions to go down the left side of the report and the sales amount to be calculated next to each region.

To achieve this effect, you need to add the Region field to the Rows area and add the Sale Amount field to the Values area.

Find the Region field in the PivotTable Fields list and select the checkbox next to it. As you can see in Figure 2-15, not only is the field automatically added to the Rows area, but also your pivot table is updated to show the unique region names.

FIGURE 2.15 Select the checkbox next to the Region field to automatically add that field to your pivot table.

Now that you have regions in your pivot table, it’s time to add in the dollar sales. To do that, simply find the Sale Amount field and select the checkbox next to it. As Figure 2-16 shows, the Sale Amount field is automatically added to the Values area, and your pivot table report now shows the total dollar sales for each region.

FIGURE 2.16 Select the checkbox next to the Sale Amount field to add data to your pivot table report.

At this point, you have already created your first pivot table report!

TIP

How does Excel know where your fields go?

As you’ve just experienced, the PivotTable Fields list interface enables you to add fields to your pivot table by simply selecting the checkbox next to each field name. Excel automatically adds the selected fields to the pivot table. But how does Excel know which area to use for a field you select? The answer is that Excel doesn’t really know which area to use, but it makes a decision based on data type. Here’s how it works: When you select a checkbox next to a field, Excel evaluates the data type for that field. If the data type is numeric, Excel places the field in the Values area; otherwise, Excel places the field in the Rows area. This placement obviously underlines the importance of correctly assigning the data types for your fields.

NOTE

Blank cells in your numeric columns previously caused the column to be treated as text. A change in early 2018 for Office 365 subscribers changed this behavior. Now, a column with a mix of numbers and empty cells will be treated as values, and the field will go to the Values area with a default calculation of Sum.

If you have someone who clears out cells by mashing down the spacebar several times instead of using the Delete key, Excel will treat those spaces as text. A mix of numbers and text in a column will cause Excel to default to counting the column instead of summing.

Adding layers to a pivot table

Now, you can add another layer of analysis to your report. Say that now you want to measure the amount of dollar sales each region earned by product category. Because your pivot table already contains the Region and Sales Amount fields, all you have to do is select the checkbox next to the Product Category field. As you can see in Figure 2-17, your pivot table automatically added a layer for the Product Category and refreshed the calculations to include subtotals for each region. Because the data is stored efficiently in the pivot cache, this change took less than a second.

FIGURE 2.17 Without pivot tables, adding layers to analyses requires hours of work and complex formulas.

Rearranging a pivot table

Suppose that the view you’ve created doesn’t work for your manager. He wants to see Product Categories across the top of the pivot table report. To make this change, simply drag the Product Category field from the Rows area to the Columns area, as illustrated in Figure 2-18.

FIGURE 2.18 Rearranging a pivot table is as simple as dragging fields from one area to another.

The report is instantly restructured, as shown in Figure 2-19.

FIGURE 2.19 Your product categories are now column-oriented.

TIP

Longing for drag-and-drop functionality?

In Excel 2003 and previous versions, you could drag and drop fields directly onto the pivot table layout. This functionality is allowed only within the PivotTable Fields list (dragging into areas). However, Microsoft has provided the option of working with a classic pivot table layout, which enables drag-and-drop functionality.

To activate the classic pivot table layout, right-click anywhere inside the pivot table and select PivotTable Options. In the PivotTable Options dialog, select the Display tab and select the checkbox next to Classic PivotTable Layout, as demonstrated in Figure 2-20. Click the OK button to apply the change.

FIGURE 2.20 Select the checkbox next to Classic PivotTable Layout.

At this point, you can drag and drop fields directly onto your pivot table layout. If you always want your pivot tables to be in Classic layout, follow these steps:

Select File | Options. A new Data category is added along the left panel starting in 2017 for Microsoft 365 subscribers.
In the Data category, the first setting is Make Changes To The Default Layout Of PivotTables. Click Edit Default Layout.
The Edit Default Layout dialog offers a few common choices. Apparently, Classic layout did not make it to the top few choices. Click the PivotTable Options button in the Edit Default Layout dialog. You will see a dialog similar to Figure 2-20. Choose the Display tab and the Classic PivotTable Layout. Click OK three times to close each of the open dialogs. All future pivot tables can be built in Classic mode.

Creating a report filter

You might be asked to produce different reports for particular regions, markets, or products. Instead of building separate pivot table reports for every possible analysis scenario, you can use the Filter field to create a report filter. For example, you can create a region-filtered report by simply dragging the Region field to the Filters area and the Product Category field to the Rows area. This way, you can analyze one particular region at a time. Figure 2-21 shows the totals for just the North region.