Long time no see

It’s been a long time since you last heard from me. Several things have happened in my life. Well, first, I had to get a job. My consulting business was doing well, and I was actually pretty good at it (at least, I thought so). But circumstances unforeseen changed what I had to do next; and lo, I had to get a job, you know, a real job. So now I’m working for EY (Ernst & Young before 2013) doing talent analytics.

Also, you know, at some point in time I’m required to finished Dashboards for Excel. So instead of writing for this blog, I’ve been working hard to finish what I started. It’s a long, long book now, but progress has been made and it’s almost complete. The manuscript is in and I’m now just answering editorial comments. I’m so close to finishing this book, I can taste it. And looking back over these three years, I’m surprised to even be able to say that. At several points in the writing process the task seemed so great I wouldn’t finish. But I am finishing now, and finally, I will have my life back. :)

There are more books I want to write. But if I tell you I’m thinking of writing another book at any point over the next year, please someone punch me right in the face.

You should expect me to start posting more regularly here soon.

Thank you all for your support of me and this blog, your patience, and your passion for Excel.

2015-02-16 15_39_12-Microsoft Excel - Book1

Advanced Excel for Business – less than two weeks left to sign up!

2015-02-16 15_39_12-Microsoft Excel - Book1

If you’re in the Ohio Miami Valley, then don’t miss my Advanced Excel for Business workshop! There are still spots available, so don’t miss out. Remember, aside from the awesome skills you’ll learn, you’ll also get a free copy of Advanced Excel Essentials and a catered lunch. What could be better?

Click here to sign up now! 

Corporate discounts are available. If you have questions, feel free to call me at 937-329-9612 or you can email me at jordan (at) cambiafactor.com.

Looking forward to seeing you there.

featured image1

PASS Business Analytics & Free Preview Session Webinar

So there’s been a lot of anticipation about this year’s PASS Business Analytics conference in Santa Clara, California. There will be a veritable Excel “dream team” (there words, not mine) presenting at this years conferences including yours truly, Rick, Oz, Rob Collie, Chandoo and more.

Ahead of this year’s presentation, I’ve provided a preview webinar called Creating Outstanding Spreadsheet Models that will also be presented at the conference. Thanks to the PASS team for encouraging me to release this work early.

I used a new microphone for this recording, and it appears the volume came across fairly low on my end. So crank your speakers when I jump in.

Colors and Excel

The theme colors for Excel 2013 are probably the best scheme to date.

However, sometimes I’m looking for another palette for my work. For instance, the colors used in this picture of a decisions support system developed in Advanced Excel Essentials are from another palette.

excel-dashboard-data-vis

For these colors, I use a terrific tool colored Color Brewer, which you can find at ColorBrewer2.org. Here’s a screen shot of Color Brewer.

image

With this tool, you can specify how many data classes you have. For instance, in the first figure, you can see I have six data classes. Using Color Brewer, I can also set the color-relationship between each data class. For instance, a sequential nature is good for showing ordinal data that might increase or decrease. The diverging nature is good for when data diverges from some center or mean; for instance, when we want to show information above or below some average. Qualitative is good for showing categorical data that is completely independent. You can play around with the color settings until you find the palette that suits your taste.

Just a quick aside: the colors in the first figure don’t represent sequential differences in intensity. However, varying the same color intensity across variables still helps to express an underlying relationship. Moreover, the qualitative scheme would have appeared too busy and too visually distressing. It’s OK to take some creative license.

When you have the color scheme you’re happy with, there are a few ways to export this information into Excel. I’m sure this could be automated, but I simply go for displaying the RGB color information…

image 

…then manually typing in each color.

image

The new colors then appear in the my Recent Colors section of the fill tool. More colors would appear had I typed them all in. I kept it simple for this example.

image

I realize there’s probably a better way to do this, but these days I feel so old, preferring these manual exercises over automation. Indeed, these color additions are only saved locally to the file itself—they aren’t saved Excel as a new theme. It would probably be smart of me to create a theme for them. But then I will tell you I’ve probably never created a new theme in my life, save for those canned Excel courses I was forced to sit through years ago! At this point, my avoidance of themes might be pathological! But I remember getting only a little mileage out of them anyway—and they always seems to screw up, as I recall. Perhaps times have changed? Maybe one of you smarter folks can give me your thoughts on themes.

What has changed very little however is good data visualization practice. Color Brewer can help you use colors that align with data visualization best practices. In particular, two features are useful when creating work for others.

image

Color blindness in Men is fairly common. Colorblind safe colors will still allow for distinct variation among the colorblind. Photocopy safe and print friendly do the same for different mediums. In particular, these features were helpful in the development of my book, which uses monochromatic colors in print. At the same time, the download files themselves are in full color.