Rules (If conditions)

You can use conditional formatting to change the font color, style, icons, background color, or border color based on one or more IF conditions.

To get started, refer to the Create Rule section. Once the title and applying options have been configured, follow the steps below to create one or more rules:

1. Create Rule

STEP 1: Choose 'Rules (If conditions)' in the Format by dropdown. You can see two sections - Style and Conditions.

Choosing Rules (If conditions)

STEP 2: Using the Style section, you can format text based on color/style, apply a background color or border color, or select icons. Within each of these options, you can change the color, border style, icon position, and visibility.

Style options

STEP 3: In the Conditions section, you can define conditions using options such as Number, Data selection, Value, Formula, and User Selection.

Format by rules

You can add multiple conditions and combine the rules with AND/OR. This is covered in the 'Add Condition' section.

Let's look at examples for each of these options.

2. Style

• Font style: bold, italic, underline

Set the font style according to the conditional formatting rules. Notice how we've used bold, italics, and underlining to highlight the values that meet the condition. Click on the icon to select a color for the underline.

Font style

• Cell background

Use a background color to highlight the cells that match the conditional formatting rule.

• Font color

Apply a custom font color when a conditional formatting rule is met.

• Cell borders

Highlight cells that satisfy the conditional formatting rule by setting a custom border. You can choose the border color by clicking the icon.

• Adding icons or text

Display icons to apply conditional formatting on your data. You can also use custom icons by clicking the Upload Icon link.

You can position the text/icons with respect to the cell values. You can also choose to display only the icons or text and hide the cell values.

When you use icons, you have an additional option to align your icons in the grid. By default, the icons are left-aligned.

• Hiding values

Business reporting may require withholding certain information to protect strategic interests. You can use conditional formatting rules to mask values based on specific criteria.

3. Conditions

3.1. Number

In this example, we are highlighting the subcategories where the Actual Sales are greater than 300 thousand. Note that we have selected 'Bold' and selected 'Green' as the font color. We have chosen grey as the cell background color. The numbers field supports scaled entries such as 300k. Click 'Apply' once the changes are done.

Conditional formatting based on a numeric input

3.2. Data selection

You can also use a cell value in the condition.

• Select 'Data selection', click on the 'select value from' field, and then click on a cell in the report.

Applying formatting based on a cell value in the report

• The value of the selected cell appears in the condition field when you click on it.

Selecting data from the report

• Click on 'Apply' to apply the formatting where the actual sales are greater than the selected value.

Conditional formatting based on data selection

3.3. Value

You can use conditional formatting based on another measure in the visual. Let's highlight actuals that are higher than the previous year's sales.

Measures that were added from the data source, auto-calculated variances, columns/measures created using calculations, data input and simulations can all be used in the IF condition.

Click on the highlighted dropdown and select PY Sales. Click 'Apply'.

Conditional formatting based on another measure

3.4. Formula

You can create simple formulas using either numeric values or measures. Let's highlight the records where the actual sales are greater than the previous year's sales by at least 200,000. Configure as shown in the below image and click 'Apply'.

Conditional formatting based on formula

+ Add Condition

The 'Add condition' option lets you create nested AND/OR conditions. Data is formatted according to the logic and list of conditions that have been configured.

STEP 1: Click on Add Condition to add additional condition(s).

Add Condition

STEP 2: You can see another condition with default selections and an option to select AND/OR. Let's select 'AND' and 'Category' from the dropdown.

Adding a condition with AND

STEP 3: You can see several options as shown in the below image. Let's go with the default option.

Options for 'Category'

STEP 4: In the 'Choose members' dropdown, let's select 'Computers' and 'Cell phones'.

Selecting a category

STEP 5: You can add as many conditions as you need and combine them with either AND or OR. Click on 'Apply' to apply the formatting.

Adding multiple conditions

3.5. User selection

You can apply conditional formatting based on a selection during runtime, such as in the reading view. Consider the following scenario: we want to use conditional formatting on 'Actual Sales' based on either the user-selected actual sales or the previous sales value at runtime.

• Configure as shown in the below image. Note that 'Actual Sales' is set in the 'Apply to' field and 'Actual Sales' and 'PY Sales' in the IF condition. Click on the dropdown and select both.

Conditional formatting based on user selection

• Let's add an icon as formatting this time. Click on the 'Icon' checkbox. There are several customization options.

Adding an icon

• Click on the icon dropdown and choose any icon.

Choosing an icon

• Let's change the flag color to green and the font color to black. Click 'Apply'.

Customizing the icon color

• Click on a value in any of the sales columns. You can see flag icons in the 'Actual Sales' column where the actual sales value is greater than the selected sales.

Conditional formatting based on user selection

4. Conditional formatting for data input fields

Highlight your data input fields, like number, dropdown, text columns, etc., with conditional formatting. When users enter values, you can define rules that automatically apply formatting when the rules are met, such as highlighting the cells when the budget entered exceeds a certain limit.

Let's draw attention to the input budget value that is more than 100,000.

Conditional formatting for data input fields

5. Rules for non-numeric measures

You can apply conditional formatting based on non-numeric measures using conditions such as "contains", "does not contain", "starts with", etc. In this example, we have used borders to highlight the words that contain "Contoso."

Formatting based on text conditions

In the next section, we'll be covering Color scale.