Column Conditional Formatting

Conditional formatting lets you format individual cells within a column based on specific criteria. Use it to quickly highlight trends, exceptions, or important values directly in your worksheet.

This is especially useful for analyzing KPIs, identifying outliers, or emphasizing key performance thresholds.

When to use column conditional formatting

• Highlighting cells by value: Draw attention to cells that meet specific criteria (such as greater than, contains, or equal to).
• Adding visual cues: Use color, font styles, or icons to improve data interpretation.
• Applying dynamic rules: Format cells automatically as data changes.
• Customizing for group levels: Tailor formatting for particular data levels or groups.

Define column conditional formatting rules

1. Do one of the following to open the Conditional Formatting dialog:
   • Right-click the field in the Columns section and select Conditional Formatting.
   • Right-click a column header in the worksheet and select Conditional Formatting.
   • In the Toolbar panel, select Worksheet Properties. Expand Columns, select the field, and click Conditional Formatting.
2. Click the + icon to creare a new rule, or the pencil icon to edit an existing one.
3. Make your selections from the available options.
4. Click Confirm when finished.
5. Click Confirm again in the Conditional Formatting dialog to apply the rule.

Conditional Formatting properties

Property	Description
Description	The name or details of the rule for easy reference.
Format	Formatting that is applied when the condition is met.
Comment	Notes or explanations about the purpose of the rule.
New Rule	Create a new conditional formatting rule.
Edit Rule	Edit the selected rule.
Delete Rule	Remove the selected rule.
Move Up, Move Down	Change the rule’s priority order by moving it up or down.

Conditional formatting options

The following options are available when creating or editing a conditional formatting rule:

Option	Description
Dependent Column	Select the column whose values trigger the formatting. You can use the same column, a different column, or a group code.
Compare with	Choose the comparison type: Constant – Use a fixed value you set for comparison. Global Variable – Use a variable that can be updated for all rules, such as a minimum threshold or color range. Column – Compare values against another column—for example, checking if the actual date exceeds the due date or if actuals are above budget.
Value Condition	Select a condition operator. Options depend on the data type from Dependent Column: Number: Between, Not Between, Equal to, Not Equal to, Greater than, Less than, Greater than or equal to, Less than or equal to. Character: Containing, Equal to, Not containing, Beginning with, Null, Not null, Ending with, In, Not in.
Level of rule applied	Choose the group level to apply the rule: Everywhere Last level only This group only Not this group This group and lower level This group and higher level
Style	Apply font formatting: Regular, Italic, Bold, Bold Italic.
Border	Add borders to cells. Define edge, style, and width (top, bottom, left, right).
Icon	Display a visual indicator (such as arrows, flags, symbols) based on the value.
Color	Change the font color when the condition is met.
Background Color	Set the background fill, or select Transparent for no fill.
Background Option	Choose how to apply the background: Full – Fill the entire cell. Based on Cell Value – Fill proportionally by value. Based on Percentage of Parent – Fill based on the value relative to its group.
Preview	View how the formatting will look before applying.
Comment	Add a description detailing the purpose of the rule.

Change the priority rules

When you have multiple rules defined in the Conditional Formatting dialog, you can adjust their priority.

1. Open the Conditional Formatting dialog.
2. Select a rule and use the up and down arrows to move it higher or lower in the list. The higher a rule appears in the list, the greater its priority.
3. Click Confirm when finished.

note

Only the first matching rule applies to each cell. Remaining rules are ignored for that cell.