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?

Or, boy I am I oblivious.

Eric Peterson lobbed this my way a week ago and I just noticed it a few days ago (I’ve been hard at work on a response).

His basic question, if I may restate it, is “What do I think about using a ‘tachometer‘ to display KPI data?”

The short answer is, I love to do this - when it’s practical.

If you work in Excel, it is often not practical because the level of initial work and maintenence needed to operate a custom chart element like this violates the efficiency rule (remember, that in order for reports to be truly effective, they need to be easily repeatable so that they can be delivered efficiently and on time). In other words, I’m lazy and it is, generally speaking, too much work for not enough return.

That being said, I’ve spent the past couple of days approximating the dashboard that Eric refers to (via Dashboard Spy and courtesy of Subaru) in Excel, to see if it could be done - with the proper wherewithal.

Here’s a screen grab of the finished product:

Ok, so it can be done.

Here’s a list of resources you’ll need to pull it off:

• Andy Pope’s Gauge Work Around
• John Peltier’s Speedometer Work Around
• Apps Pro X/Y Chart Labeler
• Excel Camera Tool

Scared yet?

Ok, here’s my file for the above picture, you’re welcome to try it out (no commercial use, please), but I make no warranty as to the accuracy or stability of it.

Long story short, custom dashboard elements like the above are cool, sexy and can effectively communicate important information such as KPIs, but hopefully you have a better platform than Excel to create them in (this is where something like Xcelsius becomes helpful).

It’s not that mysterious…

I was talking with some folks the other day and started discussing the importance of separating (or abstracting) the data in a report from the presentation of data in a report and someone asked for an example.

First, let me describe what I mean by abstracting the data from the presentation. Since I’m a web guy, I’ll give you an example via web publishing. Take this blog for instance, it is run on wordpress and at a grossly understated level, wordpress does for blogs what I meant in terms of report design.

All of the data for this site, this text, images and file locations, etc. are stored in a database (mysql) and then there are templates that query the database for the information that they need to present the data (information) in the way that I have designed it to be presented. In this situation the website data is abstracted from the presentation of said data. Contrast this with a more traditional (ok, antiquated) site that is hand-crafted, page by page, and all of the data is hard-coded into the presentation - which one do you think is more efficient?

Similarly, you can design a report where all of the data is hard-coded into the part/piece/sheet that users are supposed to look at or the data can be stored in a different location and called (or queried) into the presentation template.

There are a couple of big benefits to approaching report design this way.

1. Structuring data for manipulation and collation is often counter-productive to understanding. In other words, setting up the data so that I can easily run calculations and transforms on it is pretty much setting it up as that classic table of raw data that nobody but it’s mother can love and use. And since report design is all about providing understanding, it’s often very useful to keep the data in a seperate location from its presentation
2. Hard-coded data is hard to update. Since reports aren’t truly effective unless you can produce them easily and efficiently on schedule, hard-coding the data into the report presentation always makes that harder. So, if the data is called or queried into the presentation layer and we get our data from a refreshable query we have solved the deliverability issue - it’s now easy to produce the report on time.

Putting this strategy into an Excel context

This is very simple. In most cases, I’ll have a worksheet called (imaginatively enough) ‘Data’ where I store all of the data that I need for the report and then I’ll have one or more worksheets that provide the presentation of the data (e.g. the information and insight that the data provides).

The simplest way to call or reference the data in the data sheet is a simple reference to the datum’s location in the data sheet. There are at least two easy ways to do this.

1. Copy & Paste: Copy the cell in the data worksheet that has the datum I want and then using Paste Special –> Paste Link paste it into my presentation worksheet
2. Write the formula. The above task essentially writes a formula that says ‘this cell equals that cell’. So, I can write the formula in myself. So, let’s say that the datum I want is in cell A1 in the data worksheet. Back on the presentation worksheet in cell B2 I can write the following formula:

=Data!A1

Now, whatever datum is in Data A1 will always show up in Presentation B2

That’s the easy way, but also the most inflexible. I’m going to save detailed explanations and examples for other posts, but if you want to read ahead, using functions like:

• vlookup
• lookup
• hlookup
• offset
• indirect
• named ranges (not a function per se, but used in combination with functions and charts - we can start to get super fancy)

and maybe a couple of others provide you with a great deal of flexibility and you can create quite dynamic and robust report interfaces using them.

The guys over at Juice Analytics are at it again.

It’s another one of those ‘doh!’ moments where I say: “Why didn’t I think of that?” Basically, using a simple function in Excel (Rept, aka repeat) which allows you to repeat any piece of text N number of times, you can create simple, in-cell horizontal bar charts. I’ll let you go read their post as they explain it quite well and thoroughly.

All I have to say is, no need to wait for Excel 2007 and their less than accurate in-cell gradients - you can be doing this right now and it’s not only a better solution but also is extremely reminiscent of a sparkline.

Data tables often get a bad rap in the data visualization world. I think this is because your default table whether in Excel or some other tool is amazingly boring and can do a great job of turning off the brain of you report users. However, it’s important to note that even the gurus like Edward Tufte and Stephen Few find visualization solutions with tables. Here’s a perfect example, illustrated in Perceptual Edge’s (Stephen Few) Gallery.

In it, Mr. Few takes a poorly designed polar plot and organizes the data succinctly into a table that would allow the end user to quickly and easily make the appropriate comparison.

Here are a few of the reasons I think people think tables don’t work or tables fail:

1. Reliance on the default appearance from the application
2. They’re not ‘cool’, ’sexy’, ’slick’, or like charts, graphs, and maps are
3. Table junk - just like chart junk, but in a table and in either venue the junk can ruin it
4. Overuse of either horizontal gridlines, vertical gridlines or both
5. Tables have two masters: visualization (design) rules and text (reading) rules because they are text-heavy users will, in part, have an expectation of reading like they would read a book, article, paper, etc

I happen to use tables all the time in my work so here are some of my tips for designing tables in Excel

• Don’t rely on the default gridlines. Once you have your data organized in the table, go to Tools > Options > View and take the check mark of ‘gridlines’
• Always have column headers and row headers (i.e. describe what the metric is labelled correctly, typically in the column header although your mileage may very, and what is being measured is in the row header)
• DO NOT justify the text. If the text is all justified, this diffuses the focus and makes it difficult for users to pay attention - align left, center or right as appropriate. In other words, ragged-edge text is easier to read
• Clearly seperate row and column headers from the data - this can be done using borders, cell color or both - but be careful about it, less is more
• Use borders sparingly, especially horizontal borders. Anything that over-enforces the regularness of the table should be avoided - most users only need a hint of structure in order for them to internalize it, this is the same rule we apply in chart design when removing or de-emphazing axis gridlines
• Use an appropriate sized font - probably 10pt or higher, but it depends
• Whatever you do, make sure that the focus is on the data, if users are paying more attention to the row and column headers, to the font choices, to the gridlines they aren’t getting what’s important. Give your draft table to a buddy at work and ask them if they can read it easily, it’s a great test to see if you are on the right track.