Software

A simple Excel conditional format technique that highlights input cells

Use this simple conditional format technique for Excel to solicit input values from users.

Conditional formats highlight data to alert users to specific conditions. It's a flexible and powerful tool, and it is sometimes overlooked if the need seems too simple. For instance, you'll often see sheets with shaded input cells. It's an easy way to visually tell users that they need to enter data. Often, the sheet's author just applies a normal shading format, which means the shading never disappears. It remains even after the user inputs the obligatory data. That's not wrong; it's just a bit inconsistent.

Using a conditional format, you can easily remove the shading once the user enters a value. Doing so might alleviate any possible confusion the user might sense when the shading remains. Now, let's look at a simple example using the simple sheet below. The formula in B3 will return the number of records that occur between the beginning and ending dates:

=COUNTIFS(A5:A12,">="&B1,A5:A12,"<="&B2)

This sheet's purpose isn't particularly important, but it does have two input cells: B1 and B2. You can apply conditional shading to these two cells as follows:

  1. Select B1:B2.
  2. Click the Home tab. Click Conditional Formatting in the Styles group, and choose New Rule. In Excel 2003, choose Conditional Formatting from the Format menu.
  3. From the Select A Rule Type list, choose the Use A Formula To Determine Which Cells To Format option. In Excel 2003, choose Formula Is from the condition dropdown.
  4. Enter the following formula =ISBLANK(B1)
  5. Click the Format button.
  6. Click the Fill tab (if necessary), select a color, and click OK. The dialog will display the formula and the fill color. In Excel 2003, click the Patterns tab.
  7. Click OK to return to the sheet. Cells B1 and B2 will display the fill color you chose in step 7.

The ISBLANK() function returns true while either cell remains empty.

Now, you could've done the same thing using the Fill Color option, bypassing the ISBLANK() function and saving yourself a few steps, right? Not really. Enter a date into B1 and see what happens. As soon as you press Enter or Tab, the conditional format formula (the ISBLANK() function) is no longer true, so Excel removes the conditional format - a subtle clue to the user that he or she has completed the task successfully.

At this point, you might be wondering if the result is worth the work. When you're creating a workbook for someone else, the answer is usually yes. It's a small addition, but it does have meaning for the user.

If you'd like to learn more about the COUNTIFS() function used in the example sheet, read Count the number of Excel records that fall between two dates.

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

8 comments
hulyalkar
hulyalkar

In 2007 onwards you will not require to write formula. I use built-in feature to indicate the cells that need to be filled -in using condition "Format only cells that contain" and select Blank from drop down menu at [b] Format only cells with [/b]

DaveRissik
DaveRissik

I find that simply adopting a different colour typeface and bold type is sufficient for identifying input cells. Quick and simple for personal use, but of course it does not provide protection in a multi-user environment.

edtech1300
edtech1300

The simple solution would be to add a second conditional formatting rule with a different cell fill such as the following "=NOT(ISBLANK(B1))"

pattas
pattas

Using Excel 2010 for this example, if you highlight the cell with a color that means "input needed" (lets say red), then the conditional formatting rule could be "ifnumber()", "iftext()" or a formula that confirms that the input is in an expected form with a different color (say green). Now the cell appears red by default (needing input) and green when filled in, continuing to show a highlight indicating where user input is allowed. Since multiple rules are permitted to apply to a single cell, you could even change the highlighting to distinguish between valid input and invalid input or any other condition you can put in a formula like highlighting if the input is outside an expected range.

maaho
maaho

so conditionally format the cell as a second color to indicate input received in an alterable cell

db1234567
db1234567

The problem with making the shading conditional is that once it's been removed it's not obvious to the user what fields are editable, or require input, and which are not.

ssharkins
ssharkins

This would work too, especially if it's a consistent format throughout the org.

ssharkins
ssharkins

It's not a right or wrong -- it just depends on usage. At some point, in this type of sheet, you'd need to clear the input cells -- and then they'd be shaded again, automatically. It's a good question!