W

orking with Custom Number Formats

If you’ve worked with Excel very long, you have probably gone through all of the number formats available to you and seen some custom ones that you wish you knew how to do.

BTW, just to be clear we’re talking about Format Cells –> Number –> Custom

I’m going to cover a couple of my current favorites just to get the conversation going but it would be great if others chimed in with their favorite custom number formats or with their questions!

In science we have scientific notation, which Excel handles but it’s ugly and somewhat mysterious. Ten Billion (10,000,000,000) would be displayed as 1.00+E10 (one times 10 to the tenth power). In business we often shorten big numbers too right? 10 billion, 300K, 15M(illion), etc and it would be helpful if we could shorten really big numbers in a similar fashion because we often don’t need the full degree of accuracy to be shown (it will still be there but we want to shorten the number to a reasonable bite-size). So here’s the trick that I learned recently…

We’re probably all familiar with this number format:

#,##0.00 - this is Excel’s default number format (in the US) and displays the number with commas separating thousands, millions etc. and two significant digits e.g. 10,000,000,000.00. Well we get a lot of business speak around here and don’t need that level of accuracy - in most cases - so why not have the same shorthand in the reports that we do in language?

By adding successive commas to the custom number format, we can “divide” the original number by a thousand.

For instance, take our 10,000,000,000 and apply the custom number format of #,##0.00,

Now our number is 10,000,000.00 thousands I can even indicate this by adding the following to my number format: #,##0.00,”T” (or “M” for you Romans) and the number is displayed as 10,000,000.00T

