Course sections

Introduction to Microsoft Excel, Lecture 9

Concept of dashboard

Excel dashboards

An Excel dashboard is one pager (mostly, but not always necessary) that helps managers and business leaders in tracking key KPIs or metrics and take a decision based on it. It contains charts/tables/views that are backed by data. A dashboard is often called a report, however, not all reports are dashboards.

How to Create an Excel Dashboard

Creating an Excel Dashboard is a multi-step process and there are some key things you need to keep in mind when creating it. Even before you launch Excel, you need to be clear about the objectives of the dashboard. For example, if you’re creating a KPI dashboard to track financial KPIs of a company, your objective would be to show the comparison of the current period with the past period(s). Similarly, if you’re creating a dashboard for Human Resources department to track the employee training, then the objective would be to show how many employees have been trained and how many needs to be trained to reach the target.

Things to Do Before You Even Start Creating an Excel Dashboard

A lot of people start working on the dashboard as soon as they get their hands on the data.

And in most cases, they bring upon them the misery of reworking on the dashboard as the client/stakeholder objectives are not met.

Once you have the data, you need to clean it and standardize it.

For example, you may need to get rid of leading, trailing, or double spaces, find and remove duplicates, remove blanks and errors, and so on.

In some cases, you may even need to restructure data (for example say you need to create a Pivot table).

Using Excel Charts to Visualize Data in an Excel Dashboard

Charts not only make your Excel dashboard visually appealing, but also make it easy to consume and interpret.

Here are some tips while using charts in an Excel Dashboard:

  • Select the right Chart:Excel gives you a lot of charting options and you need to use the right chart. For example, if you have to show a trend, you need to use a line chart, but if you want to highlight the actual values, bar/column chart could be a right choice. While a lot of experts advise against using a pie chart, I would suggest you use your discretion. If your audience is used to seeing pie charts, you may as well use these.
  • Use combination charts:I highly recommend using combination charts as these allow the user to compare values and draw meaning insights. For example, you can show the sales figure as a column chart and growth as a line chart.
  • Use dynamic charts:If you want to allow the user to make selections and want the chart to update with it, use dynamic charts. Now a dynamic chart is nothing but a regular chart whose data updates in the back-end when you make selections.
  • Use Sparklines to make your data more meaningful: If you have a lot of data in your dashboard/report, you can consider using Sparklines to make it visual. A sparkline is a tiny chart that resides in a cell and can be created using a data set. These are useful when you want to show a trend over time and at the same time save space on your dashboard. Here is a detailed guide on creating and using Sparklines in Excel.
  • Use contrasting colors to highlight data: This is a generic charting tip where you should highlight data in a chart so it’s easy to understand. For example, if you have sales data, you can highlight the year with lowest sales value in red.

Excel Dashboards Do’s and Don’ts

Let’s first start with the Dont’s!

Here are some of the things you should avoid while creating an Excel dashboard. Again, these would vary based on your project and stakeholder but are valid in most of the cases.

  • Don’t Clutter Your Dashboards:Just because you have data and charts doesn’t mean it should go in your dashboard. Remember the objective of the dashboard is to help identify a problem or aid in taking decisions. So keep it relevant and remove everything that doesn’t belong there. I often ask myself if something is just good to have to absolutely must have. The I go ahead and remove all the good-to-haves.
  • Don’t use volatile formulas:As it will slow down the calculations.
  • Don’t keep extra data in your workbook:If you need that data, create a copy of the dashboard and keep it as the backup.

Now let’s have a look at some Do’s (or best practices)

  • Numbering your Charts/Section:Your dashboard is not just a random set of charts and data points. Instead, it’s a story where one thing leads to the other. You need to make sure your audience follow the steps in the right order, and therefore it’s best to number these. While you may be able to guide them when you’re presenting live, it’s a great help when someone uses your dashboard at a later stage or takes a print out of it.
  • Restrict Movement in the dashboard area:Hide all rows/columns to make sure the user doesn’t accidentally scroll away.
  • Freeze Important rows/column:Use freeze panes when you want some rows/columns to be always visible on the dashboard.
  • Make Shapes/Charts Stick:Make sure your shapes/charts or interactive controls don’t hide or resize when someone hides/resizes the cells.
  • Provide a User Guide:If you have a complex dashboard, it’s a good idea to create a separate worksheet and highlight the steps. It will help people use your dashboard even when you’re not there.
  • Save Space with Combination Charts:Use combination charts (such as bullet charts, thermometer charts, and actual vs target charts) to save your worksheet space.
  • Use Symbols & Conditional Formatting:Use symbols (such as up/down arrows or check-mark/cross-mark) and conditional formatting to add a layer of analysis to your dashboard (but don’t over-do it either).

 

WhatsApp chat