As shown in below picture, "25/11/2022" has a different alignment with other dates in the same column of the Pivot Table.
Pivot Table Error.PNG
I've accidentally renamed "25/11/2022" in my Pivot Table into a random text value. It took me a while until I realized the mistake but by the time it was already too late since I've made some changes in other sheets and have already saved the file.
I've manually changed it back into "25/11/2022" but it continues to come on top of "01/11/2022" and has a different alignment with other dates. I've tried everything I could, including the following methods I found online but none of them helps:
-Excel Options > Data> Disable automatic grouping of Date/Time columns in PivotTables
-PivotTable Options > Data > Retain items deleted from data source > None
-Removing the "Date" column once and adding it back again
It seems like Excel has identified "25/11/2022" as a special date group and remembers the change forever. I've searched online about how to clear cache in Pivot Tables but none of the websites provide the solution I need. It seems like the only method is to delete the entire Pivot Table and re-create it from scratch.
If anyone here knows how to clear cache of a Pivot Table and ensure that it displays data exactly as the data source without having to re-make the Pivot Table, I would be grateful if you can share with me your knowledge. Thank you.
Bookmarks