People often write to me –
“Chandeep, What should I do to become really good at creating Dashboards ?”
These kind of related questions are arriving my Inbox more often than others, so I thought of putting together a well structured post that talks about –
- What it takes to create an awesome Dashboard – Process
- How to do you become better at it with time – Skills
Dashboard creation is a strategic process. You carefully have to carry out the steps in a particular order to get it right. If you bounce around and do random things, you’ll find yourself doing multiple iterations to get it right or there is a good chance that you won’t get it right at all.
The process isn’t complicated, it is fairly straight forward
Creating a Dashboard means solving 2 problems..
Problem #1 – What is the Content of the Dashboard ? I call this the Pre-Excel Phase. You’ll find yourself doing things like..
- Understand the needs of the business
- Planning what metrics are relevant
- Checking data adequacy
- Mocking a rough Dashboard
Problem #2 – How are you going to present it ? Everything here needs you to work problems out in Excel.
- Doing the math, running calculations
- Modeling the data
- Preparing the right charts and visualizations etc..
The number one mistake that I see most people do is that they jump to the visualization part even before they are completely sorted with the content of the Dashboard. It doesn’t work like that (at-least it has never worked for me)
What Skills do you need ?
You need 3 core skills to be able to get better at creating awesome dashboards
Skill #1 – Knowledge of the Business / Process – Our first stage is Dashboard planning (the “What Content” problem). You’ll only be able to understand what metrics are important for the business when you understand the business or the process thoroughly.
Skill #2 – Advanced Excel Skills – “Advanced Excel” is a very abused term (at-least in India).
Just because one doesn’t know a certain excel technique, does not make the technique advanced. Also “advanced” is a very relative term. The Dashboards that I make today would certainly have been very advanced for me a 2 years ago.
What I mean by “Advanced Excel” is that the user should be able to apply Excel (Formulas, VBA, Pivots or other tools) to model and crunch data and find answers to business problems
Skill #3 – Visualization Skills – Some people are pretty good with Excel. They can write all the complex formulas, build complex logic, write VB code or even drill down into Pivots. But are terrible with Visualization and Charting (like my brother)
Charting and Visualizing skills are extremely important because
- It makes it easier to summarize, understand and infer data
- It can bring out the key metrics, trends, alarming or exciting numbers visually
- It is more engaging rather than dumping voluminous data on the screen
How to hone these Skills ?
Business or the Process Knowledge – Unfortunately there is no course that can teach you this skill. The more you’ll learn about the business or the process the more you’ll stay into it. But I would like to make a couple of suggestions that have personally helped me in my career
- Learning the nitty gritties of the business/process takes time. You are going to make a ton of mistakes at the beginning and that is okay! Be persistent. Be Patient
- Listen and observe what matters to the management (or your boss). What kind of numbers / metrics or calculations are often performed
Your business understanding is directly proportional to the relevance of the questions you’ll seek to answer from the data
Advanced Excel Skills – To become an advanced user of Excel. Here are the few things that you must master
- Learn to embrace to complexity. Excel is just not about Shortcuts
- Learn the Key Formulas – IF, AND, VLOOKUP, INDEX, MATCH, OFFSET, SUMIF, COUNTIF, CHOOSE. These are not the only formulas but I have found myself using these more often than others
- Learn Pivots thoroughly
- Learn from Excel Bloggers – There are plenty of them out there
- Join a Course – I run a very comprehensive course on Excel that can help you become a pretty solid user of Excel
Visualization Skills – If you suck at Charts you’ll not able to come up with a sophisticated and dynamic visualizations
- First get good at Excel Charts
- You should often tear apart other’s Dashboards to see how they are made. This will help you with 2 things
- You’ll learn how complex visualizations are thought of and created in Excel
- Plus it also helps you to build a personal library which you can use in your own Dashboards
- Here are some my advanced visualizations –
- Other Data Visualization Sites – Roberto Mensa, Excel Charts, Charley Kyd, Chandoo
Personally speaking, I cannot say at any point in time that I have completely mastered all these skills. With efforts put in, you just keep getting better with time 🙂
If you are video lover, here is a short video presentation. This presentation is part of my new course on Business Intelligence Dashboards