Custom Formats for Dashboards and Spreadsheet Applications

Conditional formats are volatile. If you have too many, you see the effects of this volatility in serious calculations slowdown. As the function guru Charles Williams puts it, “…conditional formats seem to be super-volatile: they are evaluated each time the cell that contains them is repainted on the screen, even in Manual calculation mode….” But even too many non volatile functions can slow things down.

The good news is you have alternatives. For some specific instances you can get away with an Excel feature called Custom Formats, which is faster than conditional formatting and non volatile. It can also replace the IF and other conditional formulas.

However, you’ll deal with some major trade-offs. Custom formats as you’ll see can be confusing. And, because they’re not commonly used, others may not understand the work you’ve completed — and you yourself might even forget since custom format code is obscured away from the spreadsheet in a dialog box.

We’ll talk about those trade-offs and go through a few examples in this article.

Introduction to Custom Formats

Excel comes with an array of built-in custom formats. For instance, in the image below you can see I’ve placed a simple date on the worksheet. The representation of the date as month/date/year is in fact a custom format that Excel applies by default. You can see the format applied to any cell by selecting the cell and selecting More Number Formats from the format dropdown.

image

The image below shows custom code applied to the date on the spreadsheet. Notice initials like d and m stand in for things like day and month. These are part of the custom format codes that can be used to represent dates.

image

Conditional Expressions with Custom Formats

It’s the combination of these letters, numbers, and codes that allow you to make all sorts of customized formats. The codes in the previous image feature no conditional expressions; however, you can write conditions as formatting codes. The basic prototype for conditions is as follows: [Condition 1] ; [Condition 2]; [Else Condition]

Let’s say you apply the following condition code to a cell: [>10] “high”; [>5] “medium”; “low”.  The image below shows this code applied to the cells in column C. Column B simply equals the values in column C. Because the custom format is applied to column C, the values appear as the text we’ve outline in our formatting code. In C3, the value is 15; our custom format code tells Excel to represent this number as  “high.”

image

Notice cell B5 is simply set to be equal to cell C5 with a formula. In fact, all the cells shown in column B simply refer to the adjacent cells in C. The custom format acts as a mask for the actual underlying values. If you were to write =C3+5, the result value would be 20 (assuming you’ve switched the new cell to a General format) even as what’s being shown in C3 is text and not a value. Dates work in the same way: Excel stores dates as discrete whole numbers but they’re represented in a common date format. Custom formats only change how values are represented on a the spreadsheet; they does not affect underlying numerical values.

Custom Formats for Dashboards

Let’s apply what we’ve learned to a dashboard. Let’s say you want to create an alert where a colored dot character, ●, is shown next to over-budget project. The image below shows this effect. You would like a softer orange alert for projects $10,000 over budget and a harsher orange for projects even more over budget than that. The following image shows this effect achieved through custom formats.

image

To create this on your own, you would first figure out which projects are over budget in Column A. The image below shows we can do this easily by subtracting the amount spent from the budgeted amount.

image

Once this calculation is completed we can apply the following custom format to column A:

[Color46][>20000]●;[Color45][>=10000]●;[Color2]

So let’s break this custom format down. First, you might be wondering how to get the dot character. Well, you can copy and paste it from in this blog post. (That’s probably the easiest way!) Alternatively, you can insert it (or any other character) by going to the Insert tab and selecting Symbol. The dot resides in the Geometric Shapes subset. I usually insert the character to a random cell and then copy it from there, pasting it into the custom format as needed.

The next item to point out is the [ColorXX] code. Excel allows you to change the font colors depending upon the conditions satisfied. The numbers 45, 46, and 2 refer to color codes within Excel’s internal colors. Unfortunately, there are only 56 colors available, which is admittedly very constraining.  Click here to see the entire color list. (You might want to bookmark this link so you have the color table handy at all time.) For reference, Colors 46 and 45 are the dark and light orange respectively; Color2 is white.

Once you have the values in Column A as shown above, it’s a simple matter of selecting the values and applying the custom format. This is shown below with some resizing of column A to reflect how I want to see the dots. I’ve also increased the font size. Notice the custom format dropdown box on the ribbon now says “Custom” to reflect the custom format code we’ve created and applied.

image

Discussion

There’s much more to custom formats. For instance, the [>10] “high”; [>5] “medium”; “low” code from above could replace the following nested IF formula =IF(A1 > 10, “high”, IF(A1 > 5, “medium”, “low”)). The greatest advantage to custom formats however is that you can dynamically change how values are represented on the spreadsheet without the weight of additional calculation and volatility. Excel can instantly change how it represents an underlying value much faster than it can recalculate.

But as you can see custom formats are also pretty limited in formatting options; they’re sometimes hard to understand; and how they work isn’t always obvious (their code is hidden away in a dialog box). Indeed, for ninety-five percent of cases, the difference in calculation speed of an IF formula and the dynamic representation of a custom format is nominal.

Still custom formats present many interesting options for developers. For dashboards and models featuring heavy calculation, they prove to be a useful alternative.

Dashboards for Excel

My book Dashboards for Excel uses examples like this and so much more. Buy it today! (The book is out and shipping. Amazon is fixing the listing.)

Advertisements

4 thoughts on “Custom Formats for Dashboards and Spreadsheet Applications

  1. Jon Acampora

    Great post Jordan! You’re right that the string of conditions and formats can get confusing, but I think you did a great job of explaining how the custom format works.

    Reply
  2. Pingback: Excel Roundup 20151123 « Contextures Blog

  3. phil

    Amazing post, thanks, Jordan.

    One minor thing: I wouldn’t nest IFs but instead use REPT in combination with Boolean logic and concatenation (google Daniel Ferry’s “I heart IF” post – no affiliation btw 😉

    Reply
  4. Pingback: Auto highlighting Excel charts | Option Explicit VBA

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s