Advanced conditional formatting

Conditional formatting allows us to customize the way that cell or group of cells is displayed in order to highlight their values. When using conditional formatting options, we can use some of the predefined rules discussed earlier. However, we often want to set the rules for formatting independently, to determine the order of their operation, or to disable some of them …

Every time we choose one of the conditional formatting options, we are actually creating a new rule. By opening the Conditional Formatting group, located in the Home ribbon, the menu at the bottom of the Manage Rules menu appears. Starting this option opens a dialog box where we can create a new one or edit the existing conditional formatting rules.

At the top of the Conditional Formatting Rules Manager window there is a small drop-down menu where we can first choose which group of rules we want to edit. These can be rules within the Current Selection, all the rules defined in This Worksheet (Sheet: Sheet2, Sheet: Sheet3, etc.).

In the main part of the window we can see all the existing rules. For the existing rule, we first see the formatting criteria, so for example, how the cells are formatted, and then on which cell or group of cells this format refers. By clicking on the rule, by clicking Edit Rule we can change the conditional formatting rules. A similar dialog window opens when we want to make a new rule, which we do by clicking the New Rule option.

The New Rule dialog box consists of a set of options, from the top of the window, and formatting criteria that change depending on which group of formatting options we have selected. Option groups represent a summary of the previously presented options for conditional formatting, which serve to: Format all cells based on their values, Format only cells that contain, Format only top and bottom ranked values, Format only values that are above or below average, Format only unique or duplicate values. Starting of the offered the option will display a different combination of the input field for the criteria and drop-down lists, as well as the Format button, which opens the Format Cells dialog box in which we can fine-tune the desired formatting format in detail.

The last set of options is called Use formula to determine which cell to format, and it is particularly interesting because it allows conditional formatting based on the given formula. The formula must start with a sign of equality, and it always represents the logical condition that completes the formatting of the cell. For example, if we selected a group of cells in the E column (E2: E10) in which we have some values, by entering the formula “= $ E2> 100000” and selecting a particular format, e.g. coloring the background of the cell in yellow, we will highlight all rows in the range (E2: E10) that fulfill the given condition. Pay attention to fixing column E in the formula! This is being done because there is an automatic update of conditions in conditional formatting, as when copying the formulas, unless we emphasize that the condition applies only to the E2 cell (“= $ E $ 2> 100000”). When entering the formula as conditional conditionalization criteria, we can use all available Excel functions. For example, by entering the formula “= MOD (ROW (), 2)” and selecting gray background color, each second row in the selected cell group will be colored in gray. When using this option, be aware that you apply rules only over values, not over the table header!

Return to the Conditional Formatting Rules Manager window. At the top of the window, except for New Rule and Edit Rule, there is also the Delete Rule option where we can delete the selected rule. Also, to the right of it, there are two arrows whose click, after we have previously selected a rule, we can move the rule “down” or “up”. That is, we can change the order of execution of the rules. To the right of each rule there is also the field if Stop if True. If it is marked, the rule ceases to apply if the condition that it is specified is fulfilled.

In the Conditional Formatting dialog box there is a group of Clear Rules options. It can be used to delete the specified rules from the current selection, worksheet, workbook or from the pivot table. It has the same function as the Delete option in the Conditional Formatting Rules Manager window, only by means of which we can delete multiple rules at once.