A Quick Guide to Excel Dashboards

on September 12th, 2016
Business intelligence

Dashboards, much like those in our vehicles, are designed to give the user a quick update and overview of how things are going under the hood. The dashboard gives the user a means for accessing the business intelligence within the Excel spreadsheet and delivering it visually. Think of it this way: you’re driving down the highway and you glance at your car’s dashboard. In an instant, you know your speed, how much gas you’ve got, whether the engine is working too hard, whether there is enough air in your tires, and even what radio station you’re listening to. That’s what Excel dashboards do for the business intelligence data in your Excel spreadsheets. It makes the meaning of the data apparent to the user at a glance.

Get an Excel Dashboard Template

The fastest and easiest way to equip your Excel spreadsheet with a dashboard is to use one of the preexisting templates. These templates are downloadable, and feature a number of helpful tools and resources, such as Gantt charts, raw data sheets, tables and graphs, and more — whatever best showcases the types of data you need for business intelligence. Most are even customizable, so after setting up your dashboard using a template, you can tweak it to look and act exactly like you want.

Use PowerPoint to Make an Excel Dashboard

Alternately, you can create your own custom Excel dashboard using PowerPoint. This eliminates some of the complexity of creating dashboards directly in Excel, especially for those reluctant to use Macros or VBAs (this is the programming language that Excel uses). You can put the charts and components you make in Excel into PowerPoint, and even include an interactive element. For instance, you can create a pie chart representing what the business intelligence data looked like for each of five separate years. Then, add a single pie chart to a slide in PowerPoint, and it gives the appearance that the chart is in motion. PowerPoint presentations can be emailed easily, as well.

DIY an Excel Dashboard

You can also make your own Excel dashboard from scratch. Here are the steps:

1. Import the data that contains your business intelligence into Excel. Do this by copying and pasting or through exportation.

2. Set up your workbook. Do this by opening a new Excel workbook and making two to three sheets (tabs). One of the tabs will serve as your dashboard, and the rest hold your raw data.

3. Establish a table using the raw data. In order to do this, your data has to be in tabular format. That means that every point of data is contained in its own cell.

4. Choose the dashboard tools you need to use. You can use formulas in Excel (SUMIF, OFFSET, COUNT, VLOOKUP, GETPIVOTDATA, etc.), or other tools like pivot tables, data validation, auto-shapes, named ranges, conditional formatting, charts, dashboard widgets, macros, Excel tables, and more.

5. Build your dashboard. Simply go to the dashboard sheet in your Excel workbook and click Insert. Go to the Charts option and choose whatever chart or graph best represents how you need to present your business data. Then link your chart to the data in your raw data sheet.

Once you have built your dashboard, you can customize it with your own colors, fonts, macros, etc. You can even add animation into your dashboard. Play around with it! The more you learn, the better your business intelligence dashboards are bound to be.

Love this article? Want to toss a pie in the author’s face? Tell us! Contact us to express your thoughts, feelings, and opinions on Data Insider.