13 Replies Latest reply: Oct 16, 2014 8:39 AM by Ruben Marin

# Highlight fields in pivot table

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,

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

• ###### Re: Highlight fields in pivot table

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

• ###### Re: Highlight fields in pivot table

Hi,

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

• ###### Re: Highlight fields in pivot table

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

• ###### Re: Highlight fields in pivot table

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()))

• ###### Re: Highlight fields in pivot table

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

• ###### Re: Highlight fields in pivot table

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

• ###### Re: Highlight fields in pivot table

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

• ###### Re: Highlight fields in pivot table

The maybe the expression should be like this:

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

• ###### Re: Highlight fields in pivot table

Hi,

If I'm using Top(expression) it doesn't take into account any number after the second one. Basically, if the first number in the column is 100, the second one is 95, let's say, 95 will be green, which is good, but the rest of number will be red, regardless of their value..

• ###### Re: Re: Highlight fields in pivot table

Hi, Check attachment, in simple example it works, maybe your table has more dimensions and need to use a TOTAL or something, can you post a sample?

Or you can use directly Top(expression) to check what value is used to compare, it will give you a hint of how to solve.

• ###### Re: Re: Highlight fields in pivot table

Unfortunately, I cannot open attachments for I have the free version

What formulas did you use?

• ###### Re: Re: Highlight fields in pivot table

The same formula, but it works in a simple example:

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

• ###### Re: Highlight fields in pivot table

use backgroung  color or text color in expresion