Forum Discussion
change drop down options in pivot table field
I use a Pivot Table for accounting. On the Expense Report page, the Quarter field has these options when you click on it: Qtr 1, Qtr 2, Qtr 3, and Qtr 4. That is how it should be. But on the Income Report page, the Quarter field options are 2, 3, Qtr 2 and Qtr 3. Clearly they were erroneously changed along the way. How to I fix this? The easiest fix would be to rename 2 and 3 to Qtr 1 and Qtr 4 but after a long time researching this I can't figure out how. File attached.
5 Replies
I think you may have cleared out the data in the same and it's affecting the filter. The expense report Qtr filter is empty.
I think I know what you're asking. The default behavior for the items found in a pivot table filter is to retain them.
For example:
I have a quarter column with entries for : Q1, Q2, Q3 and Q4
I delete out the Q2 data and I'm left with only: Q1, Q3 and Q4
The Qtr filter in the pivot will still show: Q1, Q2, Q3 and Q4.
This is the setting that does it:
Set it to None if you want the pivot filter to behave like a standard data filter.
Thank you so much for taking the time and sharing your expertise, Patrick2788 I understand your thinking but that didn't work. I'm not sure your approach would explain why there are not only Qtr 2 and Qtr 3 entries, but also 2 and 3. It looks like what happened is Qtr1 and Qtr 4 were renamed to 2 and 3, not deleted. Any ideas about how to change them back?
You may rename them back or better to calculate based on month name:
="Qtr " & ROUNDUP(MONTH(1&[@Month])/3,0)
