I have an Expense & Mileage Template with Conditional Formatting set so that any date that is NOT within a specific fiscal year turns red. This is especially helpful when you are working on last year’s taxes in January, and you forget to type the year with the date. In Excel, if you leave out the date, it puts the current year in the cell…. but we are working on last year’s taxes, right?
The “between” conditional formatting works great for our dates, but now all the unused blank cells turn red, too. Sometimes I like that because it shows me exactly where to start entering new expense details, but maybe I don’t want a bright red column of blank cells at the bottom of my spreadsheet when I hand it to the boss. (Yes, I could just delete the blank rows or filter out the blanks in my table, but that’s not what this lesson is about.)
You must already have one conditional formatting rule for this to work. We already have one rule to turn cells red if the date is not in the fiscal year of the report. Drag through those cells you are formatting, the same ones that already have a conditional formatting rule to follow.
Now, go to the Home tab>Conditional Formatting drop-down, and select Manage Rules. The current Conditional Formatting rules will appear in the list. If you have more than one, be sure you know which one should ignore the blanks.
In the Conditional Fomatting Rules Manager window, select New Rule. We are looking for cells that contain nothing or blank cells. From the Select a Rule Type choices, choose “Format only cells that contain.” The bottom section will change specific to that rule type.
In the Edit the Rule Description section>Format only cells with boxes, click the drop-down next to where it shows “Cell Value.” Select “Blanks” from this menu. The default preview is “No Format Set.” That is just what we want: No Format! Click OK.
Now there are two rules, but if you click Apply, the blanks are still red. One last step is required.
First, be sure the “Cell contains a blank value” is the top rule and highlighted gray, or at least above the red highlight rule. If necessary, change the order by using the up and down carets. Is it above the “red” rule? Good. The formatting rules go in order, top to bottom.
Look to the right of the Conditional Formatting Rules Manager window. There is a column labeled, “Stop If True.” Check this box to tell Excel
- if a cell is blank, apply no format and stop all other formatting rules
- … AND…
- if the cell is NOT blank, continue to the next Conditional Formatting rule
Click OK and your red blank cells should switch to no color. There they are, simple blanks ready for information.
Helpful Office Tips 