I have a spreadsheet with a data table from which I create a pivot table.



In the columns section of the pivot table, I use the Date field and I want to only show Months.

I am getting a very strange result in the columns (see attached).

Also, when I use the column drop-down in the pivot table, I can see the strange selections as well as all 12 months. The data table ONLY contains data for January 2022.

Tommy

Attached Images

Screenshot 2022-02-01 095416.jpg (103.2 KB, 25 views)

Please post a sample sheet. Pictures are useless to work with. Thanks

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

Sample attached.

If you click on the Column Label dropdown, you will see the spurious references that I can't figure out. I seem to have managed to correct the other issue.

Also, how do I hide any rows that have zero totals?

Tommy

Attached Files

reconciliation.xlsx (29.8 KB, 10 views)

I suspect that when you grouop ther dates by Months, you do get all the months in the dropdown.
Separately, if you only group my Months (as opposed to groupiong by Month and Years) then if your source data extends over more than one year, all the the data from multiple years will appear under a single month name.

Thanks p45cal

The data is only for a single month.

Tommy

I realise that, but I think you'll still get all the months appearing.

Oh OK - good top know.

Any idea why I am getting the other info in the drop-down.

Also, any idea how to get rid of blank rows?

Tommy

That's just the way it is.





What blank rows? Usually you can filter out (blanks).

Please see attached

Attached Images

Screenshot 2022-02-02 135135.jpg (126.1 KB, 20 views)

You can use Value filters to get:
2022-02-02_140510.png


How?:
2022-02-02_140625.png
then:
2022-02-02_140806.png
I notice you have some blanks in your picture which you can filter directly, but it probably won't necessary if the above has eliminated them already:
2022-02-02_141020.png

Thanks P45cal

The Pivot Table is going to show all the data from the selected range. The items that are showing without any values are listed in your data set without any values.
A mindset for the Database, or any data set to reduce waste, is that only records with data are recorded. Those rows are incomplete and, for some, not appropriate to be included in the dataset.
Also, there is a difference between 0 (zero) and NULL (no records.)