How to Apply Conditional Formatting in Excel

A Tutorial on How to Apply Conditional Formatting in Excel

Conditional Formatting is one of the most useful features for making data of a certain criteria really stand out in large spreadsheets.

Conditional Formatting in Excel
Conditional Formatting in Excel

The formatting can be based on the values in a cell – such as a number above or below a certain range – or you can use conditional formatting based on another cell.

Conditional formatting can also be used to highlight dates, text or duplicate values.

To use conditional formatting just select the cells you wish to apply the formatting to and select the Conditional Formatting options from the Styles group on the Home tab of Excel.

Conditional Formatting in Styles Group
Conditional Formatting in Styles Group

Conditional Formatting – Highlight Cells Rules

The predefined rules present under Highlight Cells Rules are:

  • Greater Than
  • Less Than
  • Between
  • Equal To
  • Text That Contains
  • A Date Occurring
  • Duplicate Values

For Greater Than, Less Than, Equal To or Between, you can either use conditional formatting based on another cell or based on a number that you type in yourself.

You can select the predefined options such as Light Red Fill With Dark Text, Yellow Fill with Dark Yellow Text, Green Fill  with Dark Green Text, Light Red Fill, Red Text, Red Border, or you can create your own custom format.

The Custom Format option allow you to change the font to bold, italics, and strikthough, underline and double underline. It also allows you to change the color of the text.

Start by selecting the cells you wish to format. Then click the down arrow next to the Conditional Formatting menu item.

Put your mouse cursor over Highlight Cells Rules and choose either Greater Than, Less Than, Equal To or Between. It will bring up a small application window where you can type in the amount or you can click the select cells button and click on the cell you wish to use for the comparison. Then click the the select cells box again to choose your formatting options.

If you choose Between it will allow you to select two cells separately.

After applying formatting to one or more of the options of Greater Than, Less Than, Equal To or Between you can click the Conditional Formatting again and apply another set of rules to the same set of cells as shown in the animation below:

Conditional Formatting Less Than, Greater Than and Between
Conditional Formatting Less Than, Greater Than and Between

 

 

Conditional Formatting Based on a Date