Using Conditional Formatting in Microsoft Excel

Have you ever had a spreadsheet that you’d like to apply formatting, such as highlighting a row, if a certain value was within a particular range? It’s not that difficult to do. Below, I’ll walk through doing this in Microsoft Excel 2003 and again in Microsoft Excel 2010. Microsoft Excel 2010 adds additional features such as including icons in the cells.

Conditional Formatting in Microsoft Excel 2003

Let’s assume we have a table that looks like the following:

We may want to highlight anything in red that is past due at least 60 days. If today’s date is June 5th, 2012, we’d expect results to look like the following:

To accomplish this, we’ll need to first highlight the ‘C’ column. This will mean anytime we add anything to this column, the conditional formatting will apply. We can perform this task from a single cell to the entire sheet. In our case, we’re only going to choose this column.

Once this column is chosen, we’ll choose Format > Conditional Formatting. Since we’ll be adding more rows later on, we’ll want to create a rule that will first check for blank data. We’ll add a second rule for the conditional date format. So, our rules will look like the following:

If we need to, we can add up to three rules per cell. If we’d like, we can also clear the rules for a set selection of cells by highlighting the cells, going to Format > Conditional Formatting, choosing the Delete button, then choosing the specific rules to remove (rules 1 – 3).

Conditional Formatting in Microsoft Excel 2010

The process is similar in Microsoft Excel 2010. Let’s assume we have a table that appears like the following:

We’ll apply conditional formatting to the column ‘C’ here as well. However, the traditional menu was replaced with a ribbon menu in Office 2010. So, we’ll actually find the Conditional Formatting option in the main ribbon such as highlighted below: