Conditional formatting in tables in Power BI - Step-by-Step Guide

Introduction

Conditional formatting helps you highlight important data in your tables by changing colors or styles based on values. It makes it easier to spot trends or issues without reading every number.

When you want to highlight sales figures above a target in a sales report

When you need to show negative profit values in red to catch attention

When you want to color-code customer satisfaction scores from low to high

When you want to visually separate high and low inventory levels in a stock table

When you want to apply different background colors to rows based on a category

Steps

Step 1: Select the table visual

- Report canvas

The table visual is active and ready for formatting

Step 2: Click the dropdown arrow next to the field name you want to format

- Fields pane inside the table visual

A menu with formatting options appears

Step 3: Select 'Conditional formatting' from the menu

- Field dropdown menu

The conditional formatting options panel opens

Step 4: Choose the type of formatting (Background color, Font color, Data bars, Icons)

- Conditional formatting panel

Options for the chosen formatting type appear

Step 5: Set the rules or color scale based on values or rules

- Conditional formatting panel

Preview of colors or icons appears on the table cells

Step 6: Click 'OK' to apply the formatting

- Conditional formatting panel

The table updates to show the conditional formatting visually

Before vs After

Before

Table shows sales numbers in plain black text with no color differences

After

Sales numbers above target are green, below target are red, making it easy to see performance

Settings Reference

Format by

📍 Conditional formatting panel

Choose how to define the formatting: by gradient colors, specific rules, or another field's value

Default: Color scale

Minimum and Maximum colors

📍 Color scale settings in Conditional formatting panel

Set the colors for the lowest and highest values in the data range

Default: Green to red gradient

Rules

📍 Rules settings in Conditional formatting panel

Define specific conditions to apply colors or icons

Default: No rules

Apply to

📍 Conditional formatting panel

Choose whether to format just data cells or also totals

Default: Values only

Common Mistakes

Applying conditional formatting to the wrong field

The formatting will not reflect the intended data and can confuse viewers

Always select the exact field column in the table where you want the formatting to appear

Using conflicting color scales that are hard to interpret

Colors may confuse users if they do not follow a logical order or contrast

Use intuitive color gradients like green to red or blue to white for clarity

Not clicking OK to apply changes

Formatting changes will not be saved or visible on the table

Always confirm by clicking OK after setting rules or colors

Summary

Conditional formatting highlights important data in tables using colors or icons.

You set rules or color scales on specific fields inside the table visual.

Remember to select the correct field and confirm changes to see the effect.

Practice

1. What is the main purpose of conditional formatting in Power BI tables?

easy

A. To change cell colors based on data values to highlight important information

B. To add new rows to the table automatically

C. To sort the table alphabetically

D. To create new calculated columns

Solution

1. Step 1: Understand what conditional formatting does

   Conditional formatting changes how cells look based on their values, usually by changing colors.

2. Step 2: Identify the purpose in tables

   This helps users quickly see important data like high or low values by color differences.

3. Final Answer:

   To change cell colors based on data values to highlight important information -> Option A

4. Quick Check:

   Conditional formatting = Highlight data with colors [OK]

Hint: Think: colors show important data fast [OK]

Common Mistakes:

• Confusing formatting with adding data
• Thinking it sorts or filters data
• Believing it creates new columns

2. Which of these is the correct way to apply conditional formatting to a column in a Power BI table?

easy

A. Select the column, go to Format pane, then choose Conditional formatting

B. Right-click the table and select Add Conditional Column

C. Use the Data view to type formatting rules directly in cells

D. Drag the column to the Filters pane and set color rules

Solution

1. Step 1: Locate conditional formatting option

   In Power BI, conditional formatting is set in the Format pane after selecting the column.

2. Step 2: Confirm correct method

   Options like right-clicking or typing rules in Data view are not how Power BI applies conditional formatting.

3. Final Answer:

   Select the column, go to Format pane, then choose Conditional formatting -> Option A

4. Quick Check:

   Format pane + column selection = Conditional formatting [OK]

Hint: Use Format pane after selecting column [OK]

Common Mistakes:

• Trying to add conditional columns instead
• Typing rules in Data view cells
• Using Filters pane for colors

3. You apply a conditional formatting rule to color sales amounts red if below 1000 and green if 1000 or above. What color will a cell with value 850 show?

medium

A. No color

B. Green

C. Red

D. Yellow

Solution

1. Step 1: Understand the rule conditions

   Values below 1000 get red color; values 1000 or above get green.

2. Step 2: Check the value 850

   Since 850 is less than 1000, it matches the red color condition.

3. Final Answer:

   Red -> Option C

4. Quick Check:

   850 < 1000 = Red color [OK]

Hint: Compare value to threshold to pick color [OK]

Common Mistakes:

• Mixing up color conditions
• Assuming default color if below threshold
• Choosing unrelated colors

4. You set a conditional formatting rule but no colors appear in your table. What is the most likely reason?

medium

A. You forgot to save the Power BI file

B. You need to restart Power BI to activate formatting

C. Conditional formatting only works in charts, not tables

D. The rule applies to a different column than the one shown

Solution

1. Step 1: Check rule application

   If the rule targets a column not visible or different from the displayed one, no colors show.

2. Step 2: Eliminate other reasons

   Saving file or restarting Power BI is not needed for formatting to appear. Conditional formatting works in tables.

3. Final Answer:

   The rule applies to a different column than the one shown -> Option D

4. Quick Check:

   Rule column must match visible column [OK]

Hint: Check rule targets visible column [OK]

Common Mistakes:

• Thinking restart is required
• Believing formatting only works in charts
• Ignoring which column the rule applies to

5. You want to highlight sales in a table where values are above average with green and below average with red. Which approach correctly sets this conditional formatting in Power BI?

hard

A. Apply a fixed color scale from 0 to 1000 without using average

B. Use a DAX measure to calculate average, then apply conditional formatting with a rule comparing each value to that measure

C. Manually color each cell based on your visual check

D. Create a new column with text 'High' or 'Low' and color based on text

Solution

1. Step 1: Calculate average sales

   Create a DAX measure that computes the average sales value for the dataset.

2. Step 2: Apply conditional formatting rule

   Use the measure in conditional formatting rules to color cells green if above average, red if below.

3. Final Answer:

   Use a DAX measure to calculate average, then apply conditional formatting with a rule comparing each value to that measure -> Option B

4. Quick Check:

   DAX measure + conditional formatting rule = correct approach [OK]

Hint: Use DAX average measure for dynamic coloring [OK]

Common Mistakes:

• Coloring cells manually (not dynamic)
• Using fixed color scale ignoring average
• Coloring based on text instead of values