Share [Social9_Share]

How to improve your Excel Dashboard Skills

How to Improve your Excel Dashboard Skills

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..

  1. Understand the needs of the business
  2. Planning what metrics are relevant
  3. Checking data adequacy
  4. Mocking a rough Dashboard

 

Problem #2 – How are you going to present it ? Everything here needs you to work problems out in Excel.

  1. Doing the math, running calculations
  2. Modeling the data
  3. 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

  1. It makes it easier to summarize, understand and infer data
  2. It can bring out the key metrics, trends, alarming or exciting numbers visually
  3. 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

  1. 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
  2. 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

  1. Learn to embrace to complexity. Excel is just not about Shortcuts
  2. 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
  3. Learn Pivots thoroughly
  4. Learn from Excel Bloggers – There are plenty of them out there
  5. 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

  1. First get good at Excel Charts
  2. You should often tear apart other’s Dashboards to see how they are made. This will help you with 2 things
    1. You’ll learn how complex visualizations are thought of and created in Excel
    2. Plus it also helps you to build a personal library which you can use in your own Dashboards
  3. Here are some my advanced visualizations –
  4. 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

 

 

  • GraH

    Hey Chandeep,
    You could have mentioned skill #4: have an eye for overall design. I kind of envy your ability to make the most of the dashboard real estate and keep a consistent theme.
    Though it is part of visualization, I think of it as the extra layer beyond “charting”, being it sparklines, in-cell charts with formulas or CF, or real charts.
    I’m liking the recent posts your sharing. Thx.

Chandeep Chhabra

Popular Posts

© Goodly