Using a Conditional Formatting On/Off Switch

This worksheet uses conditional formatting to highlight each cell that contains a value over 50:

[ILLUSTRATION OMITTED]

There are several ways to get this kind of conditional formatting. One way is to use Highlight Cells Rules, as shown here:

[ILLUSTRATION OMITTED]

The worksheet above uses a formula, like this:

[ILLUSTRATION OMITTED]

This type of conditional formatting can be really helpful, but you might want to be able to turn it on and off in a worksheet. In that case, you could put a data validation switch in cell I1, like this:

[ILLUSTRATION OMITTED]

Then you could change the formula to this:

[ILLUSTRATION OMITTED]

Your worksheet would then look like this with the conditional formatting turned on:

[ILLUSTRATION OMITTED]

And it would look like this with the conditional formatting turned off:

[ILLUSTRATION OMITTED]

(The numbers keep changing here because the RAND function is used in the formulas in all the cells.)

Using Data Validation to Prohibit Entry of Digits

This figure shows an invalid entry error:

[ILLUSTRATION OMITTED]

This isn't a valid entry because there are digits in cell A1, and the worksheet is set up to allow only letters. To avoid getting this type of error, you can use data validation.

A data validation formula is a custom formula that appears in the Data Validation dialog:

[ILLUSTRATION OMITTED]

Here's the complete formula:

=NOT(ISNUMBER(MATCH(FALSE,ISERROR(1*MID(A1,ROW(INDI RECT("1:"&LEN(A1))),1)),0)))

Normally, as a worksheet function, this would need to be entered as an array (using Ctrl+Shift+Enter), but you can't enter a formula that way in the Data Validation dialog. Therefore, Excel treats any formula you enter as if it were an array formula.

This formula examines each character in cell A1 and multiplies it by 1 to determine whether it's a number. If it is a number, then it shouldn't be allowed.

Let's examine how this works with the ABC123 entry from the inside out (using F9 for each step):

[ILLUSTRATION OMITTED]

Therefore, the data validation kicks out the value as invalid because the data validation formula is FALSE.

Let's redo this examination, with A1 containing the text Hello World:

[ILLUSTRATION OMITTED]

Therefore, Hello World is a valid entry.

Making a Change in One Column Based on Criteria in Another (Without VBA)

Consider this Figure:

[ILLUSTRATION OMITTED]

Someone recently asked me how he could change the agent names in column A to "Internet" whenever column B contained either Hotels.com or Booking.com.

One way to do this would be to use a helper column: You could simply use an if-statement to test for either value and use "Internet" if TRUE or the current value in column A if FALSE; then you would copy the helper column and paste values into column A and then clear the helper column.

But this person didn't want to use a helper column — or VBA.

I thought about the problem a bit and came up with another way to approach it. I added a filter to the range and then select Hotels.com and Booking.com from column B:

[ILLUSTRATION OMITTED]

This yields the following:

[ILLUSTRATION OMITTED]

Next, I selected from A2 to the bottom and pressed Alt+; (the equivalent of selecting Go To Special/Visible Cells Only):

[ILLUSTRATION OMITTED]

Next, I typed Internet and pressed Ctrl+Enter:

[ILLUSTRATION OMITTED]

When I cleared the filter, I saw that I was done:

[ILLUSTRATION OMITTED]

Changing Row Height When a Key Field Changes

Look at this figure, which is part of a worksheet that continues for hundreds of rows:

[ILLUSTRATION OMITTED]

Say that the important column here is D, and you want to make the worksheet group together the rows with the same territory field, like this:

[ILLUSTRATION OMITTED]

In this case, rows 8 and 17 have a different height than the other rows, which provides some visual grouping for the territory entries.

To accomplish this kind of grouping, you can use a simple formula. In cell G4, you use the following formula:

[ILLUSTRATION OMITTED]

(You start at row 4 because that's the first row that could be different.) Then you fill the formula down to the bottom:

[ILLUSTRATION OMITTED]

Notice the 1s that appear in cells G8 and G17. The formula in G8 is =IF(D7<>D8,1,NA()), and D7 is not equal to D8, hence the 1. If you now select column G and press F5 (Go To) and click Special, you see these options:

[ILLUSTRATION OMITTED]

Notice that there are four sections under Formulas. Because you set up the formula to produce either a 1 or a #N/A value, you can now isolate just the numbers and ignore the errors. The original formula could have used any two of these options, though. For example, it could have used =IF(D3<>D4, "X",TRUE) to distinguish between text and logical values.

Now when you select just the formulas that are numbers, you see this:

[ILLUSTRATION OMITTED]

As you can see, only G8 and G17 are selected. So now you can change the row height from the Format tab:

[ILLUSTRATION OMITTED]

When you change the Row Height now, it applies to just those selected rows. You can set it to 25 or any other value:

[ILLUSTRATION OMITTED]

This is what you see now:

[ILLUSTRATION OMITTED]

All that's left to do is clear column G!

Using a Nontrivial Conditional Formatting Formula

Someone asked me how to conditionally format each month's data if its value is different from the previous month's...