How to make an Excel Dashboard

One of the main reasons people love Excel so much is its ability to create Dashboards easily. A dashboard is a visual summary of how a business is doing – and as it suggests they consist of a number of graphs. It enables business owners and Senior decision makers to get a heads up on how a business is performing and identify any potential problems as quickly as possible.

Below are some guidelines on how to create a good excel dashboard.

  1. What are the key metrics that the excel dashboard trying to show ?

The graphs in a excel dashboard summarise graphically the value of Key Metrics  or Key Performance Indicators (KPIs). Here are some Key Performance indicators that are often measured by companies:-

  • Value of Sales in period
  • Number of enquiries received in period
  • No of staff in business in period
  • Number of customer service tickets raised / closed in period

Each business will have its own KPIs – and hence the first thing to is to decide what KPIs should be measured.

Note :-

  • Don’t have too many KPIs in one excel dashboard. I would limit to at most 6 KPIs. If you need a lot more than 6, then I would suggest breaking down your dashboard into separate mini dashboards on separate worksheets within one workbook. e.g one for HR related metrics another sheet for Sales.
  • If you do need to have multiple dashboards, then keep all the data within one workbook. I always avoid linking Excel workbooks as there is a danger of them not all being kept in synchronisation.
  • Most companies use monthly for a time frame. However, some organisations may have a separate dashboard showing either yearly for previous years or cumulative values – for the year to date. This is because Senior Decision makers like to look for trends e.g how are sales this period compared to the values in the same period in previous years.

  1. Where is the data that helps you measure the chosen KPIs ?

It may seem obvious, but unless you can get the required data easily then you won’t  be able to populate your excel dashboard. So before you plough head first into constructing a super slick dashboard, check that the data you require is actually available.

For example you may wish to breakdown sales by geographic area, but find that a couple of areas just produce consolidated sales with no apparent ability to breakdown the numbers. In the short term you might have to live with the consolidated info,  and hope that with time you are able to get hold of the new data. Note this illustrates an important point – don’t strive for perfection on the first day. Instead get a good prototype working and develop it over time.  Note that if you find that a particular KPI graph is no longer required – don’t delete it. Instead cut and paste it to a separate part of the workbook.

Often in large organisations, the data may come from several sources at different times of the month. Hence it is worth having a “project plan” so that you know when data is to be expected. This will help you manage the expectations of senior decision makers who will be eagerly awaiting the latest version of the dashboard.

  1. Graph types

 

Excel has a multitude of graph types that can be used to create stunning dashboards. There are three obvious ones line, bar and pie chart. However, these charts come into their own when you start to combine graphs types:-

  • Combine a line chart with a bar chart – on the line chart show the forecast and on the bar chart the actual values
  • Use the stacked bar and column charts to get a better understanding of the figures e.g the individual stacks could be the amount of particular product sold.
  1. Not just the graphs

Although graphs are a key aspect of Excel Dashboards, it is also possible to have summary tables as well. These can be used to show information that might not be clearly represented on a graph. For example you might want to have a table that pulls through the top three opportunities in each month.

  1. External data sources

The world is becoming ever more connected with the ability to pull down data from external data sources including the web, other accountancy packages and third party data sources. Before linking to such data sources, to pull down data it is worth considering the following points:-

  • If you want to pull down data in this way, then the chances are your excel skills will need the ability to write VBA code to control the flow of data. It allows means that the file must always be saved in an *.xslm format rather than *.xlsx. Hence any end users must be taught and reminded of the need to save the file in this format – otherwise the macro that manipulates the data will disappear.
  • Excel macros only work on Windows and not Apple Macs. Hence as far as I know there is no way of integrating a third party application to excel on a Mac.
  • If you are building a system on behalf of a client, ensure that their machine/network is configured to allow any necessary third party integrations e.g the user account may need to have Admin privileges.
  • Be mindful of software updates that could throw the integration out of synch. I had one particular client, who every time they updated their SAGE accounts, it threw out the excel integration.

 

  1. Useful Excel functions

Excel contains a vast number of functions that help facilitate the creation of dashboards. Here are a few with an overview of how they could be used:-

  • COUNTIF – used to count the number of values in a range that meet a certain criteria. E.g the number salespeople meeting a particular sales target
  • COUNTIFS – returns the number of values in a range that meet multiple criteria e.g the number of salespeople who live in a particular region,
  • SUMIF – returns the total of the values in a certain range that meet a criteria e.g the total value of sales made by a particular sales person
  • SUMIFS – returns the total of the values in a certain range that meet multiple criteria e.g the total values of sales of a product by each sales person
  • SUMPRODUCT – used when you need to count / sum values in date range e.g the monthly totals of each product sold. Again this could be split further by sales person.
  1. Underneath the dashboard

Although the visual graphs and any necessary summary tables are the outputs of the dashboard, they should not be considered in isolation. Another key part of the dashboard is data entry – and it is important to make this as painless as possible. This is because the person building the dashboard may not necessarily be the one populating it who in turn will not be the Senior decision maker. For example here are two scenarios that where I have built dashboards for clients:-

  • An engineering firm – the managing director who wanted an overview of the business was based in London. He gets his team to populate all the data – and they operate from Poland.
  • A finance firm – with two directors one for Sales and the other for operations. In this case, each director populated their respective areas, with the workbook being designed in such a way that it pulls together the respective information.

Hence think about how to make data entry easy – as this will ensure that the dashboard gets used,  and produces the correct information. For example, you might have:-

  • Separate sheets for each area of the business
  • Assign different colour codes for cells that require data entry and those that calculate values
  • Colour a row black or red, so that the user is aware that they shouldn’t enter data below this line.

Another point is considering that many organisations want to look at data over a rolling time period of say the previous 12 months. Hence you should avoid having to rebuild the dashboard every month (with the potential of errors / inconsistencies). The way to do this is to build dynamic charts and have a drop down to select the relevant time period.