When you create an Excel workbook to share information with co-workers, or company management, you can add a dashboard to summarize the data. Excel files can have many sheets full of data, which is too much to absorb by simply reading through it.
A file can contain several years of sales data, or thousands of survey results. Even if you create charts from the data, people might still have trouble wading through the sheets, looking for the specific data and charts that they need.
To make the vast sea of data easier to understand, you can create an Excel dashboard sheet, and include a few key numbers, small charts to summarize the data, and formatting to highlight the positive and negative results.
For example, an Excel dashboard could display key revenue numbers, with a small line chart of the past year's data. At a glance, anyone reading your Excel dashboard can see how things are going, without digging through years of detailed data.
Link to Key Data
To pull key numbers to the Excel dashboard, you can use links. For example, at the top of a worksheet named SalesData, you have a formula that calculates the total amount for the sales in column D. The total is in cell F2, and the formula in that cell is =SUM(D:D)
On the Excel dashboard sheet, you can use an IF formula, to show the sales total, and to make the cell appear empty if there's no total on the SalesData sheet. In this example, the formula in cell D4 on the dashboard worksheet is:
=IF(SalesData!F2="","",SalesData!F2)
Label the Key Data
When you pull key numbers to the Excel dashboard, it's very important to label all the numbers clearly. To create the labels, you can manually type a label in the cell next to a key number. Or, if the source sheet has clear, concise labels, you can use formulas to link to those labels.
Add Simple Charts
The goal of adding an Excel dashboard is to make the data easy to understand. So, when you add charts to the dashboard, keep the charts simple. Show one set of numbers per chart, such as total quarterly sales for the previous four quarters.
Use a simple line chart, with only the key features, and minimal formatting. The ideal charts would each have a single line, with a limited number of labels on each axis. There should be nothing else to distract the reader, to ensure that the message is clear in each chart.
Make the Charts Small
When you make the charts on the Excel dashboard very clear and simple, you can reduce their size. The information will be easy to understand, even though the charts can be reduced to only about an inch wide. With small charts, clearly labeled and titled, you can arrange many charts on the Excel dashboard, without overwhelming the reader.
Highlight the Good News and the Bad News
With conditional formatting, you can use color to highlight numbers on the Excel dashboard that are well below or above average. For example, you could highlight a cell if it contains an amount greater than 5000. To add conditional formatting in Excel 2007:
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.
This will open a dialog box, where you can set the minimum amount (5000 in this example), and select the formatting that will highlight the cell. You could format the cell with a bright red fill color if it's over the set amount.
Keep It Simple
The most important thing to remember when creating an Excel dashboard is to keep it simple. Select only the most important data to display, and use one or two color to highlight the top and bottom results. Small, simple charts will add visual impact, and help clarify the data.
Deixe o seu comentario
Post a Comment