Contextures

Home > Skills > Dashboards > Tips

Excel Dashboard Tips

An Excel dashboard is a single page that summarizes and displays the most important information and metrics for your business performance. It can help you visualize and analyze that data, and make it easier to monitor, measure, and improve your results.

When you build an Excel dashboard, be sure to show key information clearly, and highlight important data. The examples below show common dashboard mistakes, and how to fix them.

interactive controls on dashboard

Excel Dashboards - 5 Quick Tips

This short video shows 5 Quick Tips for Excel Dashboard. Keep these tips in mind when you work on your next project, to make the data easier to understand. The steps are also shown below.

Tip 1) Highlight key data

Know what the dashboard in your Excel workbook is designed to show

  • What key information should be highlighted ?
  • Which numbers are most important?
  • What insights should be communicated?

Dashboard Mistake

This dashboard doesn't have a clear focus on the key performance indicators (KPIs). It has several charts, but nothing in those visualizations is highlighted as key information.

no focus in dashboard

Dashboard Fix

Add key data at the top left, to highlight that information, and to give focus on the dashboard.

This revised overview can help you spot a trend in your data, so you can gauge where to invest your time, and other resources.

focus in dashboard

Tip 2) Muted colors

Use muted colors in the dashboard, and brighter or darker colors to highlight a few key items.

Dashboard Mistake

The colors in this dashboard hurt your eyes, and don't help tell the story of what is important.

too many colors in dashboard

Dashboard Fix

The revised dashboard uses muted colors, and stronger colors sparingly, to highlight the key information.

muted colors in dashboard

Tip 3) Clean & simple layout

A dashboard should show a streamlined summary of the key information -- don't try to include everything in a small space.

Dashboard Mistake

This dashboard is packed with charts and tables, and as a result, nothing stands out.

crowded dashboard

Dashboard Fix

This dashboard shows the key data, with highlights at the top, and a button to view detailed data, if necessary.

clean layout on dashboard

Tip 4) Clear charts

Keep dashboard charts clear and simple, so they get the information across quickly. Remove background colors, unnecessary gridlines and pictures.

Dashboard Mistake

Pie charts rarely work well, and avoid 3-D chart type effects, which distort the data.

cluttered charts on dashboard

Dashboard Fix

These charts use simple color schemes and white backgrounds, so the data stands out, and is easy to read. Be sure to choose the right chart type, or combo chart, for the data it shows.

  • The bar chart has product names at the left, in horizontal text, the total amounts at the right, outside of the bar.
  • The column chart shows regional sales, side by side, with error bars giving more information for each of the data points
  • The combination bar chart and line chart shows two sets of numbers, in a clear and concise way

clear charts on dashboard

Tip 5) Interactive controls

For a dynamic dashboard, add simple interactivity controls on the spreadsheet, so users can change the focus. The controls should be clearly labelled, and easy to use.

Dashboard Mistake

This dashboard has a few charts, and key points at the top right, but no way to adjust the view.

no interactive controls on dashboard

Dashboard Fix

Include a few simple controls, such as option buttons, check boxes, scroll bars, drop-down lists, and slicers. With a few clicks on those worksheet controls, people can sort or filter the dashboard data, and pivot tables, to focus on specific data in dynamic charts and formula results

You can also use Excel timelines and slicers to filter the dashboard data, quickly and easily.

interactive controls on dashboard

More Dashboard Problems

To see a list of problems that people have when building Excel dashboards, go to the Excel Dashboard Problems Survey Results page. It's the results of a dashboard survey that I posted on my Contextures blog, and compiled to share with you.

First, I asked if people build Excel dashboards, and for those who said Yes, the follow up question was "What problems do you have with dashboards?"

A tutorial on that page shows how I built a report from the survey results data.

Excel dashboard survey results yes no

Video: Show Chart or Data on Dashboard

This will save some space on your dashboards. Select "Chart" or "Chart Data" from a drop down list, to show either a Microsoft Excel chart or the chart's data on your dashboard. There are no macros in the workbook -- this tip uses named ranges and a linked picture.

This video shows you the simple setup steps, and there are written details on the Contextures Blog.

Dashboard Templates

You can download my sample Excel dashboard templates, for more inspiration and ideas.

  • In some of the dashboard examples, there are pivot tables in the background, summarizing the complex data.
  • Excel formulas do complex calculations based on the raw data in your Excel tables, for key metrics on the dashboard
  • Other examples have drop down lists, where you can select data settings, such as a date, or a specific market, for the dashboard graphs.

Change All Pivot Charts With One Filter

More Dashboard Resources

Note: I am an affiliate for the products mentioned below, and earn a commission on the sales.

Excel Dashboard Course offered by Mynda Treacy from My Online training Hub -- an amazing course that will take you from the beginning steps, up the advance dashboard techniques

Excel Charting Tools by Jon Peltier-- great tools for quickly creating complex charts

 

About Debra

Last updated: May 25, 2023 2:41 PM