Best practices for building automated dashboards in Excel

In a prior article, I introduced you to dashboards, what they are and why Excel is still relevant when building automated dashboards.

Many a time, I’ve seen people jump into building dashboards without necessarily setting the right foundations. This often results in generic and not-so-useful outputs.

Remember what happens to a house built on a weak or bad foundation? I’m sure you don’t want that.

It is in this light that we’ve put up some considerations/best practices that you need to keep top-of-mind before building your dashboards at all to make the process even easier for you.

Here are 4 important processes you shouldn’t skip when building your dashboard.

1. Define the goals/purpose of the dashboards (WHY)

Always start with the end in mind. Start by answering the question, ”what do I want to achieve with this dashboard?”

When you and other stakeholders are clear on the goal of the dashboards, you can determine what Key Performance Indicators (KPIs) you need to measure.

You can determine what type of data source you need as input, the number of times you need your metrics to be tracked amongst other things.

2. Question your data source (WHAT)

Once you have defined the purpose of the dashboard and the metrics you want to measure, it is easy to determine which data source is best for your dashboard.

Here are a few questions to ask:

  • Is the data from the operations or marketing department?
  • Which department currently measures the metrics you want to track?
  • What formats does your data come in? e.g., excel, word, pdf, etc.

You may also want to confirm how frequently the data source is updated.

3. Who will utilize the Dashboard? (WHO)

This is essential because when you know who will be interacting with your dashboard daily, you can design the interface in a way that either captures all the relevant details or creates a more detailed view at every point in time.

It is also useful to know what software version (e.g., Excel) and device type (e.g., windows, mac, etc) your dashboard user has. Some certain functions might be missing in certain devices, and this may cause problems along the way.

4. How often will your dashboard be updated? (WHEN)

If your dashboard needs to be updated frequently due to the nature of the metrics you are tracking, then an automated process is most suitable.

Here, you can set up your dashboard to pull data from specific sources automatically and update itself using your specified query.

In a case where the dashboard is only used periodically, then a manual process of updating data can suffice.

This is an inexhaustive list of things to consider before building your dashboard. However, I believe if you spend some time getting these right prior to building your dashboard, you will be reducing your chances of committing costly errors.

Also, you will minimize the occurrences where dashboards need to be re-done or adjusted.

Learn how to build automated dashboards in excel

Building beautiful, automated dashboards in excel doesn’t have to be a time-wasting task. We’ve designed our ‘How to Build Automated Dashboard Course’ to help you learn all the basics in 1 hour. Enroll here now

Leave a comment