The ever creative Chandoo has just release a new offering in his treasure trove of Excel goodies.
If you don’t know Chandoo, he’s an Excel MVP, father of twins, lover of tea with milk, and owner of one of the most popular Excel blogs today.
I call him can-do Chandoo. No matter what crazy idea you throw at him, he has a knack of making it work.
Need an example? How about an Excel Podcast?
A long time ago, I thought about doing an Excel podcast, but I figured talking about Excel would be too boring without seeing the tips and tricks. Yet, can-do Chandoo made it work. He’s on his 25th episode and seems to be going strong. Like that brown kid on a boat with a tiger, he makes any challenge look easy.
Well, today Chandoo beats me at yet another idea I’ve been toying with for years: ready-to-use Dashboard Templates.
With his templates, you simply enter your data, set up few calculation options, decide how your dashboard should look, and poof – like magic, you have a dashboard that synthesizes your data into easy to consume insights.
I spent the better part of 3 hours dissecting his template and here are my thoughts:
- Overall – very impressive. It does indeed do what you think it should do. I jammed some of my data into the dashboard, selected the view I wanted and it produced that view.
- Having this template is like having an advanced mock-up tool. With this template, I can mock up all kinds of views for my clients. I may not use this as my final dashboard, but this template definitely saves loads of time putting together a straw-man to show to my clients.
- No VBA! Given the very dynamic nature of the dashboard, it’s hard to believe there is no VBA in it. Chandoo uses a whole host of tricks from Slicers to Named Ranges to keep the final dashboard VBA free. I think this is especially handy if your organization uses Office 365 or Excel Services on SharePoint. Why? You can publish your final dashboard to the web!
In fact, there are so many tricks used in this dashboard, you can learn all kinds of advanced techniques and best practices just by reverse-engineering the formulas and named ranges. I’ll give you an example. In the set up page, you can define the number formatting for each of your metrics. This means you can change the format of a metric to (let’s say) a percent with two decimal places. This immediately changes the dashboard to show the selected format.
My question was how the heck did he change the format based on a dropdown selection without VBA? The answer is he uses the TEXT Function to dynamically change formatting. This is a clever trick that I’ll be using in my dashboard models. Small tricks like these make Chandoo’s template worth more than the resulting dashboards themselves.
Ok. I’m starting to feel a little dirty.
Jeez – it sounds like I’m ready to take a shower with the guy.
The bottom line is that this new offering from Chandoo is pretty damned cool, no matter who you are.
Take a moment this week check it out.