The most important part of an Excel Dashboard
With an increasing focus on using dashboards, there seems to be a lack of understanding of what the most important part of an Excel Dashboard process is. We recently gave a presentation at the GROW quarterly conference where we explained what you should focus on when considering creating dashboards.
What is the point of a Excel dashboard?
Generally speaking you are wanting to get a:
- Quick view of key metrics e.g.
- Sales conversions
- Sales numbers
- Gross Profit Margins
- Net Profit Margins
- Debtors Days
- Lead Times/ Turnaround times
- Summary of what (or who) is working in your business
- Summary of what (or who) is not working in your business
Creating a fancy Dashboard
At the moment wherever you look you see impressive graphics with various dashboards. Whether it is watching sports or reading business reports, more and more effort is being spent on the visual aspect of the presentation.
However, please note that
- Most of the graphics are available free or to buy and the pricing is very competitive
- Excel can do all of this as well
- Web services make it easy- there are sites that you send your data to and they will send back the dashboard
But they all require the same thing from you
- THE DATA MUST BE PRESENTED IN A USEFUL FORMAT
Why is the Dashboarding process so tough?
The process of creating and maintaining useful dashboards is tougher than it should be. The reason we believe this is the case is due to:
- Lack of initial planning
- Mixing up of data (will explain later)
- Allowing users to enter anything they want
- Users ignoring the rules of the spreadsheet
- Sharing of spreadsheets across multiple users for capture (reporting is fine)
Notice that none the reasons above refer to the end product. Making a graph pretty is easy once you have the correct data. The key is getting the correct data and in the correct format.
The First step in building a Dashboard
The first step in building a dashboard is planning what you want. Even if it is as simple as the diagram below, this will concentrate your thinking on what you actually need.
Most important is that you don’t start from the left in this picture but rather start from the right. So:
- Decide first what you want to achieve. If you have to, draw the dashboards you want and indicate the types of numbers involved. Create a sample out of real data.
- Then see what calculations would be required to achieve these outputs
- and only at this stage can you actually determine the inputs you need and the format you may need it in.
Avoid the temptation of looking at what inputs you have and then building from there. At the end you will have missed something and you will have to build work-arounds which is where things start going wrong.
The rules to follow to get input data into a useful format
Whether you are creating the input data in Excel or extracting it from a system (and then inevitable reworking some of it in Excel), some general rules to follow are:
- A column should only contain one type of data i.e. date, text or number. Don’t mix data, rather use more columns. Many of Excel’s tools rely on consistency of input (at the 3 Day Advanced Excel course we cover the benefits of this approach and the tools you can use).
- As much as possible limit the number of choices available. Notice below the misspelling of what looks like the same person. Something as simple as a yes or no answer can also have multiple options if you allow free entry. Limit the options to make it easier to report on (Data Validation is useful here which is covered in the 3 Day Advanced Excel course).
- Avoid building in subtotals. Once you have subtotals in it becomes difficult to summaries in other ways. Tools like Pivot Tables will do all this for you and enable you to do more. Pivot Tables are covered in detail in the 3 Day Advanced Excel course.
- Make sure dates are actual dates as far as Excel is concerned (just because you think it looks like a date, doesn’t mean Excel sees it as a date). Once Excel recognises a date it allows you to use more tools in Pivot Tables, Filters and with normal arithmetic. The trick is to know what is a valid date in Excel. Dates are extensively covered in the 3 Day Advanced Excel course (below only 2 of these dates are acceptable on my computer)
- If you have too many options, categorise your data to provide more meaningful results. As shown below it would be difficult to ‘summarise’ based on the age of our customers. But if you can categories into age brackets, it is suddenly easier to produce meaningful reports. If you want a quick way to do this, look at the VLOOKUP online course or attend the 3 Day Advanced Excel course
Useful Excel Tools once you apply the above mentioned rules
If you apply the above rules you will find that it is easier to create reports and dashboards out of your data. Whether you intend creating these reports in Excel or some other software, the format of the data will enable you to harness the power of Excel and other reporting packages.
The tools to become more educated in from an Excel perspective (which are all covered in the 3 Day Advanced Excel course) are:
- Dates- how they work and the functions built specifically for dates
- Data Filter- how to use it to extract quick reports from data or clean data
- Data Validation- to limit what users can enter into a cell and make sure the spelling is correct and as expected.
- Pivot Tables- For any dashboard or management report you can think of. If you work with data and report YOU MUST LEARN ABOUT PIVOT TABLES.