Creating conditionally formatted tables is a regular request for Tableau consultants.
This is very similar to what users are used to in Excel. For example, they may want some columns to be highlighted and others to be left without highlighting.
For simple highlighting requests – for example only highlighting Sales by the amount of sales, highlighting Profit by the amount of profit, etc – then it is quite straightforward.
Colouring a measure by itself is the simplest way to do conditional formatting in Tableau. It’s not more complex than placing the measure on the Colour shelf.
More complex business rules are likely to require a different technique, probably using the Placeholder technique.
For example, to colour Sales by the YoY Change use the Placeholder technique; that isn’t possible using the simple technique detailed below.
This article covers a method of conditional formatting in Tableau just using built-in Tableau options. Better to keep complexity to a minimum; and use standard Tableau options.
Tableau enables a lot of formatting “out of the box”. For example, simply setting the format can:
How to do Simple Conditional Formatting in Tableau
Building a Tableau table using the standard Measure Names / Measure Values enables each measure to have a different colour scheme.
Using sample superstore data, this simple example shows how to colour Profit by profit or loss, without colouring Sales. We use the State field as the dimension to slice the data.
In your Tableau worksheet create your table using the Measure Names / Measure Values pills. (Small tip – place a single measure in the Text / Label mark then double click another measure in the Measures list; Tableau will automatically convert your view to a Measure Names / Measure Values table!)
Set the Mark type to Square and ensure Measure Values is on both Label and Colour. Both measures show the Tableau default colours.
Use Separate Legends for Conditional Formatting
To reiterate we want highlighting of the Profit but no highlighting of Sales.
Click the small drop down arrow of the Measure Values pill on the Colour shelf and choose “Use Separate Legends”.
The legends for both Sales and Profit should automatically appear, from where you’re able to edit the colours for each. Click the small arrow on the top right of each legend and Edit Colours.
Set the Profit colours as a 2 Step with zero at the centre, this highlights whether there was profit or loss. This example uses the Tableau automatic red / blue colour scheme.
Sales should not have any highlighting. A solution is to colour everything white, however this is more complex than one might expect. (Unless I’m missing something, please add in the comments if there’s a better way.) As Sales is a continuous measure we are forced to colour by a range. Therefore white should be the colour at both the top and bottom ends of the range.
Use a Custom Diverging colour scheme and set both ends of the range to have the white custom colour (HTML = #ffffff), allowing only 2 steps. Setting to 2 “Stepped Colour” forces using only the top and bottom of the colour range.
The final conditionally formatted Tableau table
That provides, in relatively quick time, a basic level of conditional formatting for a Tableau table.
Thank that was wonderful. I have situation where on the dimension I have category and measures as sales and profits by years for three years. Now I need to format the current year sales/profit based on %change from previous year. Is that do able?
To format a field using another field, use the placeholder technique.
Hi,
I am in the same situation, whereas a I have a text table and using Measure Names on the columns shelf.
Your solution works fine, but I was wondering how I implement a more complex logic.
I got the request that at certain thresholds of values the colour should change. Unfortunately, with your approach you can only set the start, end, and center (which is fine for most situations I guess!). If I add more steps to the colour stepping, then Tableau does apply thresholds automatically.
I found other instructions on the web how to use calculated fields with if statements. However, those examples never use Measure Names in the columns shelf and I it always highlights the whole table because of that and not only the particular column, despite the if statement only refers to values from the column that is supposed to be highlighted.
Use the placeholder technique for more complicated conditional formatting in a Tableau table
Great one Thank you. Couple of items need help … 1) How did you change the color of the font to white for Profit column ? Mine is still showing Black color. 2) How to highlight the color of the column on text values. For ex: I have a column with 4 possible values and required color is i) Yes (green color) ii) No (Red Color) iii) Tentative(Orange color) iv) NA (Blue Color) . Thanks in Advance.
1. to colour the column white, it’s a custom colour scheme, starting and ending with #ffffff.
2. Also custom, but for this you will need to create your own colour palette with the 4 colours in the right order. Also, your values need to be numeric, as you have numbered them in the list.
How do you put Measure Values on both Label and Colour? It will only let me put it on one or the other
You should be able to. Trying holding Ctrl when dragging the field in for the 2nd time.