Summarize your data with Excel dashboard templates. Show charts and tables to highlight key factors. Save time with dashboard kits or online training
Some of your Excel projects need a dashboard. Those projects have lots of data -- too much to absorb by reading through it. Perhaps there are several years of sales data, or thousands of survey results. Even if you make charts, people might still have trouble wading through the sheets, looking for the data and charts that they need.
To make things easier to understand, you can create an Excel dashboard. The Excel dashboard can show a few key numbers, small charts to summarize the data, and formatting to highlight the good and bad results.
For example, here's a small section of an Excel dashboard that shows key revenue numbers, with a chart of the data. At a glance, the person reading your Excel dashboard can see how things are going, without sifting through years of data.
To pull key numbers to the Excel dashboard, you can use links. For example, you have total amounts at the top of a worksheet named SalesData.
On the Excel dashboard, you can use an IF formula, to show each total, or leave the cell empty if there's no total on the SalesData sheet.
In this example, the formula in cell D4 is:
If you pull key numbers to the Excel dashboard, it's important to label all the data clearly. You can manually enter a label in the cell next to a key number. Or, if the source sheet has clear, concise labels, you can use formulas to link to those labels.
On an Excel dashboard, the objective is to make the data easy to understand. So, when you add charts, keep the charts simple. Show one set of numbers per chart, such as total quarterly sales for the previous 5 quarters.
Use a simple line chart, with only the key features, and minimal formatting. These two charts have a single line, numbers on the Y axis, and quarters on the X axis. There's nothing else to distract the reader, and the message is clear in each chart.
Because the charts on the Excel dashboard are so clear and simple, you can reduce their size. In the sample charts shown above, the information is easy to understand, even though the charts are only about an inch wide.
With small charts, clearly labelled and titled, you can arrange many charts on the Excel dashboard, without overwhelming the reader.
With conditional formatting, you can use colour to highlight numbers on the Excel dashboard that are well below or above average.
For example, you could highlight a cell if it contains an amount greater than 5000:
This will open a dialog box, where you can set the minimum amount (5000 in this example), and select the formatting that will highlight the cell. You could format the cell with a bright red fill colour if it's over the set amount.
A few times each year, Mynda Treacy, from My Online Training Hub, presents a free webinar on creating Excel Dashboards. Click the image below, to get the registration details, and attend the free session.
Excel Charting Tools by Jon Peltier-- this Excel add-in has tools for quickly creating complex charts
Excel Dashboards and Reports, a book by Mike Alexander and John Walkenbach -- takes you step-by-step through the stages of dashboard planning and design
Data Master Bundle -- A series of online courses from Excel with Business. The bundle includes Excel, Data Science, Business Analysis and Microsoft Access courses. Click the image below, for bundle details
Excel dashboard template kit by Charley Kyd -- no longer available
Note: I am an affiliate for the products mentioned above, and earn a commission on the sales.
Last updated: March 24, 2018 11:12 AM
Contextures RSS Feed