KNOWLEDGE BASE

Conditionally Formatting Cell Background Color


Published: 30 Jan 2013
Last Modified Date: 23 Jun 2017

Question

How to format cell background color based on a KPI (key progress indicator).

Environment

Tableau Desktop

Answer

CLICK TO EXPAND SOLUTION
Example 1: Conditionally formatting the cell background color of Measures
This article expands on the steps for creating key progress indicators, adding information for formatting the cell background color based on the indicator value.

Step 1: Create a KPI 

Create a KPI view using the steps provided in Visualize Key Progress Indicators.

You can also download the attached sample workbook to see the finished result.

Step 2: Set up conditional background color

  1. Drag the KPI field to Color on the Marks card.
  2. Change the mark type to Bar.
  3. (Optional) Click the arrow in the upper-right corner of the Color legend, select Edit Colors, and select the color palette you want.
  4. Drag Number of Records to Size on the Marks card.
  5. Click the arrow in the upper-right corner of SUM(Number of Records), select Measure (Sum) > Minimum
  6. Adjust mark size as desired. See Size Properties for more information.
  7. (Optional) Hide the Number of Record Card. 
To view the steps showed in the below video, please expand the above section.
Note: the video has no sound.
 
CLICK TO EXPAND SOLUTION
Example 2: Formatting the cell background color and/or the text of Dimensions

While the feature does not currently exist in Tableau Desktop, the following two examples are workarounds to color the text and the background of dimensions.

Coloring dimensions text

  1. Select Analysis > Create calculated field
  2. Enter the following formula and click OK
    0
  3. Add the calculated field to the columns shelf twice for the dimension that you wish to color
  4. Change All Marks card to Text
  5. Right-click on the second calculated field on Columns and select Dual Axis
  6. Select the second axis in the Marks card and add the dimension on both the Label and Color marks
  7. Right-click the top axis in the view and change the title as desired, in the Tick Marks, select None for Major tick marks
  8. Right-click the bottom axis in the view and change the Range to Fixed
    1. From there adjust the range so that the ABC text disappears. In this example: from 1 to 2.
    2. In the Tick Marks, select None for Major tick marks
  9. Optionlal: Edit the title of the axis or hide them by clicking the Show header function

Coloring dimensions cell background

  1. Follow steps 1 to 10 as above
  2. Select Analysis > Create calculated field
  3. Enter the following formula and click OK: 
    1
  4. Drag this new calculated field and drop it on the dual axis field that contains the dimension text
  5. Click the mark of this new calculated field and change the chart type to Bars.
    • Optional: adjust the size of the bar.
  6. Right click the new calculated field in Columnns and select Measure (SUM) > Minimum
  7. Right-click the top axis in the view and change the title as desired, in the Tick Marks, select None for Major tick marks
  8. Right-click the bottom axis in the view and change the Range to Fixed
    1. From there adjust the range so that the ABC text disappears. In this example: from 0 to 1
    2. In the Tick Marks, select None for Major tick marks
  9. Optionlal: Hide the headers as desired by clicking the Show header option
To view the steps showed in the below video, please expand the above section.
Note: the video has no sound.
 

Additional Information

The above workaounds can be reviewed in the attached workbook Cell Background Color.twbx, you will need Tableau Desktop 10.3 to open them. 
Did this article resolve the issue?