5 Replies Latest reply on Aug 28, 2018 8:49 AM by Martin Bohlmann

# Conditional Formatting Cell Background Colour...!!!

Hi,

I have forecast values being displayed as different colours and I want to color those values based on certain thresholds on Single Column like it should be Green if its value greater than 100 and less than or Equal -100% and Red and  Greater than -100 So I created a calculated field. Please find the below Examples.

Kindly share some examples and suggestions.

If [Diff] >= 100 THEN "Green"

elseif [Diff] <= -100 THEN "Red"

elseif [Diff] > -100 THEN "Yellow"

END

• ###### 1. Re: Conditional Formatting Cell Background Colour...!!!

Is the attached what you are looking for?

• ###### 2. Re: Conditional Formatting Cell Background Colour...!!!

Hi Venkat

One thing... the coloring - your example formula, doesn't match the Excel example... no matter.

I have amended the colour formula to match Excel.

If SUM([Diff]) >= 100 THEN "Green"

elseif SUM([Diff]) <= -100 THEN "Yellow"

elseif SUM([Diff]) >= -100 and SUM([Diff])<0 THEN "Red"

else null

end

Screenshot

You may find these blogs useful, it describes the technique

Data Psientist: My Favorite Tableau Trick For Work: MIN(1)

and

Monday Morning Visualizations: Conditional Formatting w/ MIN(1)

Cheers

Mark

2 of 2 people found this helpful
• ###### 3. Re: Conditional Formatting Cell Background Colour...!!!

Hi Mark,

Let's I will for the same.

• ###### 4. Re: Conditional Formatting Cell Background Colour...!!!

Hi Venkat

Between John's and my example, do you have what you need?

John uses a slightly different method, but as you can see, the outputs are largely the same.

My technique is more difficult to implement, and in some cases isn't useful, but if you plan on developing in Tableau, it is something worth knowing, its the best way of getting maximum formatting control in Tableau.

You may need to adjust the colour formula on both solutions depending on need, you'll note that i wrap SUM() round the DIFF statements, that is deliberate.

@John, neat work by the way

Cheers

Mark

• ###### 5. Re: Conditional Formatting Cell Background Colour...!!!

Thanks Mark & John, this helped.

But my calculated field has values that don't currently exist in my data set.  They are capability levels such as 0,1,2,3,4 and these value are used to conditionally format the cell background.  Values 3 & 4 do not exist in the data set yet, and as a result don't appear in the Edit Colors panel for the calculation.  Is there a way to manually add these values or do I need to get the value into the data set in order to assign colors to the values?  When these values appear in the data source, then the cells would be formatted.

1 of 1 people found this helpful