Two commas (#,##0.00,,”M”) reduces it to millions as in 10,000.00M

Three commas (#,##0.00,,,”B”) reduces to billion - 10.00B

Effectively, we’ve taken a very large number and reduced the space it takes to display it - higher data density. We’ve given up a bit of the precision, from a display perspective, but when working with very large numbers, the significant digits can be effectively reduced and still convey the important information.

My other example today relates to a pet-peeve of mine - consistency. When working with currency - $1,000.00 - one of the choice formats is to display negative values in red and in parenthesis. Apparently people who spend all their time in ledgers appreciate this format. However, there is no matching format for percentages and I’m always calculating percent change. So, I want my negative percent changes in revenue to match my negative revenue numbers.

Again, the default currency format for negative currency is

($1,000.00) which is the equivalent of the custom format $#,##0.00_);[Red]($#,##0.00)

Ok, first an admission I don’t know what some of the above means - specifically the _) just before the semicolon, if someone wants to explain that would be awesome. But I do know that everything before the semicolon deals with positive numbers and everything after it deals with negative numbers. So [Red] in brackets tells Excel to display the text in red and the parentheses surrounding #,##0.00 tells Excel to surround it in parentheses.

So it’s pretty easy to map the same formats to percentages. First we take the custom format equivalent of Excel’s standard percentage display: 0.00%. Notice that it doesn’t do anything with thousands or with negative, so we just plug it into the custom format from above thusly:

#,##0.00%_);[Red](#,##0.00%)

1,000.00% | (1,000.00%)

Effectively, all we did was add the percent sign (%) after the significant digit declaration and we now have percentages that will be similarly formatted to currency.

What about you? What’s your favorite number formatting trick in Excel?

Wow! It’s been a really, really long time since I have done one of these. According to Wordpress, the last "Sharing the Wealth" post was published on September 27, 2006 - discussing how to store color assigments in VBA. Guess I haven’t been doing a lot of Excel work in the last year!

All that aside, today’s tip is just too obvious not to pass a long, although you may want to file it under ReadTheManual…

Someone asked me today "How can I match rows where the string contains the substring "abc" and total those rows?" Ok, so I’m paraphrasing but this was the question. Being a little rusty I went right along with it and said something like "Right, you can’t use SUMIF because it doesn’t do fuzzy matching."

WRONG! WRONG! WRONG!

After I got off the phone, I hopped into Excel help, not because I thought it would tell me something about SUMIF that I didn’t know but because there might be another formula to help me out or at least a kernel of an idea.

It turns out that SUMIF supports the wildcards ? and * (question mark and asterisk). Furthermore you can match on those characters be prefixing them with a ~ (tilde).

Here are some sample conditions, the appropriate wild-carding and the SUMIF formulae:

Ok, So the last two are a bit of a trick. We’re using SUM as a parent function to the two SUMIF functions to create the OR operator and the new in Excel 2007 SUMIFS function to match on multiple criteria for the AND operator.  SUMIFS is just like SUMIF but matches on multiple criteria (can be from multiple locatioins too) but notice that the SUM RANGE comes first instead of the CRITERIA RANGE which comes first in the more widely known SUMIF.

Not enough? You can download this sample spreadsheet to see it in action.

• Excel 2007 version with SUMIFS
• Excel 2003 version without SUMIFS

This is another in a long litany of my "DUH" moments with Excel, what about you? What’s your biggest DUH moment with Excel? 

Related Posts:

• Sharing the Wealth: Random Excel Tip
• Sharing the Wealth: Random Excel Tip #2
• Sharing the Wealth: Random Excel Tip #3

Stephen Few has long proposed his bullet chart as a replacement for gauge-type reports. Steve argues that bullet charts are more efficient (take up less space) and encode more and more accurate information than a gauge or speedometer type chart. I’m still unconvinced, especially since I’m not a fan of a visualization that requires retraining of the end consumer. I’d much rather use something that the end consumer ‘gets’ right away.

That being said, I haven’t written a visualization post in a while and just because I’m not convinced on bullet charts doesn’t mean that you aren’t and that you might not be looking for a way to create them in Excel. My version is a combo-chart using both a scatter plot and stacked horizontal bars so the order in which we add data and chart elements is important.

BTW, I am going to assume that you have a passing familiarity with Excel and its charting utilities so don’t be surprised when I outline the steps to take (insert a scatter plot) rather than give detailed instructions (select data in cells A1:B2, go to ‘Insert’, click on the Scatter Plot drop down and select the scatter line with no point markers).

Step 1: In this first step, we will add the data for the scatter plot and create the default scatter plot graph. In this example, we are using the scatter plot to set a goal line for the overall bullet chart.

So, when we create the initial scatter plot, you’ll see that the x-coordinates are ‘80′ - this is my target. In this case, I happen to be charting the performance of the Homepage Retention Rate (1-Abandonment Rate) and I am shooting for 80%. I happen to be using whole numbers for simplicity’s sake but it could just as easily be percentages. Don’t worry about the vertical alignment of the line just yet, we can adjust it - if needed - later when we are closer to the completed graph. Notice that I haven’t removed any of the chart junk yet either, that will happen in a later step.

Step 2: Now we need to add the data for the horizontal stacks and the stacks themselves. We will have 3 bars (Top, Middle, Bottom) with 4 sections each.

When working with combination charts, adding the new data points and chart types can be tricky. In the screen shot to the left, we’re looking at the completed process, I’ve excluded the intermediary step of what the data looks like when we first add it.

In order to add the stack data to chart we first select the data from all four of the stack columns and copy it. Then we select the chart and use ‘Paste Special’ to paste the data as ‘New Series’ with ‘Values ‘Y’ in columns’. This will paste our stack data in as new scatters. We then select each of the stack series and change it’s chart type to ’stacked bar’.

Ok, now that we’ve talked about how to add the stacked data to the chart, let’s talk about the data itself. In the top row, the first data point is ‘50′ - this is our floor (minimum acceptable value). The second data point for the top row is always zero. We need this second stack element for visual handling on the center stack (see the little green block in the center bar) when it is less than our minimum. Oh, I’m also not going into excruciating detail on the formulae used in the data set, they are fairly straight forward and you can take a look at them when you download the spreadsheet at the end of this post.

Step 3: This is easy, set the gap width on the bars to ‘0′ so that they are slammed up against each other.

Step 4: Time to take out the chart junk.

Remove the vertical axes, and the secondary horizontal axis.

Remove the horizontal lines.

Remove the legend.

Remove the plot edge lines

Set the scaling on the primary horizontal axis, set the axis-line to none as well as the major tick marks to none.

Step 5: Apply consistent and valuable color-coding to chart elements.

The blue stack becomes our representation of ‘Actual’ (e.g. the bullet). The background elements are encoded in shades of gray.

Up to 50% is nearly black - remember, this is our floor

From 50% to 75% is about 50% gray - this is our marginal performance section

And finally, from 75% to 100% is light gray (20%) for acceptable performance. I’ve also changed the target line to full black, it stands out nicely against both the light gray and the blue.

Step 6: Resize the chart.

So far, our chart hasn’t look much like a bullet chart has it? Well, that’s pretty simple to take care of. In this example, I’ve held the horizontal length the same but compressed the vertical height. Now we have something that very definitely is looking like a bullet chart.

Step 7: Add a text label and center align the chart to the text label.

Ok, so what I’m really doing is adding a text description, and the actual value of my metric in the two cells to the left of my bullet chart. Then, I’m massaging the bullet chart into alignment (hopefully) with the text. Notice that the value I’ve put in the label (86) doesn’t match the value in the chart, we’ll fix that in the next step. You might have also noticed that in this step, I removed the final piece of chart junk from my chart - the chart area border. Up until now, it was helpful to have the border for moving the chart around, but now that I have it correctly situated, I no longer need it.

Step 8: Synchronize label value to chart value.

Yep, you guessed, that’s a fancy way of saying set the first value of the center stack equal to the text label value (=D89). Now, I can enter a new value on the label, and the chart will update automatically.

Step 9: Obscure the chart data.

We had to jump through a lot of hoops to get to this graphic, but we probably don’t want the report consumer to be thinking about those hoops, so set the text color of your chart configuration data to ‘white’, effectively hiding it from the consumer.

Step 10: Rinse & Repeat

 Ok, so there’s my step-by-step on how to create Bullet charts.

What? You want the spreadsheet? Well, since you asked so nicely…

Download the Excel 2007 version or Download the Excel 2003 version

Avinash posted today on “Leveraging Statistical Control Limits” in web analytics as a way to cut out the noise in your KPIs and therefore being able to focus on the true outliers - data points that are truly worthy and in need of your valuable brain power.

In his post, Avinash challenged his readers to provide an Excel solution since there are no OOTB (Out Of The Box) solutions for introducing constants, statistical or otherwise, into charts - Excel tries to box us into using just vertical and horizontal axes scale controls.

Well, I can’t pass up a challenge like that right?

What do you think of this?

So what’s going on here?

1. Named Ranges - Named ranges are your friend. In this case, I’ve got named ranges for each important series of data, especially my key metric - page views per visit. That way, I can insert a new row of data and my constants (Standard Deviation, Mean, Upper Control Limit, and Lower Control Limit) will update automatically. BTW, the chart uses the named ranges as well so that it will update automatically every time a new row of data is added.
2. The chart is a combination of lines and stacked bars. The stacked bars create the gray biorhythm (normal variation) area. Line charts are used for everything else.
3. For the top 5 outliers (either upper or lower) I’m dynamically adding a data label so that I know, at a glance, the specific values for them.
4. I’ve eliminated all horizontal lines other than my own constants because they are just distracting

You can download the spreadsheet and play with it if you want.

A couple of caveats:

• Named ranges won’t do the dishes for you. In other words, when you insert a new row, you’ll need to copy down the formulae from the previous row
• Excel doesn’t always do a good job of placing data labels so the outlier labels may need to be massaged after an update
• My spreadsheet only uses 1 standard deviation. While 3 might be a six-sigma best practice, three standard deviations would have put the lower control at 0.1 and common sense tells me that it shouldn’t be possible to have an average page views per visit value lower than 1.

I’ve deliberately not gone into great detail on the inner-mechanics of the spreadsheet (not that their profound or anything) because that would make for an impossibly long post - if you have a question please post it in the comments or email me directly (civy at instantcognition.com).

More on color palettes today…

One of the problems with the previously discussed Excel color tip is that custom colors are not particularly portable. In other words, I can easily set up the custom palette on my computer but sharing that with you is a rigamorole. Instead of the color definitions being embedded in the Excel file, I’d have to give you a template file that you could import the palette from (yech!) every time you want to look at the report with my carefully chosen and designed colors.

This led me to the question of, can I store the color definitions in VBA? The answer is, no surprise, YES! I found the following snippet in a thread over on Mr. Excel.

Private Sub Workbook_Open()
Application.ScreenUpdating = False

ActiveWorkbook.Colors(35) = RGB(0, 115, 106) ‘ Teal
ActiveWorkbook.Colors(36) = RGB(255, 255, 153) ‘ Yellow
ActiveWorkbook.Colors(37) = RGB(52, 99, 175) ‘ Light Blue
ActiveWorkbook.Colors(38) = RGB(244, 154, 193) ‘ Pink
ActiveWorkbook.Colors(40) = RGB(255, 204, 153) ‘ Tan

Application.ScreenUpdating = True
End Sub

Yeah! Now my custom colors will load whenever the workbook is opened. AWESOME. Uh Oh, wait, Excel refers to each of the fifty-six colors by an index number and by the way, the index number has absolutely nothing to do with the color’s position in the palette grid (available from the format menu).

Mr. Excel to the rescue again. In this thread, I found a cool little function that lists our colors 1-56 in color coordinated cells.

Cool Little Function: (oops I had the wrong function in there orginally - sometimes I’m my worst editor)

Sub colors()
For i = 1 To 56
With Cells(i, “A”)
.Interior.ColorIndex = i
.Value = i
.HorizontalAlignment = xlCenter
.Font.Color = vbWhite
.Font.Bold = True
End With
Next i
End Sub

The only missing piece is the original RGB value. So, with a little work in Photoshop, I created the following matrix:

The number in the top right corner of each color block represents the Index number that Excel assigns to that color (see what I mean about it not really being arranged in a particularly useful way? It looks like they messed up a couple times and put some of the numbers in order).

In the lower left hand (ish) corner of each block is its RGB value.

The first five rows represent Excel’s ’standard color palette’ while the sixth and seventh rows represent the colors for chart lines and fills respectively.

Whew! Now I have a reference map for building out that first macro (I know which colors are which in the Excel palette when I change a particular index’s RGB value) and I have that Macro for making the custom color choices portable.

Are there other better solutions for making custom color palettes (in Excel) portable? Let us know how you’ve gotten around this issue

By the way, I am well aware that this tip is another rehash of information found elsewhere - but why should I have to go through another 2 or 3 hours of searching pain if I need this again and more importantly why should you?