Highlight fields in pivot table

[#1]

Hi guys,

I have a pivot table with some numerical values and I need some formula in order to highlight them either green or red. What I need to do is to calculate if the numbers in the rows under the first one (the first row stays in place and it's always green) are either +- 5% of the number in the first row that's above it. How do I do this?

Thanks,

Adrian

PS: I have the free version, so I'm not able to open any file.

[#2]

Hi Adrian,

In the properties of Pivot table, there is a tab called "Visual Cues".

You can apply colors to the expression here according to your requirement.

Hope this helps.

Thanks

Sabal

[#3]

Hi,

I'm aware of this, I don't know how to get the first number from every column.

[crusader_]

Hi,

I suppose you need to make calculated backgound of your cell in pivot table.

If it is, Chart-> Properties->Expressions-> "Expand + in expression"->Backgound color->Definition

If(condition, red(), green())

Hope this helps.

Andrei

[rubenmarin]

Hi Adrian,

In the expressions tab you can expand the expression and there you can set the colors for background and font.

You can use something like:

If (YourExpression>Above(YourExpression)*1.05, green(),

If (YourExpression<Above(YourExpression)*0.95, red()))

[sundarakumar]

in background color part try to add a expression like

if(sum(sales)>(above(sum(sales))+above(sum(sales))*.05), green(),

     sum(sales)<(above(sum(sales))+above(sum(sales))*.05),red()))

-Sundar

[#7]

Ok, this is a bit helpful, but what I'm trying to do is this:

Let's say this is a column: 100, 101, 106, 102, 98

100 is always green, 101 is green (because it's in the range 100 and 100+5%), 106 is red(not in the range), 102 is green(in the range), 98 is green (it's in 100 and 100-5%)

Is this a bit more clear? I might have not explained myself good in the first place.

Thanks

[sundarakumar]

If u always want to refer to the first row then u can replace above() with first().. This will always look for 100in the eg that u have specified..

-Sundar

[rubenmarin]

The maybe the expression should be like this:

If (YourExpression<Top(YourExpression)*1.05 and YourExpression>Top(YourExpression)*0.95, green(), red())

[munna1317]

use backgroung  color or text color in expresion