Show Key Stats Automatically in Periodic Excel Reports

Each issue of Business Week magazine used to include several figures titled “The Stat”. These figures emphasized key data related to the article on the page where each figure appeared. Many management reports could benefit from using a similar approach.

In my experience, Excel users in business are always coming across random pieces of information that managers would find useful. But often, this information doesn’t fit within any standard reporting structure.

What do you do, after all, when you discover one interesting number and have no convenient way to report it? Often, Excel users ignore the number, or “report” it by mentioning it to a passing manager in the hallway, or bury it in a sea of other data. None of these is a good idea.

Instead, you might consider adding “Key Stat” sections like the ones shown here to one or more of your standard Excel reports.

Not only do these figures provide a convenient way to report random facts, they can add a professional “look” to your ordinary Excel reports.

And even if you never plan to use figures like this, knowing how to do it could help you speed up and simplify your other management reports.

The Goal: Easy Updates

Above all, as Excel users, we want to be able to update our standard reports quickly and easily. We don’t want the Key Stat figures to become maintenance headaches.

Assuming that we’re talking about monthly reports, here’s how you would maintain your Key Stats:

1. Monthly, you enter the new stat, its title, and its short description in a Key Stats worksheet database.

2. You change the report date in one cell of your report.

3. You recalculate and print your new month’s report.

It’s that easy. So let’s see how to make this work…

Your Workbook Structure

At the minimum, your workbook report will have two worksheets. These will be named Control and Report.

Virtually every report I create has a Control sheet. In this sheet you enter dates and other information that control or affect your report.

Your workbook also will have a Report sheet. Ideally, you won’t need to modify this sheet for months on end. You’ll change the date in your Control sheet, recalculate, and print.

For this example, your workbook also will have a Data worksheet. Often, however, it makes sense to keep your data in a separate workbook. That way, several workbook reports easily can reference the same data.

The Data Worksheet

This figure shows the entire contents of the Data worksheet used for this example.

In practice, however, this little database could have any number of rows and columns of data.

Notice that the labels in the range B4:B6 begin with “Stat1” and the labels in the range B7:B9 begin with “Stat2”. Each such label refers to a different figure, discussed below.

Here are the range names and references used with this figure:

To create the first range name, select the range B3:B10; choose Formulas, Defined Names, Define Name; enter the name StatData; then choose OK. Take the same approach for the other two names.

(You also can choose Ctrl+F3 to launch the Name Manager dialog, then click on New to enter the new name.)

Format the rows and columns as shown in the figure, then enter the data shown above. (You can’t read most of the text in this figure. But I made up the text anyway, so enter any text you want.) Here are some things to watch out for:

●  The dates in row 2 are Excel data serial numbers. To illustrate, I entered the value in cell D2 as: 1/1/2007. Then I formatted the value as shown. To do so, choose Home, and the Number tab’s dialog launcher to display the Number tab of the Format Cells dialog. In the Type textbox, enter “mmm-yy” as a Custom format. (You also can choose Ctrl+1 to launch the Format Cells dialog.) I copied this cell to the other two cells and then changed the dates.

●  I entered the formatted values in cells E4, F4, and F7 as text, not as formatted numbers. This is because the formulas that use this data won’t recognize the cell formats. To enter the numbers as text, begin your entry with a single-quote (apostrophe) then enter $896, 59%, or $0 as shown.

●  The text in column F could extend far beyond the borders of their cells. That would make your database look very sloppy. So I used the approach that I often do in this circumstance. In the range G4:G9 I entered the “|” character, which you’ll probably find above the “\” character on your keyboard. Entering any character will serve as a fence to keep the text contained. But the “|” character is unobtrusive, and looks somewhat like a fence.

The Control Worksheet

The Control worksheet is very simple in this case. Cell B2 contains the only value that you will update monthly. In the figure, it contains the value 1/1/2007.

Cell B5 contains a formula that returns the column number within the database for the value entered for the CurMonth. Enter the formula for the cell shown:

B5:   =MATCH(CurMonth,Dates,0)

This formula finds the CurMonth value in the range named Dates (as explained in the previous section) and returns the column number for that date. The zero in the last argument tells Excel to find an exact match.

Because the formulas in the figures below all refer to the ColNum range, the figures automatically update when you change the CurMonth date in cell B2 above.

The Horizontal Figure

Here is the figure shown at the beginning of this article, with the row and column headings included:

I used a custom color theme to set the colors for the figure above. But for now, rather than dark green and gray tan, let’s suppose you want to use maroon:

You can do this by simply choosing Page Layout, Themes, Colors, and then choosing the color theme you like.

The Vertical Figure

This figure presents the data using a vertical format. I created it using a new worksheet in the same workbook, but you could create it in any worksheet you want.

Use the same approach to create this figure as the previous one. Here, however, enter the text “Stat2_” in cell B2.

Displaying the Figure

Whether you use the horizontal or vertical versions of these figures, they look best when their column widths are set carefully. Therefore, you should create each figure in an area of a worksheet where you can control row and column sizes, and then use Excel’s Camera Tool to display each figure in your report sheet. The article Add Class to Your Reports With Excel Drawing Objects explains how to use the Camera tool.