Why Does the Pivot Table Field List Keep Disappearing?
November 23, 2022 - by Bill Jelen
Problem: The pivot table tools are there, then they are gone. What is Microsoft’s problem?
Strategy: Here is Microsoft’s rationale. They found an Excel 2003 customer who had been living with the Picture Toolbar for months. There was no picture in the worksheet, and the toolbar was actually getting in the way. Because of this event, Excel now has an obsessive desire to put away the contextual ribbon tabs as soon as you are not using them.
If you build a pivot table and keep the cell pointer within the pivot table, Excel will display the two new ribbon tabs and the PivotTable Field List dialog. But as soon as you click outside the pivot table, Microsoft will put away the ribbon tabs and hide the PivotTable Field List dialog. This drives me crazy. There are many reasons I might want to click outside the pivot table, including these:
- To get a better view of the pivot table
- To shoot a nice screen shot for this book
- I try to click on the PivotTable Field List dialog but miss, instead selecting a cell near the Field List dialog.
- I accidentally press the left mouse button when the mouse pointer had the audacity to not be above the pivot table.
- I type the Right Arrow key to scroll right in a wide pivot table, and I accidentally go one cell too far.
To my friends at Microsoft: There is nothing on Sheet2 except the pivot table. As long as I am looking at Sheet2, I am looking at the pivot table. Quit hiding the ribbon tabs just because I clicked out of the pivot table! The lady who lived with the picture toolbar for six months because she didn’t know how to click the X to close the toolbar should not cause the other 749.999 million people using Excel to suffer.
To keep everyone happy, how about these rules: If your code renders a picture in the visible window of Excel, show the Picture Tools tab of the ribbon. Even if the picture is not selected, it will at least give me a clue that there are things I can do to the picture. If the ribbon is allegedly to help people discover new features in Excel, then quit hiding important tabs.
Additional Details: The new ribbon interface causes enough stress without it randomly switching to other tabs. If you are working on the PivotTable Tools Design tab and you accidentally arrow out of the pivot table, you will find yourself on the Home tab. Even if you immediately arrow back into the pivot table, you are still on the Home tab.
Maddeningly, Microsoft handled this one bizarre situation but none of the other common situations. Try this:
1. Select a cell in the pivot table.
2. Choose the Design tab of the ribbon.
3. Use the mouse to select exactly one cell outside the pivot table. Excel will hide the pivot table ribbon tabs and the PivotTable Field List dialog.
4. Using the mouse, select a cell back in the pivot table. Excel will redisplay the Design tab.
If you prefer to use the keyboard, you can instead try this:
1. Select a cell in the pivot table.
2. Choose the Design tab of the ribbon.
3. Press the Right Arrow key until you have moved exactly one cell outside the pivot table. Excel will hide the pivot table ribbon tabs and the PivotTable Field List dialog.
4. Using the Left Arrow key, move back into the pivot table. Excel will redisplay the two ribbon tabs, but it will leave you on the Home tab of the ribbon.
However, this similar scenario does not work:
1. Select a cell in the pivot table.
2. Display the Design tab of the ribbon.
3. Use the mouse to select one cell outside the pivot table. Select another cell outside the pivot table. Select a cell inside the pivot table. Excel will not return you to the Design tab.
So, Microsoft went through the incredibly convoluted task of catching when you select exactly one cell outside the pivot table with the mouse and immediately go back to the pivot table using the mouse. The whole situation frustrates me to no end.
This article is an excerpt from Power Excel With MrExcel
Title photo by Khadeeja Yasser on Unsplash
