Conditional Formatting in Excel

This is another addition to my continuing series on Excel and the Project Manager. This time I want to demonstrate some advanced functionality using conditional formatting. Conditional formatting provides a high-level method to view and parse data without an extensive amount of formulas, sorting, or filtering. It can also provide an on the fly view of how data changes the outcomes, sort of like a simple dashboard.

Highlight Duplicate Values:

Often when presented with a long chain of data, instead of sorting and searching, Excel has a built-in conditional format indicator. To access, choose the conditional formatting menu under the home tab, then choose highlight cell rules, duplicate values.

Your list will look something like this (depending on the style you use):

Highlight Columns with Duplicates

Now I want to test your memory a little. In my last column, I brought in the =ISERROR() addition to the formula. If you remember, it was designed to suppress div/o type errors. I am going to apply some logic to a conditional formatting statement using this now.

One of my daily tasks in Excel is to find and highlight values that exist in two columns. To do this, you will need to use the Excel conditional formatting rule mentioned above, as well as a new one called =MATCH() rule for each column. Apply each rule as indicated below, and

For Column A: =ISERROR(MATCH(A1,$B$1:$B$10000,0))=FALSE

For Column B: =ISERROR(MATCH(B1,$A$1:$A$10000,0))=FALSE

Note. For such conditional formulas to work correctly, it’s very important that you apply the rules to the entire columns, e.g. =$A:$A and =$B:$B.

Stop Light Values

So, you are planning out your budget or schedule, and you want to set up some alerts. In Excel, there are some built-in icons and formats that allow you to easily do this based on entered values.

When editing this rule, you can use the defaults which group the values in thirds, or you can set your own values:

Using a Formula

Using a formula to create a highlight allows the project manager to add a custom touch to the formatted values. For instance, in my last blog I discussed the =NETWORKDAY() formula. A similar formula, =WEEKDAY(), returns a value referencing the day of the week (i.e. 1-7). We use a formula here =WEEKDAY(range, 2)>5. This will highlight any cells that are referenced as 6 or 7 (Saturday or Sunday).

How to Make Sure You Get What You Expected.

1. Use absolute and relative cell addresses correctly. Instead of applying the rule to just a few rows within the column, apply it to the entire column to avoid splitting up your formatting. Use an absolute value (with $) in your cell references, e.g. =$A$1=Wednesday.
2. Make sure you are selecting the correct range. Similar to above, but make sure your header row(s) or other values aren’t included that may cause some interference.
3. Write the formula for the initial cell; typically the top left most. That way you have an initial point of validation.
4. Check the rule you created. Sometimes I like to say that Microsoft likes to think for the User and this will distort the outcome. If the rule is not working, go to Conditional Formatting > Manage Rules and check the formula and the range. If you have copied or moved the formula, check it again.
5. Adjust cell references when copying the rule. If you copy Excel conditional formatting using Format Painter, don’t forget to adjust all cell references in the formula.
6. Break down complex formulas into multiple, simple elements. What works on the sheet as a formula does not always translate into the conditional formatting world.

[avatar user=”jchamberlain” size=”thumbnail” align=”left” /]JEFF L. CHAMBERLAIN, PMP | Project Manager 

Jeff comes to KTL Solutions with an extensive background in healthcare IT, technical consulting, and telecommunications. He has been a project manager for almost 20 years, holding certifications from the Project Management Institute as a Project Management Professional, from the Management and Strategy Institute as a Six Sigma Lean Professional, and he holds a Scrum Master Certification from the Scrum Alliance. He has managed both hardware and software implementations for both the government and private sectors, in industries such as healthcare, insurance, telecommunications, staff augmentation, supply chain and shipping.

Jeff has provided training for clients globally, working in Europe, Russia, North and South America on various topics from system optimization to wireless theory and design. He possesses a Bachelor’s Degree in Technical Writing from the University of Baltimore.