By David Nunn AATPowerUpPresenting data 2 – using Excel to make persuasive and useful data dashboards21 May 2019 We show how to make dashboards in part two of our series on getting started in data analytics using only Excel.Using Excel for analytics seriesPresenting data – 1 – How to get started with dynamic chartsExample spreadsheet 1 Presenting data – 2 – How to use Excel to make dynamic dashboardsExample spreadsheet 2A dashboard is a visual summary of how a business is doing, and you can easily create one in Excel. Dashboards enable business owners and senior decision-makers to get an idea of how a business is performing and to identify any potential problems as quickly as possible. Below are some guidelines on how to create a good dashboard in Excel.What metrics do you need to show?The graphs in a dashboard visually summarise the value of key metrics or key performance indicators (KPIs). Here are some KPIs that are commonly measured by companies:value of sales in a periodnumber of enquiries received in a periodnumber of staff in business in a periodnumber of customer service tickets raised and closed in a period.Each business will have its own KPIs. Hence the first thing to is to decide what KPIs should be measured.Don’t have too many KPIs in one dashboard – limit it to six. If you need a lot more than six, thenI would suggest breaking down your dashboard into mini-dashboards on separate worksheets in one workbook. For example, you might have one for HR-related metrics and another sheet for sales.Tips for managing dataIf you do need to have multiple dashboards, then keep all the data in one workbook. I always avoid linking Excel workbooks as they may not stay synchronised.Most companies use monthly dashboards. However, some organisations may have a separate dashboard showing either yearly results or cumulative values for the year to date. Senior decision-makers like to look for trends – for example, how are sales in this period compared to the values in the same period in previous years?Keeping data accessible It may seem obvious, but unless you can get the required data easily, you won’t be able to populate your dashboard. So, before you plough head first into creating a super-slick dashboard, check that the data you require is actually available. Don’t strive for perfection on the first day.You may wish to break down sales by geographic area, but find that a couple of areas produce consolidated sales with no apparent ability to break down the numbers. In the short term, you might have to live with the consolidated information and hope that, with time, you’ll be able to get hold of new data.Get a good prototype working and develop it over time. If you find that a particular KPI graph is no longer required, don’t delete it. Instead cut and paste it into a separate part of the workbook.Plan interactionsIn large organisations, the data may come from several sources at different times of the month. Therefore, 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. Choose the right type of graphExcel 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 them.Try combining a line chart with a bar chart. On the line chart show the forecast, and on the bar chart show the actual values. Use the stacked bar and column charts to get a better understanding of the figures – for example, the individual stacks could be the amount of particular product sold.Summary tablesAlthough graphs are a key aspect of dashboards, you could use summary tables as well. These can show information that might not be clearly represented on a graph. You might want to have a table that showcases the top three opportunities in each month.External data sourcesThe 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, it is worth considering a few points.If you want to pull down data in this way, the chances are that you will need the ability to write Visual Basic for Applications code to control the flow of data. In that case, the file must always be saved in the XSLM format, rather than XLSX. 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.Need to work with more complex data sources? Check out our series on Data Analytics for businessExcel macros only work on Windows, not Macs. As far as I know, there is no way of integrating a third-party application in Excel on a Mac. If you are building a system for a client, ensure their machine or network is configured to allow any necessary third-party integrations – the user account may need admin privileges.Be mindful of software updates that could throw the integration out of sync. One client of mine had issues with Sage integration. Every time they updated their Sage accounts, it threw out the Excel integration.Think about data entryAlthough 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 – it is important to make this as painless as possible.The person building the dashboard may not necessarily be the one populating it. For example, I worked with two businesses with very different approaches:An engineering firm, with a managing director based in London. He got his team to populate all the data – and they operated from Poland.A finance firm with two directors – one for sales and the other for operations. Each director populated their respective areas, and the workbook was designed to pull together the respective information.Think about how to make data entry easy. This will ensure the dashboard gets used and produces the correct information. For example, you might have separate sheets for each area of the business, or you might assign different colour codes for cells that require data entry and those that calculate values. Colour a row black or red so the user is aware they shouldn’t enter data below this line. Consider that many organisations want to look at data over a rolling period – say, the previous year. You should avoid having to rebuild the dashboard every month, increasing the potential for errors and inconsistencies. The solution is to build dynamic charts (see the previous article) and have a drop-down to select the relevant time period.Graph types, and how to use themcolumn – Great for comparing values.Bar – Avoids clutter when labels are long.Line – Reveals trends over time.Area – Shows part-to-whole relations.Pie – How various elements represent part of a whole.Histogram – Show the shape of a continuous data set.Scatter – Shows lots of data points.Waterfall – How positive and negative factors affect a value.Surface – The optimum combination of two data sets.Radar – plot groups of values over common variables.Useful Excel functionsExcel contains a vast number of functions that help facilitate the creation of dashboards. Here are a few that could prove particularly useful.COUNTIF This is used to count the number of values in a range that meet certain criteria – for example, the number of salespeople meeting a particular sales target.COUNTIFSThis function returns the number of values in a range that meet multiple criteria – the number of salespeople who live in a particular region, say.SUMIF This returns the total of the values in a certain range that meets certain criteria – for example, the total value of sales made by a particular salesperson.SUMIFSThis returns the total of the values in a certain range that meets multiple criteria – for instance, the total values of sales of a product by each salesperson.SUMPRODUCT This is used when you need to count/sum values in a date range – for instance, the monthly totals of each product sold. Again, this could be split further by the salesperson.Download example fileYou can download an example Excel file for this article by clicking here.About the authorKapil Kapur is the director of Fingertips Intelligence, which helps companies obtain management information (a.k.a business intelligence) easily. David Nunn is a former Content Manager at AAT.