Bottom Line: Learn three tips to save time when working with the pivot table field list.
Skill Level: Intermediate
Watch the Tutorial
Download the Excel File
You can download the example file I use in the post to follow along.
The Pivot Table Task Pane
When working with pivot tables, there's is a task pane that is used to add or delete fields to different areas of the table. This is the Pivot Table Fields list and I'd like to share with you three tips to help you use it more efficiently.
Tip #1: Change the Layout of the Field List
The first tip involves modifying how the pane is laid out. The default layout of this pane is as shown above, with the fields listed on top and the areas underneath. But the layout can be changed to a few other configurations. To change the layout, simply click on the settings dropdown menu. It's the button that has the gear icon on it.
Of the alternate layouts that are offered, I think I prefer the side-by-side layout most. That's because it allows for more fields to be shown in list form without scrolling, which is really helpful when there are many fields to choose from.
This layout is also helpful when you are using Power Pivot. Other layout options that are less popular, but still useful, are fields only, areas only, and and stacked areas only.
Other Features in the Settings Menu
In addition to changing the layout of the pane, the settings menu allows you to do a few more things as well:
- If you are using Power Pivot, you can collapse and expand the table fields.
- You can sort the fields in alphabetical order.
- You can sort in data source order. (This would be the columns in your data set, from left to right).
Tip #2: Undock and Move the Fields List
By default, the fields list is docked to the right of your worksheet. But you can move it. Hover your cursor near the title of the pane until it turns into a cross-arrows. Then left-click and drag the pane to your desired location.
Once the pane has been undocked, you are able to resize it as you like. You can also dock the pane to the left side of the worksheet as well. If the pane is floating, you can double-click the top to instantly redock it to whichever side it was last docked at.
One other feature to note is that you can move the pane off of the application completely. So if you have multiple monitors, for example, you could have the worksheet on one monitor and the fields list on another monitor.
Tip #3: Reopening the Fields List When Closed
Sometimes you might inadvertently make the Pivot Table Fields list disappear. Or you may close it purposely but not be aware of how to get it back. If you've closed the window, clicking anywhere in the pivot table will NOT bring it back. Instead you can reopen it in one of two ways.
- Right-click on the pivot table and then select Show Field List.
2. Click the Field List button on the PivotTable Analyze or Options tab.
Bonus Tip: Managing Multiple Task Panes
One other tip I want to mention concerns having multiple task panes open simultaneously. In the image below, you can see that I have the Format Chart Area task pane open, and it has eclipsed my Pivot Table Fields pane. To navigate between the two, click on the icons that are aligned vertically down the right side of the pane. The top icon indicates the Pivot Chart Fields pane and the bottom one is the Format Chart Area pane. If I had more panes open, they would appear below these as vertical tabs.
Improve Your Knowledge of Pivot Tables
If you'd like to brush up on pivot tables or take your knowledge to the next level, I have a free webinar going on that we'd love to have you to join. It covers all the power tools, including Power Query, Power Pivot, Power BI, as well as macros, VBA, and, of course, pivot tables. You can access it here: The Modern Excel Blueprint
I hope the tips above help you use the Pivot Table Fields list with more ease. If you have comments or questions, please leave them below.
wow it is really helpful
Thank you, Jon, for your willingness to share these tips. Even the most mundane are helpful.
There are too many versions of Excel. There are too many variables between versions. Unfortunately for ‘me’… I’m trying to run and learn Excel on a Mac, and it is diabolical…. it now seems that Microsoft are ‘funnelling’ everyone into the ongoing subscription model of the online 365 version. Every time I open my 2016 version, I’m bugged by an *update* notice… that keeps taking me to the subscription model…. before I figured out, the update… was in fact, an up-grade $$$ub$cription….
I’d like for Excel for Mac please
Excel does not remember the new Field Layout preference, so every time you want it displayed in an alternate way; you have to change it…is this accurate? I can’t see any “Save as Default” option.
Can anyone help with a Field List that won’t come back? I was playing with it based on this tutorial (learned some great tricks) but now I cannot retrieve viewing the Field List. I have gone through the suggestions multiple times (click inside the PT, toggle Analyze > Field List, search all around all three monitors). I’ve also asked other Excel power users in my company look at it (no one can figure it out), and IT re-installed Excel on my computer, but I still cannot access the Field List.
Please help! I need my Pivot Tables!
Created two columns excel list where 13 raws, There are no blank cells in raws or columns.
Why Field table is blank in my spreadsheet when I generated Pivot Table?
My pivot gives this message when I tried to add another field to my pivot: too many records to complete this operation -my data table has 35,000 rows and will grow to over 60K rows by year-end. Any suggestions on how to correct?