Conditional formatting can be used to highlight certain values or make cells easy to identify. It modifies the appearance of a range of cells according to a condition or several criteria. In this article, you'll learn the tricks of conditional formatting and understand how to highlight the data you wish to advance in your analysis.
We’ll be using the table below throughout the article to illustrate the various features of excel conditional formatting. The table shows the price of different plant varieties according to the date on which they are sold.
1/ Conditional formatting using the Excel toolbar
- First, select the range of amounts, then click on Conditional formatting in the Home tab.
- If you choose Cell highlighting rules, you have several options. You can highlight amounts that are greater than, less than, between or equal to a certain amount… and even create your own rules using the “Other rules…” menu.
Example 1
When you select “less than”, you can enter a maximum amount (e.g. 10) below which the values will be highlighted, as shown below: all plants priced under €10 will be highlighted using the custom format you’ve chosen. Here, cells meeting this criterion are highlighted in blue with red characters.
Example 2
You can also highlight all cells containing a certain text, or dates occurring yesterday, today, tomorrow, in the last 7 days, last week, this week, next week, last month, this month or next month.
- You can also choose Set high/low range values. If you want to highlight the 5 highest values in red, select “10 highest values” and replace 10 with 5 in the dialog box. You can then change the format in which you want your values to appear (e.g. Light red fill with dark red text).
This is what you get:
- You can also select Data Bars, Color Shades or Icon Sets.
Example with icon set
Choose 3, 4 or 5 icons, depending on the number of slices you require.
- To modify these rules as you wish, click on Home / Conditional formatting / Manage rules.
And double-clicking on the Icon set rule produces :
(To be modified as desired)
If you have defined more than one rule for a cell range, and you have ticked Interrupt if true, the subsequent rules will no longer be applied if the first is true.
Please note: when you copy/paste cells, the conditional formatting is also copied.
Don’t hesitate to go to Manage rules in Home / Conditional formatting to create, modify or delete a rule. Look carefully at the cell ranges to which these rules apply.
As you need to select the cell range before modifying the rules, it would be a good idea to name this range in the Formulas / Define name ribbon, so that you can find your way around if your Excel sheet contains a lot of data.
Then simply select the name from the drop-down list on the left of the formula bar, before modifying the conditional formatting rules.
You can also change the priority of rules using the arrow keys.
2/ Formatting an entire line
Even more interesting! Excel also lets you color an entire row, not just a single column, according to the value of a column:
To obtain the table on the left, select the range of cells corresponding to the Plant column, then click on the Home / Conditional formatting tab and select the type of rule: Apply formatting only to cells containing the plant.
To obtain the table on the right, select the entire table and use a formula with a mixed reference: freeze the column but not the row, i.e. put a dollar in front of the column and nothing in front of the row, or press the F4 or Fn F4 key 3 times:
3/ Inserting functions into conditional formatting
You can also insert all Excel’s functions into conditional formatting if you choose to use a formula to determine for which cells the format will be applied.
Case study 1
Show the start and end dates of plant sales on a schedule as above. Freeze the column for the start and end dates and the row for the month dates with mixed references and use the AND function.
Select the grid cell range in the table and select Home / Conditional Formatting / Use a formula to determine for which cells the format will be applied:
Write the formula: =ET(F$3>=$A4;F$3<=$B4) then click on the Format… button and the Fill tab.
Case study 2
Highlighting weekends and the current date in a schedule
Let’s create two rulers by clicking on New Ruler then Use formula to determine for which cells the format will be applied and in the Apply formatting to values for which this formula is true, enter your formula. You can’t automatically insert the functions, you need to know them.
Click again on New Ruler or in Manage Rulers to see all the rulers in your selection.
Here are the two formulas to write:
=F$10=TODAY()
=OR(JOURSEM(F$10)=7;JOURSEM(F$10)=1)
Set a mixed reference by freezing the line containing all the dates of the month and use the Joursem function, which returns a number from 1 to 7 from Sunday to Saturday.
You can return to the formulas by clicking on Manage rules and double-clicking on the rule you wish to modify.
The result is as follows:
