Skip to content

How to Use Conditional Formatting in Excel

< 1 minute read

Excel Green

Conditional formatting is an Excel feature that allows you to do just what it sounds like: apply different formatting to data depending on various conditions. It can come in handy when you need to identify certain criteria at a glance.

For example, you may want to see cells that contain duplicate values, or see values that fall in the top 10% of your range. Applying conditional formatting will change the cell color, font, and border depending on your criteria.

You can create your own conditional formatting rules, but to understand the tool, it’s easiest to first play with the pre-made rules that come with all modern versions of Excel.

  1. Select which cells or rows/columns you want to format.
  2. In the Home tab, click Conditional Formatting in the Styles area.
  3. Choose one of the options in Highlight Cells Rules or Top/Bottom Rules.
    1. If you’re stuck, try Text That Contains if your data has a lot of text, and include something you know is in multiple cells (like the word “yes”). If you data contains a lot of numbers, try Greater Than and enter a value that is less than a number on your sheet.
  4. Enter any conditions into the fields, and select a formatting option.
  5. Click OK. You will see cells meeting your criteria highlighted in the formatting you chose.

Once you understand the basics of conditional formatting, you can experiment with various criteria and formatting styles. For example, you can use conditional formatting to compare lists and find discrepancies. Try these methods for more uses of conditional formatting.

Sign up for our newsletter