Preview Excel Custom Formats


(Enter a format in column A and something in column B to preview the format.)

I was thinking of doing a simple post (hah!) on using Excel’s TEXT function to transform numbers to text, in order to match them to ID-type numbers from databases. One of my recurring work tasks is matching a host of possible school IDs from databases (text) to those in spreadsheets (numeric). I frequently use formulas like:

INDEX(tblFromCsv[School_Name],MATCH(TEXT(Numeric_ID,"0"),tblFromCsv[Text_Id],0))

Then recently a post here was featured on Chandoo’s site, along with some from other blogs – increasing my lifetime page views by about 30%. Searching his post for complimentary comments, I saw a couple along the lines of “Thanks for the great links, especially the one from Bacon Bits.”

Mike’s post is indeed a a beauty. It shows how to use custom number formats and create a percentage format like that shown in the first two rows of the interactive workbook above. It got me thinking about what formats you can specify in the TEXT function. It turns out anything you can enter as a custom format works for the TEXT’s Format argument, with very similar results. For example, this formula will format whatever’s in A1 with a format specified in B1:

=TEXT(A1,B1)

This got me thinking about creating a utility that shows the results of any custom format. Just like what you could get by using Excel’s custom format dialog or one line of VBA code, only a lot more work :). Although to be fair, Excel’s custom format dialog doesn’t show color:

Custom number format with no color preview

The reason TEXT doesn’t show color is that Excel functions – native and user-defined – don’t change the format of a cell. TEXT may seem like it’s breaking that rule, but it just returns a string that mimics the specified formatting.

So, adding color without VBA became my excuse reason for creating this tool. To do so, I used conditional formatting and an additional 29 columns of formulas. You can check them out by scrolling right in the workbook above. (You can also download it by using the button at the bottom of the workbook.)

There are two general types of custom formats, so my first task was to determine which kind is being used. The first has four different conditions in the form:

Positive number formats; negative number formats; zero formats; text formats

You can use less than four conditions. If you use only one, negative numbers and zero will use the positive format. If you use two, then zeros get the positive format.

The second type of custom form is more free-form and allows you to enter two custom conditions, and corresponding formats, along with a third format that covers all conditions not met by the first two. The conditions are specified using comparison operators enclosed in brackets like “[=]” “[<>]” “[>]”, etc. The form is:

1st condition and formats; 2nd condition and formats; formats for unmet conditions

Rows 4 through 6 above have some examples. Keep in mind that these custom conditions are evaluated from left to right, and when one is met the evaluation stops, like a VBA If statement.

This Microsoft page has a good explanation of the conditional formats including both of these types.

Determining which of the two types is being is used pretty easy. If the format contains any comparison operators inside of brackets then “Has custom conditions” in column H is true. I used an array formula that searches the format for one of the operators listed in D15:D20:

{=IF($F2,SUM((ISERROR(SEARCH(main!$D$15:$D$20,main!A2))=FALSE)*1)>0,"")}

The rest of the 29 columns contain equally convoluted formulas that tease out the various conditions and whether they have associated colors. These are summarized in columns J:M and N:Q.

The conditional formatting uses COUNTIF. COUNTIF is the only function I know that understands comparison operators combined with numbers in a string. For example, if you have the numbers 1 to 10 in cells A1:A10 and “>5” in B1 you can do this:

Countif with comparison in cell

So I ended up with a conditional format for each possible color, like this:

=INDEX(N2:Q2,MATCH(1,COUNTIF(B2,J2:M2),0))="[Red]"

It’s an array formula, but for some reason in conditional formatting you don’t have to enter them with Shft-Ctrl-Enter and you don’t get the curly brackets. If anybody knows how conditional formatting recognizes array formulas, I’d like to hear.

I know of two things that don’t work correctly in this tool:

  • Text is sometimes colored when it shouldn’t be. If a text color isn’t explicitly implied it should not be changed, unless the general format is used for the positive condition. With this tool it uses the positive condition format in all cases.
  • If you put a color or custom condition, things that are enclosed in brackets, inside of quotes, this will still recognize them as colors or conditions. So don’t do that, at least for testing.

I could fix the first, but I’m not sure about the second. If you see any other failings, feel free to leave a comment.

I mentioned that this all could be done with one line of code. Assuming you have a number in A1 and format in B1, this line of code will apply the format to the number. Note that you need to format B1 as text so you can enter formats like 0000 – without quotes – and not have Excel convert them to a single zero.

Range("A1").NumberFormat = Range("B1")

I learned a ton from doing this, and now have a much more detailed understanding of custom formats. Have you done any projects that were less-than-practical, but rewarding?

One thought on “Preview Excel Custom Formats

Speak Your Mind

Your email address will not be published. Required fields are marked *

To post code, do this: <code> your vba here </code>