The Top 25 Best Tips on Excel Dashboards

Charts

This is a collection of tips that will help you create powerful Excel dashboards. 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.

Lean Analytics: Use Data to Build a Better Startup Faster (Lean Series)
by Alistair Croll and Benjamin Yoskovitz

Performance Dashboards: Measuring, Monitoring, and Managing Your Business
By Wayne Eckerson

Data-Driven Marketing: The 15 Metrics Everyone in Marketing Should Know
by Mark Jeffery

Marketing Metrics: The Manager’s Guide to Measuring Marketing Performance (3rd Edition)
by Paul Farris, Neil Bendle, Phillip E. Pfeifer, David J. Reibstein

Web Analytics 2.0: The Art of Online Accountability and Science of Customer Centricity 1st Edition
by Avinash Kaushik

The HR Scorecard: Linking People, Strategy, and Performance 1st Edition
by Dave Ulrich, Mark A. Huselid, Brian E. Becker

The Workforce Scorecard: Managing Human Capital To Execute Strategy 1st Edition
by Mark A. Huselid, Brian E. Becker, Richard W. Beatty

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.

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.)

You can learn more about flowcharts in Wikipedia.

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.

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.

Use Data Validation to create interactive charts.

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.

Dashboard Your Scorecard
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.

img-dashboard-clean-layout

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.

Data icons make a quick assessment easy.

From the Excel Easy site

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 Data Validation to create interactive charts.

Use templates for consistent layout and format.

Custom Reusable Cell Styles for Excel Dashboards
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.

Reusable cell styles.

Reusable cell styles.

Chart Templates
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

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.

Use the Name Manager to edit existing range names.

Use the Name Manager to edit existing range names.

Quickly Create Range Names

Use a Structured Prefix to Identify Range Name Types

Use a Major and Minor Naming Convention when Naming Ranges

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 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.

How to Create a Dynamic Chart Using a Data Validation List

How to Create a Dynamic Chart with Drop-Down List

Creating Dynamic Excel Chart Titles that Link to Worksheet Cells

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.

A good place to start learning PivotTables is at Excel Easy.

Another good site is by Debra Dalgleish. She is the author of three books on Excel PivotTables.

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.

Colored markers show target values against actual values in columns.

5 Top Budget vs Actual Excel Charts You Need

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.

The TREND function can also forecast data for future time periods.

Creating a Simple Trend Line on Your Excel

Using Excel’s TREND function to Create Trend Lines on Excel Charts

Using the TREND Function to Forecast Future Values in Excel Charts

Dynamically Change the Type of Trend Analysis in an Excel Chart Using a Drop-Down

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.

Creating Dynamic Charts in Excel that Resize Using the OFFSET Function and Named Ranges

11 Best Dynamic Excel Chart Tips

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.)

25. Sometimes You Do the Best You Can and Fix It in Version 3

 

Share the power...

Leave a Comment:

Leave a Comment: