Home >  Excel Solutions > 

An Excel 2003 Tutorial:

Show Key Stats Automatically
In Periodic Excel Reports

Here's a way to display miscellaneous facts and figures in your
Excel management reports, and update them automatically.

by Charley Kyd
April, 2007

(Follow this link for the Excel 2007 version.)

Each issue of Business Week magazine includes several figures titled "The Stat". These figures emphasize key data related to the article on the page where each figure appears. 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. (If you use an Excel-friendly OLAP, you could enter these items there, instead.)

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.

(You can download the workbook and the Key Stat examples using this link.)

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 Insert, Name, Define; 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 Define Name dialog.)

Format the rows and columns as shown in the figure, then enter the data shown above. 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. I copied this cell to the other two cells and then changed the dates. (You can choose Ctrl+1 to launch the Format Cells dialog.)
   
• 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. Using the "|" character is an unobtrusive way to do this.

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 the techniques described in Display Any Colors in Excel to format the colors for this figure. But for now, rather than dark green and tan, let's suppose you want to use blue and gray for your version of this figure:

1. Add a new worksheet to your workbook.

2. Select cell B2 and assign a blue color pattern. To do so, use the Fill Color tool on your Formatting Toolbar or choose Format, Cells, Patterns, and then choose the blue color.

3. Enter the text "stat1_" in cell B2. (You also could enter "Stat1_" because the case doesn't matter.) Then format the cell's font as blue. To do so, use the Font Color tool or choose Format, Cells, Font, and then choose the blue color. Doing so makes the text invisible, which is what we want. (You'll see how we use this text shortly.)

4. Select the range C2:E2 and assign a black color pattern. Enter "KEY STAT" in cell C2 and assign a white font to that cell. Format the font as bold. The example uses the Cambria 14 font, but you can use any font you want.

5. Select the range B3:D7. This range needs to be merged into one large cell to contain the large number. To do so, choose Format, Cells. In the Alignment tab, check Merge Cells, then choose OK.

6. The font in this cell is Cambria 40, Bold. You can use any font you want.

7. Select the range E3:I3. Merge this range into a large cell.

8. The font in this range is Calibri 14, Bold. Apply any font settings you want.

9. Select the range E4:I7. Merge this range as well.

10. The font for this range in the figure is Calibri 11.

11. Each merged range uses a formula to return data from the database. Enter these formulas for the ranges shown:

B3:   =INDEX(Data,MATCH(B2&"Data",StatData,0),ColNum)
E3:   =INDEX(Data,MATCH(B2&"Title",StatData,0),ColNum)
E4:   =INDEX(Data,MATCH(B2&"Desc",StatData,0),ColNum)

In cell B3, the reference to cell B2 returns "stat1_". When combined with "Data", the MATCH function searches for "stat1_Data" in the StatData range, and returns that label's row index number. The INDEX function returns the value in Data for that row, and for the appropriate column number. The other two formulas work similarly.

To copy this figure, just copy and paste it. The formulas in the new figure will point to the text in its top-left cell. To use this figure with the Stat2 dataset, merely enter "Stat2_" in the top-left cell of the figure, and then recalculate.

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 the worksheet with the first figure.

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, it's probably best if you create each figure in its own worksheet and then use Excel's Camera Tool to display them in a report sheet. The article Add Class to Your Reports With Excel Drawing Objects explains how to use this tool.

You can download the workbook and the Key Stat examples using this link.