change drop down options in pivot table field

Copper Contributor

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

@KrisA22 

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:

clipboard_image_0.png

 

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?

@KrisA22 

You may rename them back or better to calculate based on month name:

="Qtr " & ROUNDUP(MONTH(1&[@Month])/3,0)

@Sergei Baklan 

Thank you so much - love your idea. But - how do I rename it? It doesn't matter what I do - double click, left click, pulling up the pivot field settings - can't find this option anywhere. Please advise!!

@KrisA22 

I meant to rename in source table, not in PivotTable