Home » Computers & Electronics » PCs » Computer Software

How to Create an Excel Dashboard

0 Comments

3 Like

Like 3

Pin It

Ranked #4,599 in Computers & Electronics, #84,825 overall

Summarize Data With an Excel Dashboard

To summarize the key data in a complex Excel workbook, you can add an Excel dashboard.

Your Excel dashboard can be a simple sheet with a few key numbers and one or two charts. In some workbooks, you might need a bigger Excel dashboard, with a series of charts, and one or more tables of numbers.

In this lens you'll see how to add data and charts to an Excel dashboard, and tips for setting them up.

Plan Your Excel Dashboard

Before you add items to your Excel dashboard, decide what information is most important to your users. A large Excel workbook might contain many sheets of data, packed with thousands of rows of numbers.

With an Excel dashboard, you can summarize that data, and show only the key numbers. For example, show the total revenue for the latest quarter, or the annual profit.

Shown below is a small section of an Excel dashboard that shows key revenue numbers, and a chart of the data. Someone opening this workbook can easily see how things are going, by looking at the latest numbers, and the Revenues chart.

Sample Excel Dashboard

Add Key Data With Links

To show a summary of numbers on the Excel dashboard, you can use simple formulas. For example, you might have total revenue numbers at the top of a worksheet named SalesData.

To show the total revenue on the Excel dashboard, use an IF formula, that will show the total. In the example, the formula on the Excel dashboard is:

=IF(SalesData!F10="","",SalesData!F10)

If there is no data in cell F10 on the SalesData sheet, this formula will show an empty string "" so the formula cell will look empty. If there is sales data in cell F10 on the SalesData sheet, this formula will the sales total.

Add Small Simple Charts

Charts are a great way to show a summary of several years of data, or the latest few quarters. On an Excel dashboard, simple line charts are very effective for showing values that change over a period of time. Use simple formatting, such as white background, and remove any extra labels or numbers.

In the sample charts shown above, the charts each have a single line, with small numbers on the axes. There's nothing else to distract the reader, and the message is clear in each chart.

Make the charts very small, by dragging a corner of the chart in toward the center of the chart. Adjust the font size, if necessary, so it's also as small as possible, but still legible.

Highlight the High and Low Numbers

You can use colour to highlight the numbers on the Excel dashboard that are below or above a specific target. For example, with Excel's conditional formatting feature, you can highlight a cell if it contains an amount greater than 50%.

To add conditional formatting in Excel 2007, follow these steps:

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.

In the dialog box that opens, you can set the minimum amount, and select the formatting you want. For example, highlight the cell in bright yellow if the value is over 50%.

If you're using Excel 2003 or earlier version, see the instructions at Excel Condtional Formatting Basics.

Excel Dashboard Kit

You can create your own Excel dashboard, or use an Excel dashboard kit to make things easier as you get started.

I bought an Excel dashboard kit. the first time I had to make an Excel dashboard. The kit had several templates and sample files, and a well-written instruction manual.

The Excel dashboard kit was very reasonably priced, and paid for itself in the hours that I saved while working on my first project. I've used it many times since then, and it remains a valuable addition to my Excel tools and resources. Click this Excel dashboard kit link to see what's in the kit, and order your own copy of this time-saving tool.