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.

Why I don’t like RUN buttons

When I first started this blog, I thought to myself: VBA is wonderful and amazing—there’s nothing you can’t do with it. I still love VBA, don’t get me wrong, but my relationship with the scripting language has changed considerably over the last several years. On a recent Excel.TV episode, Rick rightly pointed out the irony in my relationship with VBA solutions. “VBA appears in the title of you blog!” he said. 

My issue isn’t with VBA itself, but rather with how we use it. This won’t be a cliché article complaining about how old VBA is or why I think another language is so much better. Blech. But I do want to discuss how I think it should be located in development and provide you some food for thought in this vein.

image

The “RUN” button perhaps best encapsulates my relationship with VBA. You know what the RUN button is because you’ve created it before. It’s the button that updates a worksheet’s values, creates a new report, inputs data into a model—and so on. It’s the button you press to begin automation. In a sense, it’s everything I used to think VBA was about.

But having done this for many years, I now hate RUN buttons. To some extent, they can be unavoidable given the underlying nature of the problem. But they are often more avoidable than we think. As followers of my blog know, I am keen to place as much functionality on the spreadsheet as possible. If I can do it with Excel and formulas, then I avoid needless VBA. This makes for tighter faster work, that’s easily scalable. (In my book, I call the concept “reusable components.”)

And RUN buttons are often filled with needless VBA. We can use formulas to automatically update data values so that what we’re looking at is always live data. Form controls can help us with this. RUN buttons are often used to create copies of new report tabs, where the layout of a template tab is copied, filled with the latest information, and then displayed. But wouldn’t one display tab that could use formulas or Power Pivot suffice? The RUN button symbolizes processes that are far more complex than they need be.

Look, I’m just complaining about a symbol of a true underlying problem. We make things more complex than they need to be. Our work should never be more complex than the underlying model.

But agree or disagree, we should think more about how we develop. Like I said, RUN buttons are sometimes unavoidable. But if you can limit the amount of code in your work, replacing iterative volatile actions with faster formulas, that’s something to shoot for. At least I think so.

Excel for Business Workshop in West Chester, Ohio

COURSE DESCRIPTION

Have you mastered VLOOKUP and INDEX? Have you not met a Pivot Table you couldn’t best? Then this is the class for you.

With this advanced course, I’ll teach you powerful techniques to take your skills to the next level. This is a full-day hands-on workshop, where we’ll use Excel’s features and functions in novel and innovative ways to create products that rival the large, business intelligence vendors. I’ll show you how to create data visualizations and interactivity not thought possible with Excel. And I’ll show you how to make them fast and efficient, so you can use model results without waiting.

YOU’LL LEARN HOW TO

  • Create fast and efficient spreadsheet models.
  • Use Excel’s boolean functions to select, filter, and aggregate data on the fly.
  • Avoid common spreadsheet development pitfalls
  • Apply techniques in your dashboard, decision model, and data visualization tools

WHAT YOU’LL GET

All attendees will receive an absotlutely free copy of my book, Advanced Excel Essentials

NOTES

  • Following along on your laptop is encouraged but necessary. If you want to follow along, you’ll need Excel 2007 or above installed on a Windows laptop.
  • This course qualifies for CPE credits. You will receive a certificate upon completion.
  • Group discounts are available, please call 937-329-9612 to inquire.

COURSE COST

The cost of the class is $197 per person. This workshop has a limited number of seats which fill up quickly, so you should sign up as soon as possible. Until the end of January, use discount code EARLYBIRD to receive a 35% discount.

CANCELLATION POLICY

You may receive a 50% refund if you cancel a week before the event. Beyond that, refunds will be issued at our sole discretion. If inclimate weather causes the event to be canceled by the venue, we will let you know as soon as possible and change the event date.

QUESTIONS?

Contact Jordan Goldmeier by calling 937-329-9612 or by email jordan@cambiafactor.com.

Until the end of January, use discount code EARLYBIRD to receive a 35% discount.

Happy Holidays 2014

Hello everyone. I’m a little late getting out my holiday card. Unfortunately, it’s not as polished as last year’s, which had falling snow. So, I’m sending you this card, which isn’t perfect. Just pretend it’s something your kid made with glued macaroni.

Move your mouse around like a flashlight to reveal a hidden message. See below:

image

If you look at the backend pieces, you’ll see some extra fat that could have been trimmed. But, for now, I’m happy with it!

Keep on calculat’n’, friends.

Download the Holiday Card!

(It’s hosted on Google Drive so you might have to hit a ‘Download’ button.)