Search Contextures Sites
Summarize Data With Excel Dashboard Templates
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.
Link to Key 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:
Label the Key Data
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.
Add Simple Charts
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.
Make the Charts Small
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.
Highlight the Good News and the Bad News
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:
- Select the cell that you want to highlight
- On the Ribbon, click the Home tab, then click Conditional Formatting
- Click Highlight Cells Rules, then click Greater Than.
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.
Excel Dashboard Template Kits
A few years ago, before making my first Excel dashboard, I bought an Excel dashboard template kit to help me get started. Even though I'd been using Excel for several years, I wasn't an expert in this area of reporting. Instead of spending many hours figuring things out for myself, I decided to invest in the Excel dashboard kit.
The Excel dashboard template kit came with an instruction manual, in pdf format, and several templates and sample files. Some of the techniques were familiar to me, but there were lots of new techniques demonstrated. The manual was clearly written, and easy to follow, and I had my own Excel dashboard up and running in a very short time.
The Excel dashboard template kit was reasonably priced, and more than paid for itself in the time that I saved on that first project. There's a 1-year unconditional money back guarantee, so you can try the Excel dashboard template kit risk free.
Free Excel Dashboard Webinar
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.
Contextures Inc., Copyright ©2016
All rights reserved.