This is a collection of tips that will help you create powerful Excel dashboards for your consulting clients. This ranked list started by collecting the top Excel dashboard tips from multiple search engine pages. After aggregated the tips I ranked them for usefulness based on 30 years of experience developing Excel dashboards and strategic performance projects.
1. Talk to Users. Build for their Needs.
Identify the dashboard’s primary user and interview them for their needs. Here are a few categories and the ends of a range of responses you might want to touch on in your interviews. The general idea for these comes from a Juice Analytics whitepaper, A Guide to Creating Dashboards People Love to Use.
| Role | Decision Maker | Analyst |
| Scope | Strategic | Operational |
| Time Span | Long | Near |
| Time Period | Historic, static | Real-time, predictive, dynamic |
| Depth | High level, Across | Detailed, Drill down |
| Use | Read, decide | Query, analyze |
| Ease with Data | Business-level | Operational data lover |
2. Use Proven KPIs Before Inventing Your Own
Before you spend hours of fun brainstorming KPIs and metrics check with your industry organization and in some of these great books for industry standard KPIs and metrics.
3. Map the Metrics that Drive Your KPIs
A KPI is a Key Performance Indicator. It is a metric that drives the success of a key strategic or operational objective. Selecting the right KPI(s) for each objective is absolutely critical. Obviously, if you don’t have the right KPIs you may drive the organization’s efforts in the wrong direction.
Often a strategic or operational objective will need two KPIs, one to balance the other. For example, an objective that demands the revenue KPI go up could be counter-productive if that happens because margins go down, so a margin KPI might be needed as balance. For every KPI you should have two or three driver metrics that are the primary drivers of the KPI. Rather than pulling metrics out of the air or using “what we’ve always used”, draw a map.
For Balanced Scorecards start with a Strategy Map. Start at the top and work down identifying KPIs and metrics that drive each objective. Some KPIs, especially customer perception and branding, are extremely difficult or expensive to measure.
Strategy Maps are excellent for showing the relationship between objectives in your strategy.
For operational KPIs and metrics use one of the many business flow diagraming methods to create a flow map, then identify the cause and effects in the operation. From the diagram you should be able to identify the most important KPIs and their drivers. Best way to do it? Grab two or three SMEs (subject matter experts) and a stack of Post-It notes and start mapping on a wall. (For Strategy Maps and simple business maps I use inserted objects in PowerPoint.)
4. One Domain. One Dashboard. One Set of KPIs.
This reminds me of my favorite story about the Texas Rangers. In the old West in the United States (think cowboys) before the West had states and governments, there was only one law officer per territory, a Texas Ranger. When a riot of immense proportions was feared to break out in the town of Dallas, Texas, the mayor telegraphed for a squad of Rangers. The story goes that the next morning a single Texas Ranger stepped off the train. The mayor, anguished, asked, “Where are the other rangers?” The Ranger slowly answered, “One riot. One ranger.”
A few of the Texas Rangers.
Dashboards at the executive and top-management level are usually designed to monitor one performance domain, for example, Regional Sales. Put on the dashboard the KPIs and metrics needed to make a decision about the one performance domain. No more, no less.
Sometimes two or three counter-balancing KPIs are needed, like Revenue, Average Deal Size, and Margin. Put those KPIs on the top of the dashboard or down the left side. The other metrics on the dashboard should be drivers of those top KPIs.
5. Put Key Data at Top Left. Group Related KPIs and Metrics.
The most important data should be at the top left of the dashboard in Left to Right (LTR) countries. So, put your most important KPIs, metrics, and indicators at the top left. Put driver metrics and related analytics down and right.
Keep the dashboard focused on a KPI and its driver metrics.
This clean example is out of a tutorial and sample files using PivotTables in the Journal of Accountancy, Jan 31, 2011.
By Mark W. Lehman, CPA, Carol M. Lehman and Jim Feazell
This tutorial has a nice use of a drop-down in Unit Styles to retrieve data from a PivotTable using the GETPIVOTDATA function. (To learn how to do this see Chapter 21, Working with PivotTables, in “Balanced Scorecards & Operational Dashboards with Microsoft Excel” by Ron Person, the author of Critical to Success.) Data, calculations, and display are also kept in separate worksheets as tip 11
6. Group Data and Visuals in a Clean and Simple Layout
Keep your dashboards clean and simple. Use lots of whitespace so readers can focus on important data and relationships. If an element is not adding to the understanding or insight, delete it.
From Debra Dalgleish at Contextures
When management first begins using dashboards it is best to start with dashboards, perhaps using a grid layout with three (above) or four rectangles. As they use the dashboard more they become more familiar with how to read it and the relationship between sections. Once they are familiar you can can begin including additional data; however, it is still important to put “just enough” and “not too much.” They will let you know what they want. I usually have a few backup dashboards ready to support the main dashboards.
7. Use Conditional Icons for Quick Assessment
Make it easy for your readers to decide what to focus on. Use icons to give a quick assessment of good, neutral, or bad results. Conditional icons use conditional formatting and are found on the Home ribbon, in the Styles group, in Conditional Styles. Select an icon set that will indicate whether the metric is good, neutral, or bad.
Data icons make a quick assessment easy.
8. Learn Basic Design Concepts
You don’t have to become an artist. But, a few design principles will make you feel more confident and make your dashboards more pleasing.
Seriously lift your design credentials with “The Non-Designer’s Design Book (4th Edition)” by Robin Williams. You’ll learn to design with CRAP – Contract, Repetition, Alignment, and Proximity.
It isn’t specific to dashboards, but it will change how you see text and designs from the moment you open the book.
9. Keep It Clean
Keep the look clean. Use lots of white space.
As soon as Excel creates its default chart, get rid of the chart clutter by clicking on the interior horizontal and vertical lines and pressing Delete. If the chart shows only one data set you don’t need a legend, so click it and press Delete. If the context is obvious you don’t need the title, click it and press Delete.
One way to make this easier is to use chart templates. See the following tip on templates.
10. Use Cell, Chart, and Dashboard Templates for Consistency
Templates give a professional look that matches your organizational brand. It also maintains consistency in layout and style between different operational presentations so the user can work on making a decision rather than searching for meaning in the dashboard. Just like the templates you create in PowerPoint and Word, you can create templates and styles in Excel.
Grid Layouts for Excel Dashboards
Almost every dashboard solution you need will fit one of three or four general grid layouts. Choose three or four grid layouts for your templates. This series of dashboard layout grids are from Microsoft’s Operations Manager 2012 Dashboard Widget.
Use templates for consistent layout and format.
Create custom cell styles that include fonts and font size, number formats, cell borders, and cell colors and shading. You can create, apply, modify, and remove custom cell styles.
Create, save, and apply chart templates for chart designs you use frequently.
11. Keep the Charts/Reports, Calculations, Menu Items, and Data each on Separate Sheets
Make it easier to maintain and protect larger Excel projects by putting reports and charts, calculations, menus and items, and data on separate worksheets. Keeping each type in a separate worksheet makes it easier for you to architect the layout for each sheet. For example, unless it is very simple, putting data on the same sheet as a report and charts will restrict the freedom of your row and column layout. You may also want multiple report and chart sheets feed from one sheet of calculations.
Put presentation, calculations, data in separate worksheets
This figure shows a worksheet I’ve used to layout the lists for menus, menu items, drop-down lists, and all the control variables used to control a very robust ROI calculator. With it all on one well-documented sheet it makes it easy for another developer to find and make changes (and for me to remember). Notice the arrows that point between tables to show the logic flow of menu selections and calculations.
12. Don’t Keep Data in the Worksheet
Don’t keep your data in the Excel workbook, if possible. Keeping data in the workbook, from a CSV file for example, is asking for “One Thousand Versions of the Truth.” You have difficulty knowing the currency and validity of the data.
Instead, link the workbook to data in a repository. For example, link a PivotTable to an external data source.
13. Use Structured Range Names for Clarity
Well documented range names make formulas much easier to understand. For example, instead of a formula reading,
=INDEX($C$12:$F$18,MATCH($D$4,$C$12:$C$18,0),4)
It’s much more understandable when you use,
=INDEX(tblSalesProducts,MATCH(rngName,tblSalesRep,0),4)
Using a naming syntax will make it easier for you to know where the range name is and what it does. See the links below for more.
Learn how to use the Name Manager to make range names easy to change. To get to the Name Manager on the Home ribbon, in the Defined Name group, click Name Manager.
14. Create Dynamic Dashboards for Data Interaction
Executive- and upper-management dashboards usually show just the top strategic KPIs and their direct drivers. However, operational level managers and analysts want to interact with data and get at the details.
Use Data Validation to create interactive charts.
Use the dynamic charting methods described in Critical to Success to filter and query data. Use drop-downs, sliders, Data Validation, and more so that dashboard users can retrieve and analyze the data they need.
15. Use PivotTables and Slicers for Large Data Interaction
PivotTables are the demi-god of data analyses. You can use them to slice and dice large databases. Slicers are clickable widgets that filter the results from a PivotTable.
16. Create a Visual Comparison Between Forecast and Actual
Make it easy for managers and analysts to see the difference between budget (expected) and actual (reality). This blog will show you five different ways to chart budget vs. actual (forecast vs. actual).
Colored markers show target values against actual values in columns.
17. Show Trends when Growth is Being Analyzed
Trends smooth erratic and show the overall direction of movement. I recommend using one of Excel’s trend functions rather than the automatic trend lines you can add to charts. Functions give you more control.
The TREND function can also forecast data for future time periods.
18. Format with Tables so Excel Charts Automatically Expand to include New Data
In a chart, if you add data to the end of the chart’s data sequence or add a new data series, the chart does not update to include the added data. There are two ways to correct this. One is to select the range of headings and data for the chart and then choose Home, Styles, Format as Table. The data becomes an Excel table which will auto-expand to include new data for the chart. The second method is to use dynamic range names.
19. Use Dynamic Range Names so Excel Charts Automatically Expand to include New Data
Use the OFFSET and COUNT or COUNTA functions to create dynamic charts in Excel that resize automatically as new data is added. Dynamic range names are MUCH more work than making the chart data into a table (see tip 18), but once you know this technique you will have amazing super powers to create interactive data and charts.
20. Be Wary of Pie Charts
Some executives love to see pie charts, but I try to steer people clear of them. It’s much better to use column charts (not stacked columns) so viewers can easily compare the ratios of heights.
21. Never Use 3D Charts
Never use 3D charts!
Never use 3D charts or did I write that already? 3D charts look flashy, but they make visual analysis and comparison difficult.
22. Use Comments as In-Context Help
Use the Insert Comment and Edit Comment commands to insert pop-up help throughout your data-entry area, dashboard, or report. Comments pop-up a text box when the mouse hovers over the cell containing the comment. You can format comments with font size, bold, colors, etc. by selecting the cell they are in, right-click Edit Comment, then move over the comment box and right-click Format Comment.
23. Post a Version and Date on each Report or Dashboard
Put a version number and date of refresh on your dashboards. People can quickly compare whether they are using the same dashboard version and whether they have the current data.
We’ve all seen tens of minutes wasted trying to understand which charts and reports have the most current numbers.
24. Document Your Work
In addition to user comments, have empathy with yourself or other dashboard developers. If you have separated your calculations and menus from the presentation you have room to add comments and explanations. I even put in arrows to show the flow of data or logic. (Yes, you can use Home>Formulas>Trace Dependents/Precedents, but arrows on the worksheet give a better idea of the holistic process.)
