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 value by 10%. Handling this request doesn't sound too difficult, but there are a couple of hitches. First, the data occurs in every fourth column. Also, if there is no data for the previous month, then you need to go back to the most recent month that does have data. So if there was data for January and April, the formatting for April would be dependent on the percentage change between April and January, but if new data was "found" and put into February or March, then the same formula should be looking at the new percentage change (April vs. February or April vs. March).

Here's a small sample to work with:

[ILLUSTRATION OMITTED]

As you can see, cell J2 (March) is highlighted because the percentage difference between it and cell F2 (February) is 11.5%. The formula in J6 is =ABS((J2-F2)/F2). However, if the February data were cleared, you'd see this:

[ILLUSTRATION OMITTED]

Now March isn't highlighted because the formula is comparing the 1062 in J2 with the value for January in B2, and that's less than 10% — because ABS((J2-B2)/B2) is 6.2%.

So what formula can you use to make this adjustment? Keep in mind that you need to be able to apply the same formula for each month through December (because January has no conditional formatting).

This Figure shows an array formula in cell J4 (which winds up being the formula used in the conditional formatting formula):

[ILLUSTRATION OMITTED]

Notice here that the formula contains mixed references in the formula: It goes from A or B as absolute to I (as relative — the column just before the active cell). The same formula in cell N4 is shown here:

[ILLUSTRATION OMITTED]

As you can see, it now refers from column A or B to M. It's time to decipher this to see how it works, using the formula for March in J4. You can first look at it as if there is data in February.

The first calculation involves looking only at columns B, F, J, N, etc. These columns have something in common: Their column numbers (2, 6, 10, 14, ...) all give a remainder of 2 when divided by 4. They're the only columns you're interested in:

[ILLUSTRATION OMITTED]

This formula expands to:

[ILLUSTRATION OMITTED]

When you multiply this series of TRUE and FALSE values by the same column numbers, you see the following:

[ILLUSTRATION OMITTED]

Now because you're interested in this column only if there's data in it, you also multiply by the "truth" of these values containing data (also assuming only positive numbers here):

[ILLUSTRATION OMITTED]

This formula expands to:

[ILLUSTRATION OMITTED]

And you want the latest column, hence the MAX, which gives 6. So now this part:

[ILLUSTRATION OMITTED]

is the INDEX(A2:I2,6), or F2, or 1200:

[ILLUSTRATION OMITTED]

When you subtract this from J2, you get:

[ILLUSTRATION OMITTED]

Okay, so what are you dividing the — 138 by? The denominator is the same; the formula in easy terms is =ABS((J2-F2)/F2), and that 1200 from two screenshots ago is what you're after. So now you have:

[ILLUSTRATION OMITTED]

which is:

[ILLUSTRATION OMITTED]

which is:

[ILLUSTRATION OMITTED]

or TRUE.

Now let's take one more look when February is blank. The inner part now expands to:

[ILLUSTRATION OMITTED]

And this is the INDEX(A2:I2,2), or 1000:

[ILLUSTRATION OMITTED]

which is:

[ILLUSTRATION OMITTED]

which is FALSE, so no conditional formatting.

To apply this to a whole column, you can use the Format Painter on cell F2 and then click on column F. Then you select F:I, like this:

[ILLUSTRATION OMITTED]

Then you right-click and drag the fill handle from the top-right corner of cell I1 to column AT (December), and when you let go, you see this:

[ILLUSTRATION OMITTED]

When you select Fill Formatting Only, you are done:

[ILLUSTRATION OMITTED]

Notice that the formatting rules apply to columns F, J, N, R, V, etc.

One more screenshot:

[ILLUSTRATION OMITTED]

May isn't highlighted because the value in February is being compared to the 1276 for May, and that's not >10%. February and June are highlighted because the most recent month is over a 10% change.

Rearranging Data (Revisiting a Technique from Excel Outside the Box)

Someone recently ask me to help him reorganize his data from one long list into three columns. He sent me a workbook with a small sample manually started to show what he wanted:

[ILLUSTRATION OMITTED]

The list in column A went to A6123, and the worksheet included the sample in columns C:E for the first six items. The formulas supplied were pretty obvious:

[ILLUSTRATION OMITTED]

But when you select C2:E3 and drag the fill handle, you get these formulas:

[ILLUSTRATION OMITTED]

This clearly is not what you want. You can solve this problem in two ways: using a formula or using VBA. Let's look at them both, starting with the formula version.

Using a Formula to Rearrange Data

You can change the formulas in C2:D3 to text by using the Replace command:

[ILLUSTRATION OMITTED]

This yields:

