Hide and Seek in #Excel – PivotTable Fields

Have you ever encountered a situation like this? How are we supposed to work with a PivotTable without seeing the data fields?

I was so lost when I saw this from a workbook on my friend’s notebook. He came to me for PivotTable help but I was not able to even work with the PivotTable he opened and presented to me… How embarrassing… 😑😅

I am a big fan of PivotTable. I use PivotTable a lot for data analysis. And I am so getting used to the default layout of the PivotTable Fields pane, like this:

This is almost a must-have pane for people to work with PivotTable. We can select, drag and drop the data fields into corresponding areas of a PivotTable. Honestly I have taken this layout for granted whenever I deal with PivotTable.

Although I use PivotTable a lot, there are a few buttons I rarely touch. This is one of them:

And this is exactly where we can hide/display the data fields.

As you see, there are five different layouts:

Very straightforward. Now you know, it is just two clicks away to our “favorite” layout for PivotTable Fields pane. Yeah! 😁

It made me think… who would use layout 4 and 5 where we do not have the data fields to drag and drop?? Layout 3 as well where we can see the fields but can’t pivot them between rows and columns? What’s the point? Probably when we do not want (or try to prevent) user to change the pivot tables created.

Do you have a practical use case for layout 3 to 5? If you do, please share with us by leaving comments below.


About MF

An Excel nerd who just transition into a role related to data analytics at current company......😊 Recently in love with Power Query and Power BI.😍 Keep learning new Excel and Power BI stuffs and be amazed by all the new discoveries.
This entry was posted in Excel Tips and tagged , . Bookmark the permalink.

Comments, suggestions, corrections are welcome.

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.