Current fashion is about Big Data and Analytics. One related term is very popular – Excel Dashboards. Let us start understand the concept first and then explore the benefits and methods of creating useful Dashboards.
Estimated reading time 10 min
A web search about “Excel Dashboards” yields 5.7 million results!
So why am I writing this article?
There are many famous sites and experts who have spent their entire lives for this subject. There are lots of online, premium courses which teach you the mechanics of how to create dashboards. There are many tools other than Excel which create similar or even better dashboards.
The objective of this article is neither to review existing content nor to compete with it. I am trying to show the need, the available approaches. It is very easy to get too philosophical about it or too techie about it. Most content out there falls in one of these categories. I am trying to be somewhere in the middle.
The idea is to remove the jargon and show the concept in a logical manner which can then be applied practically to your work scenarios.
Creating great dashboards does not lead to growth. Using them does!
Contents
The data cycle
Someone captures data about business transactions. Someone summarizes it and makes reports. Those reports are interpreted to understand what is happening and take relevant action.
Earlier we were sending reports to each other using mails. Now there is a better way. Read these articles to understand the new way.
Part 1 covers the concept.
Part 2 shows the step by step process.
Part 3 (this article) covers Frequently Asked Questions.
How many reports you need to see?
Each person needs to see different set of reports at different points of time. Even if all the reports are generated from automated systems, correlating them to make sense out of the data is still requires human intervention.
Usually different reports arrive in different mails. Even if you use SharePoint, those reports are separate files. Each will open in a separate browser page.
Correlating information is difficult if you have to keep shifting from one piece to another. You need to see all the pieces together to get the overall picture.
That overall picture is called a dashboard.
Car dashboard – how much information is here?
Just out of curiosity, I actually made the list…
- Speed
- Fuel Level Indicator
- Engine RPM
- Engine Temperature
- Turning lights
- Day light
- High beam
- Fog light
- Rear fog light
- Gear Position
- Average mileage
- Optimal driving indicator
- Outside temperature
- Current trip distance
- Parking brake status
- Engine Status (shown at startup)
The question is – do we need all this information?
At the same time – No. At some point of time – absolutely yes.
If you put all the potentially useful information in one place – it is called a dashboard.
Let us explore how we use this information while driving the car.
Who needs it?
Answer is – only the driver. Usually yes. But not always. There are backseat drivers who also look at the speed and admonish the drivers. Someone traveling with you may notice that fuel is low. Thus, anyone who cares to look at the dashboard may benefit from it. But yes… primarily it is the driver.
What is the important meaning? If you can see the information but cannot act on it, what is the use? Therefore, although everyone sitting in the car can see all the information, practically it is useful only in the context of the driver.
What is the business linkage of this concept?
If you don’t have the power to act on the information – don’t waste time looking at it!
Conversely, if you are empowered to act on it – you will be much more effective in your work.
In real life, you will see a mismatch of this concept in many cases. Decisions makers with power to act don’t get data on time. On the other hand, people who know exactly what action to take often do not have the authority to do so.
What to look at?
Driver should look at the dashboard at the time of starting the car. Why? Because some lights come up for few seconds at the engine start time – glow for few seconds and then disappear.
Why does this happen? This is a very important step in information display and utilization. Suppose there was an engine problem warning light. That light comes on at startup, stays on for few seconds and then switches off. Does that mean there is engine trouble? No.
It was just to show you that the engine warning light is WORKING.
So what is the big deal. Do you care to know that the light is working? In any case, if the engine has no trouble – it is going to be OFF. If engine has problem it will turn ON. So what is the point in proving to you every time you start the engine that all such lights are working?
Think about it a little and then you will get the answer. You rely on these lights. If you don’t see it ON you assume everything is ok. You MUST check if it comes ON at startup. If the light itself is not working, it is going to misguide you. Therefore you must get it replaced immediately.
What did we learn in this apparently unnecessary and unrelated discussion?
Normalcy is also a state which needs to be shown – not assumed.
Don’t worry. We are just starting on the topic of dashboards. As we go along this series, things will become more apparent.
Where does the data come from?
Going back to the car dashboard – it is easy to imagine the situation. Each piece of data is originating from different source. Battery, engine, speed sensors, temperature sensors, lights, … so many origins.
But the important part is that the viewer – the driver – does not need to know where the data is coming from. It is always there – look at it when you need. Else ignore it.
Compare this with the way we look at business reports. Each report is sent to you from different people. Those people have got data from various sources. You wait to receive a report by mail from someone. That is one piece of data.
Similarly you wait for other reports to arrive from different people or departments. Finally when all arrive, you still open them separately and try to think holistically.
Car dashboard does not require you to login
The data originates from various sources, goes through various types of processing and conversion but you see it in a single place – without any active effort.
This simplicity should be achieved with business dashboards for them to be practically useful.
Car dashboard vs. business dashboard
There is one very important difference – Car Dashboard shows the status as of NOW. There is no past information.
In most business dashboards, there will need to be some historical information.
While viewing the car dashboard you cannot – and should not interact with the dashboard.
While viewing and interpreting business dashboard – you can and should interact with the information displayed.
For example, Car dashboard shows current engine temperature. You are not really interested in a chart of how it fluctuated over last 2 months or years.
But if business dashboard shows todays total sales, you surely want to compare it with earlier sales by days, weeks, months, years and so on.
So need for comparison is a feature unique to Business Dashboards – but it is unnecessary in the car dashboard.
Security – who can see the information
Car dashboard displays information only if you can start the car. Which requires a key. So the key is your security. If the car is stolen, the thief is the authorized user of the data!
Similarly, in a business dashboard – protecting it is equally important. Dashboard shows many related pieces of information together. Therefore unauthorized viewing of dashboard is potentially more dangerous than leakage of one of the reports.
Consequently, you must take precautions to ensure that only authorized people can see dashboards.
That is not all. There is jurisdiction to consider as well. For example, VP Sales can see all the data. But Regional Sales Manager should see only data originating from her region.
Although we will see this aspect in more detail later, it is important to mention that Excel and SharePoint are NOT good at filtering data based upon the currently logged on person. This is best done using a more sophisticated BI tool like SQL Server Analysis Services (and many more enterprise class tools).
Correlation with norms, gut feel, regulations
Consider the car dashboard. When do you look at the speedometer?
Only when you are worried about exceeding the speed limit.
Does the car know if the current spot where you are driving has a speed limit?
Obviously not.
That part you have to know – that is the external information YOU must have to make use of the internal information from the dashboard.
Now, with GPS devices becoming cheaper and commonplace, this does happen automatically and the GPS device will warn you if you are exceeding locally established limits. But remember, the GPS device itself needs updates from external source. Besides, the car may have GPS and Speedometer. But even today, those two are NOT integrated. GPS device gets your speed information interpolated from the GPS satellite data – NOT a direct feed from the speed sensors in the car itself. It is an excruciatingly roundabout way – but it works because the GPS signal is free!
That is what I mean by norms or regulations. In business context as well, a great dashboard is useless unless you know the external scenario, regulations, limits, constraints and lots of other information – which will never be shown in the dashboard itself.
Everything is contextual. You may obey speed limits very carefully. But if suppose a war broke out and you were running away from the battle zone in your car to save your family – you are only going to look at the rear view mirror and the fuel gauge. Nothing else matters.
In short, everything is there for you. But what to look at, how to interpret it and what to do with it is entirely up to you.
To summarize, like so many other things in technology, YOU must be in command.
Do we really need those needles?
Look at the middle portion of the dashboard – it shows the current data – like temperature, gear position, etc.
But look at the RPM meter and the speed meter. At a given point of time, the current speed or RPM is just ONE number. But it is showing it as a gauge – to show the RANGE.
Do you realize that it is occupying 80% of the available space just to show two simple numbers? Why does this happen? Because we are used to that kind of display. Newer cars do display speed and RPM only as numbers and that has simplified their dashboards.
But still, due to familiarity and nostalgia – most cars do waste precious amount of space for just two numbers!
We have to avoid such temptations while designing Business Dashboards – because usually there is no precedence or familiarity involved. We have to strive to minimize wastage of space.
What next?
I hope you have begun to understand the REAL meaning of a dashboard. This is a series of articles. In the next article, we will see how this concept can be applied to Excel reports.