[ILLUSTRATION OMITTED]

Now, by selecting C2:E3 and filling down a few rows, you get:

[ILLUSTRATION OMITTED]

This is precisely what you need! So you can drag the fill handle down to row 2041 (that is, 6123 ÷ 3), which is a very long drag, or you can simulate the fill handle drag with a built-in but underused feature. First, you need to select C2:E2041. You can accomplish this by selecting cell C2, pressing F5 (Go To), typing E2041, and holding down the Shift key before clicking OK:

[ILLUSTRATION OMITTED]

This has the effect of anchoring the active cell (C2) so the entire range C2:E2041 is now selected. At this point, you use Home/Editing/Fill/Series:

[ILLUSTRATION OMITTED]

By selecting AutoFill in the Series dialog, you simulate using the fill handle:

[ILLUSTRATION OMITTED]

Finally, you replace x= with =, and you're done:

[ILLUSTRATION OMITTED]

Using VBA to Rearrange Data

The VBA approach to rearranging data is valuable, too. In VBA, if you qualify a range object with another range object, then the first one is deemed to be the entire worksheet. For example:

Range("C5:E12").Rows(3)

is the same as:

Range("C7:E7")

If C5:E12 were the entire worksheet, then the first row would be C5:E5, the second would be C6:E6, and the third would be C7:E7! In this case:

Range("C5:E12").Cells(4)

would be the same as:

Range("C6")

because after Cells(3), or (E5), the next cell would wrap to the next row. Knowing this, here's a procedure that runs very fast and creates the same result but without any formulas:

[ILLUSTRATION OMITTED]

And here's the result:

[ILLUSTRATION OMITTED]

Let's examine the VBA code a bit more closely.

The range A1000000 is somewhat arbitrary. In this case, Range("A1000000").End(xlup).Row would be 6123. The statement Range("C2:E1000000").Cells(i).Value = Cells(i, 1).Value uses this range object qualified by another range object, so C2:E1000000 is the "universe," and when i is 1, Range("C2:E1000000").Cells(i) is cell C2. When i is 4, Range("C2:E1000000").Cells(i) is cell C3, having "wrapped around" from cell E2. Try it; you'll like it.

Note that changing the range to six columns instead of three is trivial. The VBA code only needs to change C2:E1000000 to C2:H1000000, and here's the new result:

[ILLUSTRATION OMITTED]

Truncating Text and Showing an Ellipsis (...) After 47 Characters

In this Figure, you can see that cell A1 is selected, but the text shown in it is truncated:

[ILLUSTRATION OMITTED]

The formula bar shows the A1 text ending with "Figure it out," but the cell actually shows "Figure it o ..." Why is this the case?

Now look at cell A3:

[ILLUSTRATION OMITTED]

As you can see from the text in cells A2 and A3, anything over 47 characters gets chopped off and replaced by an ellipsis. What's going on? Well, a bit of trickery, of course. And it happens without macros!

Here's the length of the cells, by the way:

[ILLUSTRATION OMITTED]

The trickery involves a few steps. First, let's look at column AF:

[ILLUSTRATION OMITTED]

The formula takes the first 47 characters in cell A1, and if there's anything after that, the formula appends the ellipsis to the first 47 characters.

As you can see here, the font color in column A is white (this is so that the picture of the text you are showing from column AF doesn't overlap with the text in column A – you'll see this in a moment):

[ILLUSTRATION OMITTED]

But wait! How can you see the text if it's white? To figure it out, Ctrl+click on cell A1:

[ILLUSTRATION OMITTED]

Aha! You're actually looking at a picture of the cells in column AF! To implement the trickery here, you need to use the Camera tool. You also need to make column AF the same width as column A, so the picture looks perfect, and you need to remove the border of the picture . Here's how you remove the border:

[ILLUSTRATION OMITTED]

Note that this screenshot is from Excel 2013. The Format Picture options look different in other versions.

If you delete the picture, here's what you see:

[ILLUSTRATION OMITTED]

Notice that you still see the text in the formula bar, but you don't see the white font. I told you it's tricky! If it were not a white font, then both the text and the picture from column AF would show.

What? You're not sure how to use the Camera tool? You can get it by right-clicking the QAT and selecting Customize Quick Access Toolbar:

[ILLUSTRATION OMITTED]

Then you select All Commands from the top dropdown and then scroll down to the Camera tool:

[ILLUSTRATION OMITTED]

To take a picture for this example, you select AF1:AF3, click the Camera tool, then Alt+click in the top-left corner of cell A1:

[ILLUSTRATION OMITTED]

You hold down the Alt key here to align the picture with the border that's closest to where you click.