Conditional formatting in tables

With conditional formatting for tables, you can specify customized cell colors based on cell values, or based on other values or fields, including using gradient colors. You can also display cell values with data bars.

To access conditional formatting, in the Fields well of the Visualizations pane in Power BI Desktop, select the down-arrow beside the value in the Values well that you want to format (or right-click the field). You can only manage conditional formatting for fields in the Values area of the Fields well.

Conditional formatting menu

The following sections describe each of these three conditional formatting options. One or more options can be combined in a single table column.

Note

When applied to a table, conditional formatting overrides any custom table styles applied to the conditionally formatted cells.

To remove conditional formatting from a visualization, just right-click the field again, select Remove conditional formatting and then the type of formatting to remove.

Remove conditional formatting menu

Background color scales

Selecting Conditional formatting and then Background color scales brings up the following dialog.

Background color scales dialog

You can select a field from your data model to base the colors on, by setting Color based on to that field. In addition, you can specify the aggregation type for the selected field with the Summarization value. The field to be colored is specified in the Apply color to field, so you can keep track. You can apply conditional formatting to text and date fields, as long as you choose a numeric value as the basis of the formatting.

Color based on field

To use discrete color values for given value ranges, select Color by rules. To use a color spectrum, leave Color by rules unchecked.

Background color scales dialog

Color by rules

When you select Color by rules, you can enter one or more value ranges, each with a set color. Each value range starts with an If value condition, an and value condition, and a color.

Color by rules value range

Table cells with values in each range are filled with the given color. There are three rules in the following figure.

Color by rules example

The example table now looks like this:

Example table with color by rules

Color minimum to maximum

You can configure the Minimum and Maximum values and their colors. If you select the Diverging box, you can configure an optional Center value as well.

Diverging button

The example table now looks like this:

Example table with diverging colors

Font color scales

Selecting Conditional formatting and then Font color scales brings up the following dialog. This dialog is similar to the Background color scales dialog, but changes the font color rather than the cell background color.

Font color scales dialog

The example table now looks like this:

Example table with font color scales

Data bars

Selecting Conditional formatting and then Data bars brings up the following dialog.

Data bars dialog

By default, the Show bar only option is unchecked, and so the table cell shows both the bar and the actual value.

Example table with data bars and values

If the Show bar only option is checked, the table cell only shows the bar.

Example table with data bars only