KNOWLEDGE BASE

Conditionally Formatting Cell Background Color


Published: 30 Jan 2013
Last Modified Date: 14 Aug 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 only
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 Records Card. 
To view the steps shown in the below video, please expand the above section.
Note: the video has no sound.
 
CLICK TO EXPAND SOLUTION
Example 2: Conditionally formatting the cell background color of Measures and/or Dimensions

Step 1: Create a "container" calculated field

  1. Select Analysis > Create Calculated Field.
  2. In the Calculated Field dialog box, do the following, and then click OK:
    • Name the calculated field. In this example, the calculated field is named Container.
    • In the Formula field, type 0
 

Step 2: Create the View

These directions will build the view one column at a time. After all of the columns are built, we will format all the columns at once. The original view had two dimensions on the Rows shelf, and two measures, so we will need to create 4 columns in this view.
  1.  Create a new worksheet
  2.  Drag [City] to the Rows shelf
  3.  Right-click [City] on the Rows shelf and uncheck Show Header
  4.  Drag two copies of [Container] onto the Columns shelf

    Note: Tableau Desktop will show one marks card for each copy of container called: Sum(Container) and Sum(Container) (2). This step is necessary because if only one copy of [Container] is used to create each column, then the headers will be on the bottom of the view.

  5. Click on the first copy of SUM(Container) on the Columns shelf. This will automatically open the marks card for this copy of SUM(Container)
  6. For the marks card for SUM(Container), drag [City] onto Label. Now the first column in the view contains city names and the second column is still blank.
  7. Right-click the second copy of SUM(Container) on the Columns shelf and select Dual Axis.
  8. Repeat steps 2-7 for any dimensions on the Rows shelf in the original worksheet.
  9. Repeat steps 4-7 for every measure on the Measures Values card in the original worksheet.
 
 

Step 3: Conditionally Color the View

  1. If there are 6 or fewer copies of [Container] on the Columns shelf, then click on the marks card labeled "All" to select it
  2. If there are more than 6 copies of [Container], then at the bottom of the marks card, click "More Fields" and select "All"
  3. Drag the calculated field used to determine the color of the rows to Color
  4. In the dropdown menu, select Square
  5. Click Size and adjust the slider all the way to the right
  6.  Create a calculated field with a name like "Blank" with a calculation similar to the following:
    ""
  7. Drag [Blank] to the Rows shelf
  8. Right-click [Blank] on the Rows shelf and uncheck Show Header
  9. (Optional) decrease the width of the columns if necessary so that the color completely fills the columns

 
 

Step 4: Rename the Headers

  1. For each bottom header, right-click the header "Container" and select Edit Axis
  2. In the Edit Axis dialog for the bottom axis, do the following and click OK:
    • On the General tab, delete the title "Container"
    • On the Tick Marks tab, select None for Major tick marks
  3.  For each top header, right-click the header and then select Edit Axis
  4.  In the Edit Axis dialog for the top axis, do the following and click OK:
    • On the General tab, change the title "Container" to the desired title
    • On the Tick Marks tab, select None for Major tick marks

Additional Information

The above steps can be reviewed in the attached workbook Cell Background Color.twbx.
Did this article resolve the issue?