Free Online Microsoft Training

Free tips and tricks for using Microsoft Office and Windows

Free Online Microsoft Training

Free tips and tricks for using Microsoft Office and Windows

How to use conditional formatting in Excel

Print Friendly, PDF & Email

Conditional formatting in Excel is one of my favourite features to use. 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. Conditional formatting is the easiest way to achieve this result.

I am always surprised how often participants in training are manually formatting specific cells with different colours to highlight their values, rather than letting Excel do the work for them.

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.

Example

I have today’s date displayed in my spreadsheet. I want to apply conditional formatting based on the value in that cell (today’s date) and then have a group of cells in another section of the spreadsheet, change colour IF they match the date, or even if they are BEFORE or AFTER that date. I have several conditional formatting posts available so be sure to check out the list at the bottom of this post for links to check them out.

Apply conditional formatting in Excel

There are many different pre-defined conditional formatting rules ready for you to use. The rules are broken up into categories such as Highlight Cells Rules and Top/Bottom Rules. Let’s try one of the Highlight Cells Rules options.

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.
  4. From the Home tab on the Ribbon select the Conditional Formatting button within the Styles group:
  1. Move your cursor over the Highlight Cells Rules option and then choose Greater Than.
  2. The Greater Than dialog box will appear:
  1. Excel will automatically display a value in the value field (for this example, $43,275).
  2. 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.
  3. To directly enter the value you’d like to highlight for this range, type the value in. E.g. 24000
  1. 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 cell reference into the conditional format:
  1. Now to choose the type of formatting we want to apply.
  2. Use the drop-down menu to select from some predefined options, or choose Custom Format if you wish to choose your own formatting options:
  1. Excel will already show you a live preview of the result in your data.
  2. Once you are happy click OK. Remember you can always go back and change these settings later.
  3. 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:
  1. 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.
  1. Select the rule you wish to edit from the list and click Edit Rule.
  2. 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:
  1. Once complete click OK to close the dialog box.
  2. Now click OK on the Conditional Formatting Rules Manager window to return to your worksheet.
  3. 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:

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.

Facebook
Twitter
LinkedIn
Pinterest

Leave a Reply

Your email address will not be published. Required fields are marked *

three × four =

This site uses Akismet to reduce spam. Learn how your comment data is processed.

.pf-button-img { padding-top: 20px; padding-bottom: 20px; }