How do CEOs and leaders of large organizations maintain an effective grasp of business progress? Chances are, they are using similar techniques to those described below. A business dashboard is a one-page report that summarizes the key performance indicators that a business leader may find important in understanding the current situation and trends. However, dashboards are useful throughout an entire organization, and even in personal life situations too. This article will teach you the basics of dashboardology — the science of summing it up.

Every entity has goals it is trying to achieve. It’s important to revisit the second letter of the acronym, SMART goals, for which the M stands for measurable. Each goal needs to be measurable. If it is, success is a much more likely outcome. It’s very often, that people confuse tasks with goals, and try to make their measurement as a percentage of task completion. Unfortunately, experience dictates that task completion is not equivalent to business success. Try to tie each project or task into a metric that is related to the success of your business.

Typically, when I’m faced with putting together a dashboard, I’m dealing with web or sales data that is collected into a relational database. My dashboards tend to show time-series trends (which are usually best shown in simple line graphs), and top 5 lists (like top 5 clients), or worst 5 lists (worst 5 ad campaigns). I’m able to work with IT to export this data, or even better to have it presented in a visually digestible manner.

My family recently doubled in size with the birth of our twins. Taking care of twins is a lot of work. Per our hospital’s suggestion, we keep a log of each of their feeding intake, diaper changes, vitamins, and any other items of interest. However, to get a sense of how much they are eating and growing, it takes quite a bit of flipping through the logs. Even then, it is not easy to spot any trend, because each meal is logged, and trends are usually seen in aggregate (ie, daily intake growth, since meal size varies a lot throughout the day). Furthermore, when we visit the doctor, the data is not in a format that’s easy for him to digest.

First Step: Mockup The Dashboard
I decided I wanted to look at their feed intake as the primary measurement on the dashboard. I also wanted to highlight other measures, such as weight, length, and head circumference growth. However, those measurements aren’t taken with any frequency. I also reserved some sections with some important information, like the doctor’s phone number, and their medical record numbers.

Second Step: Creating your Database
I decided to use Microsoft Excel to make the dashboard, and it also made sense to use it as the database. I setup a worksheet as the “data sheet” with days across the top row, and the measures down the first column. Then, I entered the data from our log sheets. It took about 20 minutes to enter all of the data in, and it takes a few minutes every few days whenever I update it.

Third Step: Charting Your Data
An important part of this step, is also cleaning your data (adjusting the scale or converting the units). For example, we measure per-meal intake in milliliters, but I wanted the daily graph to be in ounces (which makes more sense in terms of the scale). Another part of cleaning the data is deciding how to handle absent data. For example, we only have their weight information from the days they have visited the doctor. Microsoft Excel can handle charting this cleanly by leaving the days we don’t have the data for as =NA().

Fourth Step: Making Your Dashboard Visually Pleasing
For this step, I defer to the experts such as Stephen Few and Edward Tufte. Use visual elements to summarize your data, but don’t over-do it with 3D graphs. Unfortunately, Microsoft Excel makes it quite challenging to make a very visually pleasing dashboard, or so I thought, until I came across this e-book by Charley Kyd which explained some critical techniques for layout, formatting and data selection.

The result (this is only a partial snapshot because their is personal information on it):

Snippet of the dashboard

This is what I read to learn how to make my dashboard in Excel. If you’re like me, you don’t want to spend your valuable time digging through data to find trends and meaning. Invest a few hours up front, and save countless hours in the future by setting up your dashboard today.