Conditional Format Values based on the cell above it

[Yandeez]

Hi - I need to format the attached data set to show a 3-color arrow icon set conditional format. If the cell directly below the current cell is lower than the current cell, I need it to show a red downward arrow. If the cell below the current cell is greater than the current cell, then I need to show a green upward arrow. The idea is to allow anyone to see quickly that sales increased or decreased month to month. Any help is greatly appreciated.

 

[Somendra Misra]

Hi Yandeez,

See the attached file, it uses Column B with CF arrows.

Regards,

 

[Luke M]

Had to use a helper column, but how's this?

 

[Yandeez]

Thanks for the prompt response. Let's say those values were part of a Pivottable? I like the helper column but how do I do the same exact thing but in a PT? I know I have to add the 2nd sales field in the values area.

 

[Luke M]

Add your value field twice, change it's setting to show Difference from: Next, then add CF. See attached.

 

[Yandeez]

When I do that it prompts to me to turn off autosort. When I click yes, my PT unsorts which I don't particularly want because then it's hard for anyone to easily see the best performing customers.

 

[Luke M]

To quote the man in black, "...then we are at an impasse"
Either we use a helper column outside the PivotTable and you sort it,
OR
Put icon column in PT, but don't have it sorted.