How to use conditional formatting in Excel

Conditional formatting in Excel is one of my favourite features. I am a very visual person and would much rather look at colour charts or visual representations of data to give me a sense of what I’m looking at. In this post you’ll learn how to apply basic conditional formatting rules, how to customise them, and how to edit them later so your spreadsheets practically format themselves.

What is conditional formatting in Excel?

Conditional formatting in Excel will allow you to apply “rules” or “conditions” to a group of cells and based on the rule, Excel will then format the content. There are many pre-defined conditional formatting rules available but you can also define your own custom rules to suit your requirements.

Some examples might include:

• Format cells based on their value. E.g. If the cell is greater than 50, make the cell go Green.
• Format cells that contain a specific value. E.g. Format any cells with the number 70, make the text go Red.
• Format the highest or lowest values. E.g. Format the top 10 values in Blue and the lowest 10 values in Red.
• Format cells that are above or below the average.
• Format unique values, or duplicate values. E.g. All cells that contain the same name, format them in Blue.
• Format cells based on a formula. E.g. If a cell value is before todays date, format it purple.

Excel conditional formatting can be based on the cell you wish to format, or you can apply conditional formatting based on another cell. The last example in the list above is a perfect example of this. If we wanted to apply conditional based on another cells value, we can do that.

Common examples

Wondering HOW you can use conditional formatting in everyday situations, here are just a few simple ways:

• Highlight overdue tasks: this is great for tracking projects, to-do lists or anything with a due date.
• Find blank or missing information: use conditional formatting to highlight empty cells within your data.
• Identify duplicate entries: Conditional formatting is perfect for flagging duplicate values and help you clean up your data.
• Highlight cells containing specific text: If you need to keep track of specific text references, conditional formatting makes it easy.
• Compare values to a target: If you have a target or benchmark you can create a rule to highlight values that are above/blow the target.
• Data bars for quick comparison: Using data bars inside each cell provides a way to visually compare information without a chart.

Apply conditional formatting in Excel

There are many different pre-defined rules ready for you to use. The rules are broken up into categories such as Highlight Cells Rules and Top/Bottom Rules.

In the following exercise we are going to look at a range of different conditional formatting options so you can see the range of options ready for you to use.

In the example below I have a target value of $24,000 stored in cell E3. I want to have Excel format any cell within cells E5:E16 that has a value greater than the target.

To apply the Greater Than conditional format, follow these steps:

1. Open Microsoft Excel.
2. Open a workbook which contains content you wish to use conditional formatting on.
3. Highlight the data which you want the rule to apply to. In my example I have highlighted cells E5 to E16.
4. From the Home tab on the Ribbon select the Conditional Formatting button within the Styles group:

5. Move your cursor over the Highlight Cells Rules option and then choose Greater Than.
6. The Greater Than dialog box will appear:

7. Excel will automatically display a value in the value field (for this example, $43,275).
8. You can either enter the exact value you want to use for this rule, or you can use cell references. If the value you want to use is being stored in a cell within your workbook, you can link to that cell.
9. To directly enter the value you’d like to highlight for this range, type the value in. E.g. 24000

7. If the value is being stored in a cell, use the mouse to click on the cell which holds the data. Excel will automatically add the absolute cell reference into the conditional format:

7. Now you need to choose the type of formatting you want to apply.
8. Use the drop-down menu to select from some predefined options, or choose Custom Format if you wish to choose your own formatting options:

7. Excel will already show you a live preview of the result in your data.
8. Once you are happy, click OK. Remember you can always go back and change these settings later.
9. Any data which meets the criteria you have set in the conditional formatting rule will now be highlighted:

Edit a conditional formatting rule

If you need to edit an existing rule, follow these steps:

1. Highlight the data area containing a conditional format.
2. From the Home tab click the Conditional Formatting button then choose Manage Rules.
3. The Conditional Formatting Rules Manager will appear:

3. If you want to view all conditional formatting rules set up in your entire worksheet, click on the Show formatting rules for drop-down menu at the top of the dialog and choose This worksheet.

3. Select the rule you wish to edit from the list and click Edit Rule.
4. You will now see the specifics of the rule you have created, including the type of formatting which will be applied, you can edit any aspect of these settings:

6. Once complete click OK to close the dialog box.
7. Now click OK on the Conditional Formatting Rules Manager window to return to your worksheet.
8. You will now see the changes applied to your rule.

There are some amazing options to use conditional formatting in Excel, so it’s worth taking the time to experiment with some of them. I personally find the Top/Bottom rules are very useful as are the Data Bars and Icon Sets.

If you are working with date based data, be sure to check out some great ways you can work with conditional formatting in Excel and dates:

• Use conditional formatting to identify dates older than today
• Use conditional formatting to identify dates within a specific range
• How to conditional format an entire row based on dates older than today

I hope you have enjoyed this post about how to apply a basic conditional formatting rule to data.

Enjoyed this article? Remember to share this with your friends or coworkers and comment below if you have any questions.