June 21


Use Conditional Formatting to identify cells that are of interest

By Sharyn Baines

June 21, 2018

Conditional Formatting, Format

black smartphone near person


Learn how to use Conditional Formatting to identify cells that are of interest.

For example, you can apply a Conditional Format that checks to see a cell’s value is greater than $500. If it is the Conditional Format can change the Fill colour of the cell. Therefore making it really easy for you (and others using the file) to quickly identify any cell with a value greater than $500. 

Applying Conditional formatting

Excel can apply a different font, style, pattern and border to cells with content that fall outside or within the limits you specify.

This lets you quickly spot areas of interest or concern.

If a cell contains a formula, the value of the formula will be calculated, and the conditional formatting then tested against that value.

Setting a condition with Highlight Cell Rules

The Highlight Cells Rules option displays seven pre-set criteria that can be applied to a cell or group of cells.

To apply one of the pre-set Rules:

1. Select the cell, or group of cells, for which you want to apply conditional formatting.

2. From the Home tab, in the Styles group click on Conditional Formatting. A drop down menu is displayed. Select Highlight Cell Rules from the menu.

3. Select a condition from the drop-down menu, or select More Rules to create a new condition.

4. In the dialog box enter the condition to be matched. If the condition depends on the value of another cell, you can select the cell by clicking on it. In the example below the format will only be applied to cells containing a value “Greater Than” 500.

Excel Conditional Formatting

5. From the option box on the right select the format to be applied if the condition is met. There are a range of pre-set formats to use, or you can create your own by selecting Custom Format from the drop-down list.

6. Click OK. If you require another condition click Conditional Formatting again and create another rule.


Identifying duplicate or unique rows of data

To identify rows with duplicate or unique data:

1. From the Conditional Formatting menu select Highlight Cell Rules and then select Duplicate Values.

2. From the Duplicate Values dialog box select if you would like Duplicate or Unique values formatted. Click OK.

Excel Conditional Formatting


If you enjoyed this you may also enjoy...

How to Edit Drop Down List in Excel (find, add and remove items)

[Watch on YouTube] / [Subscribe to our YouTube Channel]Learn how to edit a drop-down list in Excel. In this blog you will learn how to find, remove and add items to the drop-down list. An extra step is also included for those using older versions of Excel.How to find items in a drop-down listClick onto the

Read More

How to Create Drop Down Menu in Excel (Drop Down List)

[Watch on YouTube] / [Subscribe to our YouTube Channel] Learn how to create a drop-down list in Excel. In this blog you will learn two different ways to create a drop-down list and the advantages of each as well as an extra step needed for older versions of Excel when creating a drop-down list from another

Read More

Remove Drop Down List in Excel

[Watch on YouTube] / [Subscribe to our YouTube Channel]Need to remove a drop-down list in Excel? In this blog you will learn how to remove the drop-down list as well as how to remove the menu items list.How to remove drop down list in ExcelClick onto the cell that has the drop-down list on it. In

Read More

How to Freeze rows in Excel

[Watch on YouTube] /  [Subscribe to our YouTube Channel]Would you like to keep row headings in Excel when you are scrolling so that you can easily identify your columns?  What if I told you there was a way to freeze rows and columns in Excel, so your headings come with you when you scroll down

Read More

Find, modify and break links to an Excel workbook

Learn how to find, modify and break links to an Excel workbook. Linking is great, until you no longer need it. Then it can be a bit of a pain. A couple of challenges may come your way if you weren’t the creator of the workbook that contains links. One challenge is identifying where the linked data

Read More

Link cells in the same or different Excel worksheets

Learn how to link Link cells in the same or different Excel worksheets. Linking saves a huge amount of time (and a huge amount of mistakes) in that it allows you to create connections from one cell to another. For example, if I’m creating a personal cash flow worksheet and at the end of the month I

Read More

Sharyn Baines

About the author

Sharyn is an expert trainer. She became the first certified Microsoft® MOUS Authorised Instructor in New Zealand. She is endorsed by Microsoft® as a qualified Microsoft® Office Specialist and has more than 20 years of experience in the training industry, developing and delivering technology training workshops. Her approach to taking the “techie-speak” out of technology training has placed her as a preferred supplier to many of New Zealand’s leading organisations.

{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}

JOIN FREE! 'Excel at Work Insiders Group' for free tutorials, mini-courses, videos and blogs!