Microsoft Excel’s PivotTable tool is vital for drawing analyses from big datasets in just a few clicks. However, understanding the often confusing PivotTable Fields pane is essential to making the most of this powerful feature.
What are PivotTables in Microsoft Excel?
Big datasets can be difficult to read, interpret, and analyze. Indeed, it can be difficult to track specific information and understand relationships between data points if you have to scroll through thousands of rows and columns, even if you use the Freeze Panes or Focus Cell tools.
However, converting a dataset into a PivotTable allows for simplified data exploration and analysis, instant insights, and quick comparisons. What’s more, PivotTables let you perform calculations without the need for complex formulas, and you can use slicers and timelines to only show the data you need at any given point.
To follow along as you read this guide, download a free copy of the workbook used in the examples. After you click the link, you’ll find the download button in the top-right corner of your screen.
You can create a PivotTable in Excel from an external data source, a table in your worksheet, or a pre-built data model. To access these options, in the Insert tab on the ribbon, click the “PivotTable” down arrow.
In my case, I want to analyze an Excel table (named T_Sales) I created in an Excel worksheet, and I want to do so in a new worksheet to keep my workbook tidy and organized. So, after selecting a cell in the table, I’ll click “From Table Or Range” in the PivotTable drop-down menu. Then, after making sure the correct range is selected, I’ll check “New Worksheet,” and click “OK.”
I strongly recommend that you format your data as an Excel table before converting it into a PivotTable. Taking this step ensures that the source data is formatted in a way that the PivotTable tool can understand, and the PivotTable can pick up new rows and columns subsequently added to the table.
Opening the PivotTable Fields pane
The PivotTable Fields pane is where you decide what goes in the PivotTable. This pane usually opens by default on the right of your screen as soon as you initiate the PivotTable-building process.
The PivotTable Fields pane closes as soon as you click away from the PivotTable area.
If the PivotTable Fields pane doesn’t appear by default when you select a cell in the PivotTable area, or if it disappears at any point when you’re working on your PivotTable, click “Field List” in the PivotTable Analyze tab on the ribbon. You can also click the same button if you don’t want the PivotTable Fields pane to be displayed by default.
To move the PivotTable fields pane, hover your cursor over the top of the pane until it turns into a four-arrow move pointer, and click and drag the pane to a more convenient position on your screen as a floating window.
To turn it back into the default pane, click and drag it to the right of your screen until it clicks back into position.
Understanding the PivotTable Fields areas
When I first started using PivotTables in Microsoft Excel many years ago, I found the PivotTable Fields pane confusing, not only because the default layout is overly condensed, but also because what goes in the different fields isn’t immediately obvious.
By default, the fields (labeled A in the screenshot below) are stacked on top of the areas (labeled B in the screenshot below). If your source dataset is substantial, this layout makes everything appear overcrowded, especially if you’re working on a small screen.
To fix this, click the “Tools” cog, and select “Fields Section And Areas Section Side-By-Side.”
This vertical layout makes the pane appear less cluttered and, thus, easier to use.
Before you go ahead and start building your PivotTable, take a few moments to understand what each area means:
- Rows: Fields inserted into the Rows area appear as row labels down the left-hand side of the PivotTable. In the screenshot below, the Product field being in the Rows area means the products in the source data are listed in column A.
- Columns: Fields added to the Columns area appear as column labels across the top of the PivotTable. In the example below, you can see countries in the column headers.
- Values: Fields placed into the Values area are usually variables with numeric values that form the main part of the PivotTable, with each value acting as an intersection between a column and a row. In the PivotTable below, each cell where a product and country intersect contains a profit value.
- Filters: Insert a field into the Filter area if you want to filter the entire PivotTable based on a given variable. In this case, I can filter the PivotTable by department.
Adding and removing fields to and from a PivotTable
There are two ways to add fields to an area in the PivotTable Field pane.
When you check a checkbox next to a field, Excel automatically moves it to one of the areas according to the type of data the field contains. Usually, non-numeric fields are added to the Rows area, date and time fields are added to the Columns area, and numeric fields are added to the Values area. Useful as this automation may be, fields sometimes go in the wrong areas. What’s more, when you use this method, there’s no way for Excel to know which field to add to the Filters area.
Alternatively, dragging and dropping a field into an area gives you more control over what goes where. If you want to change which area a field is added to, simply drag and drop it to the appropriate place. For example, to remove the Department filter, click and drag “Department” from the Filters area to the left of the PivotTable Fields pane.
As you add a field to an area in the PivotTable Fields pane, you’ll see the PivotTable being constructed in real time. If you find this confuses you more than it helps you, check “Defer Layout Update” at the bottom of the pane, and click “Update” once you’ve moved all the fields to the desired areas.
To remove a field from a PivotTable area, either uncheck the corresponding checkbox in the fields section or drag and drop a field from the areas section to anywhere outside the PivotTable Fields pane.
Now that you have used the PivotTable Fields pane to build your PivotTable, use the PivotTable Analyze tab on the ribbon to manipulate and customize the data. For example, you can rename the PivotTable, group different fields, or insert slicers and timelines for dynamic filtering. On the other hand, you can change the PivotTable’s visual properties via the Design tab, such as adding subtotals, formatting the data into banded rows, or changing the colors of the headers.
OS
Windows, macOS, iPhone, iPad, Android
Free trial
1 month
Microsoft 365 includes access to Office apps like Word, Excel, and PowerPoint on up to five devices, 1 TB of OneDrive storage, and more.
