- Article
- 9 minutes to read
APPLIES TO: 
PowerBIDesktop PowerBIservice
With conditional formatting for tables and matrixes in Power BI, you can specify customized cell colors, including color gradients, based on field values. You can also represent cell values with data bars or KPI icons, or as active web links. You can apply conditional formatting to any text or data field, as long as you base the formatting on a field that has numeric, color name or hex code, or web URL values.
To apply conditional formatting, select a Table or Matrix visualization in Power BI Desktop or the Power BI service. In the Visualizations pane, right-click or select the down-arrow next to the field in the Values well that you want to format. Select Conditional formatting, and then select the type of formatting to apply.
Note
Conditional formatting overrides any custom background or font color you apply to the conditionally formatted cell.
To remove conditional formatting from a visualization, select Remove conditional formatting from the field's drop-down menu, and then select the type of formatting to remove.
The following sections describe each conditional formatting option. You can combine more than one option in a single table column.
Format background or font color
To format cell background or font color, select Conditional formatting for a field, and then select either Background color or Font color from the drop-down menu.
The Background color or Font color dialog box opens, with the name of the field you're formatting in the title. After selecting conditional formatting options, select OK.
The Background color and Font color options are the same, but affect the cell background color and font color, respectively. You can apply the same or different conditional formatting to a field's font color and background color. If you make a field's font and background the same color, the font blends into the background so the table column shows only the colors.
Color by color scale
To format cell background or font color by color scale, in the Format style field of the Background color or Font color dialog box, select Gradient. Under What field should we based this on?, select the field to base the formatting on. You can base the formatting on the current field, or on any field in your model that has numerical or color data.
Under Summarization, specify the aggregation type you want to use for the selected field. Under Default formatting, select a formatting to apply to blank values.
Under Minimum and Maximum, choose whether to apply the color scheme based on the lowest and highest field values, or on custom values you enter. Drop down and select the colors swatches you want to apply to the minimum and maximum values. Select the Add a middle color check box to also specify a Center value and color.
An example table with color scale background formatting on the Affordability column looks like this:
The example table with color scale font formatting on the Affordability column looks like this:
Color by rules
To format cell background or font color by rules, in the Format style field of the Background color or Font color dialog box, select Rules. Again, What field should we base this on? shows the field to base the formatting on, and Summarization shows the aggregation type for the field.
Under Rules, enter one or more value ranges, and set a color for each one. Each value range has an If value condition, an and value condition, and a color. Cell backgrounds or fonts in each value range are colored with the given color. The following example has three rules:
When you select Percent in this dropdown, you’re setting the rule boundaries as a percent of the overall range of values from minimum to maximum. So, for example, if the lowest data point was 100 and the highest was 400, the above rules would color any point less than 200 as green, anything from 200 to 300 as yellow, and anything above 300 as red.
An example table with rules-based background color formatting based on Percent in the Affordability column looks like this:
Color by rules for percentages
If the field the formatting is based on contains percentages, write the numbers in the rules as decimals, which are the actual values; for example, ".25" instead of "25". Also, select Number instead of Percent for the number format. For example, "If value is greater than or equal to 0 Number and is less than .25 Number" returns values less than 25%.
In this example table with rules-based background color on the % revenue region column, 0 to 25% is red, 26% to 41% is yellow, and 42% and more is blue:
Note
If you use Percent instead of Number for fields containing percentages, you may get unexpected results. In the above example, in a range of percent values from 21.73% to 44.36%, 50% of that range is 33%. So use Number instead.
Color by color values
If you have a field or measure with color name or hex value data, you can use conditional formatting to automatically apply those colors to a column's background or font color. You can also use custom logic to apply colors to the font or background.
The field can use any color values listed in the CSS color spec at https://www.w3.org/TR/css-color-3/. These color values can include:
- 3, 6 or 8-digit hex codes, for example #3E4AFF. Make sure you include the # symbol at the start of the code.
- RGB or RGBA values, like RGBA(234, 234, 234, 0.5).
- HSL or HSLA values, like HSLA(123, 75%, 75%, 0.5).
- Color names, such as Green, SkyBlue, or PeachPuff.
The following table has a color name associated with each state:
To format the Color column based on its field values, select Conditional formatting for the Color field, and then select Background color or Font color.
In the Background color or Font color dialog box, select Field value from the Format style drop-down field.
An example table with color field value-based Background color formatting on the Color field looks like this:
If you also use Field value to format the column's Font color, the result is a solid color in the Color column:
Color based on a calculation
You can create a calculation that outputs different values based on business logic conditions you select. Creating a formula is usually faster than creating multiple rules in the conditional formatting dialog.
For example, the following formula applies hex color values to a new Affordability rank column, based on existing Affordability column values:
To apply the colors, select Background color or Font color conditional formatting for the Affordability column, and base the formatting on the Field value of the Affordability rank column.
The example table with Affordability background color based on calculated Affordability rank looks like this:
You can create many more variations, just by using your imagination and some calculations.
Add data bars
To show data bars based on cell values, select Conditional formatting for the Affordability field, and then select Data bars from the drop-down menu.
In the Data bars dialog, the Show bar only option is unchecked by default, so the table cells show both the bars and the actual values. To show the data bars only, select the Show bar only check box.
You can specify Minimum and Maximum values, data bar colors and direction, and axis color.
With data bars applied to the Affordability column, the example table looks like this:
Add icons
To show icons based on cell values, select Conditional formatting for the field, and then select Icons from the drop-down menu.
In the Icons dialog, under Format style, select either Rules or Field value.
To format by rules, select a What field should we base this on?, Summarization method, Icon layout, Icon alignment, icon Style, and one or more Rules. Under Rules, enter one or more rules with an If value condition and an and value condition, and select an icon to apply to each rule.
To format by field values, select a What field should we base this on?, Summarization method, Icon layout, and Icon alignment.
The following example adds icons based on three rules:
Select OK. With icons applied to the Affordability column by rules, the example table looks like this:
Format as web URLs
If you have a column or measure that contains website URLs, you can use conditional formatting to apply those URLs to fields as active links. For example, the following table has a Website column with website URLs for each state:
To display each state name as a live link to its website, select Conditional formatting for the State field, and then select Web URL. In the Web URL dialog box, under What field should we based this on?, select Website, and then select OK.
With Web URL formatting applied to the State field, each state name is an active link to its website. The following example table has Web URL formatting applied to the State column, and conditional Data bars applied to the Overall rank column.
Totals and subtotals
You can apply conditional formatting rules to totals and subtotals, for both table and matrix visuals.
You apply the conditional formatting rules by using the Apply to drop-down in conditional formatting, as shown in the following image.
You must manually set the thresholds and ranges for conditional formatting rules. For matrices, Values will refer to the lowest visible level of the matrix hierarchy.
Color-code based on text
Unlike in Excel, you can't color-code text values to display as a particular color, such as "Accepted"=blue, "Declined"=red, "None"=grey. You create measures related to these values and apply formatting based on those instead.
For example, StatusColor = SWITCH('Table'[Status], "Accepted", "blue", "Declined", "red", "None", "grey")
Then in the Background color dialog box, you format the Status field based on the values in the StatusColor field.
In the resulting table, the formatting is based on the value in the StatusColor field, which in turn is based on the text in the Status field.
Considerations and limitations
There are a few considerations to keep in mind when working with conditional table formatting:
- Any table that doesn't have a grouping is displayed as a single row that doesn't support conditional formatting.
- You can't apply gradient formatting with automatic maximum/minimum values, or rule-based formatting with percentage rules, if your data contains NaN values. NaN means "Not a number," most commonly caused by a divide by zero error. You can use the DIVIDE() DAX function to avoid these errors.
- Conditional formatting needs an aggregation or measure to be applied to the value. That's why you see 'First' or 'Last' in the Color by value example. If you're building your report against an Analysis Service multidimensional cube, you won't be able to use an attribute for conditional formatting unless the cube owner has built a measure that provides the value.
Next steps
For more information about color formatting, see Tips and tricks for color formatting in Power BI
FAQs
How do I apply conditional formatting to a table in Power BI? ›
In the Visualizations pane, right-click or select the down-arrow next to the field in the Values well that you want to format. Select Conditional formatting, and then select the type of formatting to apply.
How do I apply conditional formatting to an entire table? ›- Select the desired cells for the conditional formatting rule.
- Click the Conditional Formatting command. ...
- Hover the mouse over the desired preset, then choose a preset style from the menu that appears.
- The conditional formatting will be applied to the selected cells.
Conditional formatting in Power BI visuals allows you to draw attention to, or highlight, data in text or numeric fields using color, icons or data bars. You can also assign URL's to fields to make them clickable and load web pages.
How do I apply conditional formatting to multiple rows and columns? ›- Highlight all of the cells in the sheet to which you'll apply the formatting rules. ...
- Click Conditional Formatting.
- Select Highlight Cells Rules, then choose the rule that applies to your needs. ...
- Fill out the Less Than dialog box and choose a formatting style from the dropdown.
- Select the range of cells to apply the conditional format. ...
- From the Excel menu, Select "Conditional Formatting" and then "New Rule..."
- Select "Use a formula to determine which cells to format".
Conditional formatting can be applied to a set of selected cells, including an entire row.
How do I apply conditional formatting quickly? ›- Select the data that you want to conditionally format. ...
- Click the Quick Analysis button. ...
- In the pop-up that appears, on the Formatting tab, move your mouse over the different options to see a Live Preview on your data, and then click on the formatting option you want.
- Select the cells that have the names.
- Click the Home tab.
- Click on the Conditional Formatting icon.
- Click on New Rule option.
Select the cell (or range of cells) from which you want to copy the conditional formatting. Click the Home tab. In the Clipboard group, click on the Format Painter icon. Select all the cells where you want the copied conditional formatting to be applied.
How do I apply multiple conditions in conditional formatting? ›Conditional Formatting With Multiple Conditions
To highlight cells according to multiple conditions being met, you can use the IF and AND Functions within a conditional formatting rule. Select the range you want to apply formatting to. In the Ribbon, select Home > Conditional Formatting > New Rule.
How do I apply conditional formatting to multiple rows individually? ›
Select and right-click the range where you want to paste the formatting rule (B3:B10), (2) click Paste special, and (3) choose Paste conditional formatting only.
What is the limitation of conditional formatting? ›There is no way to perform analysis based on formatting. For example, you cannot count red cells (at least not easily). CONDITION → FORMAT is the end of the road... you cannot do any analysis on FORMAT.
Is conditional formatting good? ›Conditional formatting makes it easy to highlight certain values or make particular cells easy to identify. This changes the appearance of a cell range based on a condition (or criteria). You can use conditional formatting to highlight cells that contain values which meet a certain condition.
How do I bulk edit in conditional formatting? ›- Click a cell in the range of an existing conditional formatting rule.
- Click the Conditional Formatting button on the Home tab.
- Select Manage Rules. ...
- Select the rule you want to edit.
- Click Edit Rule.
- Make your changes to the rule. ...
- Click OK.
Check the rule you created.
Double-check the rule in the Conditional Formatting Rules Manager. Sometimes, for no reason at all, Microsoft Excel distorts the rule you have just created. So, if the rule is not working, go to Conditional Formatting > Manage Rules and check both the formula and the range it applies to.
There are two ways to use the Format Painter: Single-click the Format Painter to copy the formatting from the selected cell, then click a different cell to paste the formatting. Alternatively, you can click and drag over a range of cells to paste the formatting to multiple cells.
Why does my conditional formatting keep changing? ›I've found that Conditional Formatting formulas and ranges will automatically adjust when you copy, delete, or move data around in a spreadsheet.
What are examples of conditional formatting commands? ›Which of the following are examples of using the Conditional Formatting command? Turn text red if the number is above 1000. Change the fill color of a cell to yellow if the number is below 500. Turn the number to green if the number is in the bottom 10%.
What is conditional formatting and give an example of how do you apply it? ›Conditional Formatting (CF) is a tool that allows you to apply formats to a cell or range of cells, and have that formatting change depending on the value of the cell or the value of a formula. For example, you can have a cell appear bold only when the value of the cell is greater than 100.
What can conditional Formatting not be used for? ›You cannot use Conditional Formatting when you are in Group Mode. To ungroup worksheets, click on the tab of a sheet that's not in the group. This will immediately takes you out of group mode. If all the sheets in the workbook are grouped together, right-click on any of the grouped sheet tabs and click Ungroup Sheets.
Which Cannot be formatting using conditional formatting? ›
Not all format items are available in Conditional Formatting. For example, you cannot change the Font or Font Size with Conditional Formatting.
Can you prioritize conditional formatting? ›Conditional formatting logs your formatting rules so you can edit, prioritize, and delete rules easily.
Which tool allows to quickly copy and apply formatting? ›Use Format Painter to quickly apply the same formatting, such as color, font style and size, or border style, to multiple pieces of text or graphics. With format painter, you can copy all of the formatting from one object and apply it to another one—think of it as copying and pasting for formatting.
Is conditional formatting slow? ›Not many Excel users know that Excel Conditional Formatting is volatile. While you may not notice the difference with small data sets, it can result in a slow excel spreadsheet if applied on large data sets, or applied multiple times.
Can you format an entire column at once? ›Select the cell with the desired format and press Ctrl+C to copy its content and formats. Select the entire column or row that you want to format by clicking on its heading. Right-click the selection, and then click Paste Special. In the Paste Special dialog box, click Formats, and then click OK.
Can you have two rules in conditional formatting? ›Apply Multiple Conditional Formatting Rules
You can add many conditional formats to the same cell and range in order to get the desired effect.
We can check the conditions against Cell value or Formula when applying conditional formatting.
How do you explain conditional formatting? ›Conditional formatting is a feature of many spreadsheet applications that allows you to apply special formatting to cells that meet certain criteria. It is most often used to highlight, emphasize, or differentiate among data and information stored in a spreadsheet.
What are conditional formatting rules? ›To conditionally format your data, you can utilize preset rules such as Color Scales, Data Bars and Icon Sets or create custom rules where you define when and how the selected cells should be highlighted.
Is conditional formatting the same as data validation? ›Excel's conditional formatting tool applies formatting depending on cells' contents. Validation rules can change cells font, style, size or border. They can also change the text's color. For example, rules may highlight negative figures by printing them in red.
Is conditional formatting data validation? ›
1. Conditional formatting, which allows you to colour cells based on a condition like the size of a number, or even apply a colour scale to bring more clarity to your Google Sheet. 2. Data Validation, which allows you to create a drop-down list of items or regulate what data can be entered in a cell.
What are the benefits of formatting? ›The benefits of formatting are that: the document will have a clear and consistent layout. automated tables of contents for headings, tables and figures can easily be inserted and updated.
Can we apply formatting to a table? ›You can also change the format for an existing table by selecting a different format. Select any cell within the table, or range of cells you want to format as a table.
How do you conditional format a table based on another column? ›- Select M2 (The cell at row 2 in column M)
- Home > Styles > Conditional Formatting > Manage Rules.
- New Rule.
- "Use a formula to determine which cells to format" (you probably have done it)
- This step is one of the key that you need to know. ...
- Apply the format you want.
Now that we have everything ready, we can do the conditional formatting on the table. Select the table visual first, then go to the format tab, and under conditional formatting, select Revenue as the column. Set the Background color to On, and you will see conditional colors appears immediately.
Can you use table references in conditional formatting? ›you cannot use structured references in conditional formatting formulas. You can apply a conditional format that uses regular cell references to a table column and that format will be applied to new table rows. It gets VERY messy when new rows are inserted and deleted in the middle of the table.
What is the main advantage of formatting data as a table? ›Formatted tables have many advantages, such as:
The table range automatically expands (including the format) when you add new rows or columns to the table. Filter icons are automatically added to the header row. The headers are always visible as you scroll down the table.
Create a table, then convert it back into a Range. On the worksheet, select a range of cells that you want to format by applying a predefined table style. On the Home tab, in the Styles group, click Format as Table.
Which is a basic rule of formatting tables? ›Tables should be prepared using a roman font. Bold may be used for emphasis. Except for basic horizontal lines (see “Lines” below), tables should be free of lines, boxes, arrows, or other devises unless they indicate the structure of the data. Alternating white and gray rows are standard style shading for all tables.
How do I apply multiple conditional formatting in one column? ›Apply Multiple Conditional Formatting Rules
Select the cell or range to format and go to the Home tab > Conditional Formatting and choose a pre-defined rule from the menu or click New Rule at the bottom of the menu and make your own rule.
How do I apply conditional formatting with multiple criteria? ›
- Select the range to apply formatting rules.
- Click Home > Conditional Formatting > New Rule.
- Choose 'Use a formula to determine which cells to format', and type the formula: =AND(LEFT(F2,1)=” A”, RIGHT(F2,1)=” X”). ...
- Click on the Format button and add your preferred style.
Limitations. There are some limitations that come with formula-based conditional formatting: You can't apply icons, color scales, or data bars with a custom formula. You are limited to standard cell formatting, including number formats, font, fill color and border options.
Is there a limit to conditional formatting rules? ›For complex formatting, however, there is one drawback for most Excel users: The conditional formatting feature only seems to allow up to three conditions to be defined.
Why is conditional formatting highlighting incorrectly? ›If your formula refers to a wrong cell, a mismatch between the active cell and the formula will occur, which will result in conditional formatting highlighting wrong cells.
Does conditional formatting expand with table? ›Even though we can't use structured references, we still get some benefit from using a table, because Excel will keep track of the table range. If I add some new data to the table, the table expands and Excel automatically extends the conditional formatting rule to cover the entire range.