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:
However, we’ll see that we have many options to choose from in Excel 2010.
In our case, we’ll use the Highlight Cells Rules > Less Than and set our value to =Now() – 30:
Next, we’ll add our rule to clear out the blank cells. In this case, we’ll select Conditional Formatting > Highlight Cells Rules > Equal To:
We’ll change the value to =”” and change the style to a Custom Format making the fill color and border color transparent.
In the end, we’ll be left with a table that appears like the following:
To delete rules in Excel 2010, Choose the Clear Rules menu option.
You may find that using conditional formatting doesn’t fit your needs. What if you wanted to highlight an entire row based on the value of a certain cell? For something like this, macros do the best job.