Excel Training: Custom Conditional Formatting

The goal here is to add 3 conditional formats to the following table (lesson-8b.xlsx) in order to:

  • Format cells containing "x"
  • Format the column of the day (based on today's date)
  • Format a row (based on a search)

Formatting cells containing "x"

We will start by adding the first conditional format by selecting the range of cells and clicking on "New Rule" in "Conditional Formatting":

excel conditional formatting schedule png custom

To format the cells whose value is equal to "x", choose Cell Value equal to ="x" and set a format:

excel conditional formatting schedule x png custom

Formatting the column of the day

The goal now is to format the entire column of the schedule based on today's date (knowing that row 1 contains dates and that today's date can be obtained using the TODAY function).

Now select the range of cells including the dates and add a new formatting rule:

excel conditional formatting table png custom

Set a format then enter the formula =B1=TODAY():

excel conditional formatting formula today png custom

You may notice that only the cell containing today's date has been formatted.

When you enter a formula with references to cells, you need to do it for the cell in the top left of your selection. The formula will then adapt for the other cells as when you copy a formula (unless of course you have added $ to the references).

To lock the date test to row 1, you must therefore add a $ before the 1 =B$1=TODAY():

excel conditional formatting color column date today png custom

Formatting based on a search

To format a row based on the search in cell B23, select the cells in the table including this time column A and add a new rule:

excel conditional formatting search png custom

To search only in column A, you must add a $ in front of the A $A2 and 2 $ to the cell $B$23 since it must remain fixed. The formula for this conditional formatting is therefore =$A2=$B$23:

excel conditional formatting color row column png custom

If needed, you can change the order of the different conditional formats:

excel conditional formatting reverse order png custom

Renaming a cell

Instead of entering in the formula the cell $B$23 with its 2 $, you can choose to rename this cell and directly enter its name.

To assign it a name, select cell B23, enter its new name and validate:

excel rename cell custom conditional formatting

You can now enter search instead of $B$23 in the formula of the conditional format =$A2=search (or in any other formula of the workbook):

excel conditional formatting renamed cell png custom

You can find all the names assigned to cells in the "Name Manager" of the "Formulas" tab.