A Pivot Table Quirk in Excel - Photo by Nick Fewings on UnsplashI was helping a student create pivot tables the other day and we came across a quirky thing. At first glance the data that we were using in three separate spreadsheets in the same file looked identical. All three had the same sort of data with the same titles at the top. Before entering the data, he created the three sheets simultaneously. But when we created pivot tables for each sheet, one gave a different result.

Here’s the story.

This is what the columns in each spreadsheet had.

  • A has dates.
  • B has what you bought.
  • C has the amount you paid.

The three sheets are three different people in the same department of a company.

After having created the three sheets, he created a pivot table for the first.

This is what he was presented within the sidebar.

Pivot Table Fields

In the spreadsheet titles at the top of each column there are only: Date, What and Amount. So, where did Quarters and Years come from? Excels’ intelligence has put that in for you after scanning the data to find more than one year and therefore can show years and quarters.

The strange thing with my student was that when he created a pivot table for the third sheet it had the result of this. Pivot Table fields 2

Why are there only the three titles showing this time?

There is a simple explanation. He had explored this sheet and to help him, he used the Freeze panes tool from the View tab. view tab

Using this in the data spreadsheet made a huge difference to the result. Taking this effect off and creating a new Pivot Table gave him the result he needed with the extra Year and Quarter options.

Therefore, learning from this experience, he now knows that using some features in Excel may well impact the use of others. Finding what you believe to be a wrong answer may well be just that you have used a feature that inhibits the results for you.

Linking spreadsheets in Excel 19

How to apply freeze panes in Excel

Using Grouping in Excel

 

LEAVE A REPLY

Please enter your comment!
Please enter your name here