Home > Excel Solutions > 

An Excel 2003 & 2007 Tutorial

Reporting Periodic Data
From Excel Databases

Do you need to update your Excel reports with daily, weekly, or
monthly data? Here's a low-maintenance way to do it.

by Charley Kyd
January, 2008

Sometimes, the best way to solve your Excel problem is to redesign your workbook.

Ron G. brought this thought to mind with a recent question. He has a large spreadsheet with many columns of data, and also with intervening columns of formulas. Each day when he enters a new column of data he must move the columns of formulas that summarize his data. He also must change the formulas to adjust to the new data. He asked how to automate the process of moving and updating his formulas.

Ron is using a common Excel design. For example, many people enter columns of data each day or month, and then summarize it weekly or monthly in adjacent columns. These columns of calculations could return variances, growth rates, running totals, year-to-date balances, detailed statistics, and so on.

The "obvious" answer is VBA. After a lot of coding, Ron probably could set up Excel to do exactly what he wants. Unfortunately, there are at least two problems with this approach. First, Ron probably isn't a VBA expert. Second, this "solution" probably would require a lot of maintenance, because each time he needed to change his report he probably would need to change his code.

So if you have a challenge like this, I recommend that you redesign your workbook to avoid the problem in the first place. The general approach is to maintain one worksheet that contains only your data, and then to maintain one or more worksheets that report your data. In those reports, use formulas to display just the data you want.

The Data Worksheet

The first step is to set up a data worksheet, one that contains only data and no formulas. To illustrate, here's a design that I use frequently:

This figure contains four key range names.

1. Codes (A3:A7) These codes could be text or numbers that uniquely identify each row of data.

2. Products (B3:B7) This is a description column that provides information about each row of data. You could add other columns that contain other information about the items in each row, information like weight, price, color, vendor, and so on...depending on the type of data that the database contains.

3. Dates (C2:J2) The dates use Excel's date serial numbers and are formatted as you want. (The date format above is "mmm yy") The dates could be daily, weekly, monthly, or whatever. However, the dates should be in sequence, with the earliest date at the left.

Because of the approach we're taking, it's okay to have missing dates. For example, if you're tracking daily data it's okay for weekends and holidays to be missing from your database.

4. Data (C3:J7) The data area contains no formulas; it's all raw data.

To assign the first two range names, select the range A2:B7 and then launch the Create Names dialog by pressing Ctrl+Shift+F3. (Or, in Classic Excel, choose Insert, Name, Create. In New Excel, choose Formulas, Defined Names, Create from Selection.) Then, in the dialog, make sure that only Top row is checked, and then choose OK.

To assign the Dates range, select the range C2:J2 and then in:

• Classic Excel, press Ctrl+F3 to launch the Define Name dialog. (Or choose Insert, Name, Define.)
   
• New Excel, press Ctrl+Alt+F3 to launch the New Name dialog. (Or choose Formulas, Defined Names, Define Name, Define Name.)

In either dialog, enter the name Dates, then choose OK.

To assign the Data range, select the range C3:J7 and assign the name as above.

The Control Worksheet

The formulas in your report will rely on certain settings, like the starting date and ending date of your report. By putting these values in a Control sheet, you can change them easily.

Add a new worksheet to your workbook. To do so quickly, press Shift+F11. You also can right-click a tab of your workbook and choose Insert, Worksheet.

To remove the gridlines in Classic Excel, choose Tools, Options, View, and then uncheck Gridlines. In New Excel, choose View and then uncheck Gridlines in the Show/Hide group.

Enter the text shown in column A...except for the bold headings. Assign this text as range names in column B. To do so, select the range A2:B9; press Ctrl+Shift+F3; make sure that only Left column is checked; then choose OK. Now enter the bold text in cells A1 and A5.

Enter dates in cells B2 and B3, and format the dates as you wish. Then enter the following formulas in the cells shown:

B6:  =MATCH(StartDate,Dates,0)
B7:  =MATCH(EndDate,Dates,0)
B8:  =ROWS(Data)-1
B9:  =EndDateIndex-StartDateIndex+1

Now let's use these settings in a report.

The Report Worksheet

Because Ron's question concerned columns of calculations, I'll show a "report" that returns the same kind of information. This report is not something that most Excel users would distribute to others. But you can use similar techniques to provide data for good looking reports, including dashboards.

The report, shown twice below, displays the three rows of information from the three rows of data in the database. If you insert as many as three more rows in the database at the top of this article, and then enter some data and recalculate, this report would automatically display those new rows of data.

Here are the values and formulas for the cells shown:

E2:  =StartDateIndex
F2:  =IF(OR(E2=EndDateIndex,E2=""),"",E2+1)

Copy cell F2 to the right as needed.

This row controls the number of columns of data that appear in the report starting in cell E5. The formulas in row 2 count from the first date index to the last. After the last, the cells return a null string ("").

The labels in the range A5:D5 are text values. Enter them as shown. Also, assign the name CodeTop to cell B5. An error-checking formula will need to refer to this cell at the top of the Code column.

E5:  =StartDate
F5:  =IF(OR(E5=EndDate,E5=""),"",DATE(YEAR(E5),MONTH(E5)+1,1))

Copy cell F5 to the right as needed.

This formula merely displays the sequence of months from the starting month through the ending month, as specified in the Control sheet. Slightly different formulas could increment each period by day, by week, and so on.

A6:  2
A7:  =IF(OR(A6=NumRows,A6=""),"",A6+1)

Copy cell A7 to the range A8:A11.

This column of formulas counts from 2 to one plus the number of rows of data in the database. The other formulas in this report rely on the contents of column A.

B6:  =IF($A6="","",INDEX(Codes,$A6))
C6:  =IF($A6="","",INDEX(Products,$A6))

These formulas return information from the range names shown. If the corresponding cell in column A contains a null string (""), the formula also returns a null string. Copy the range B6:C6 to the range B7:B11.

D6:  =IF($A6="","",
          SUM(OFFSET(Data,$A6-1,StartDateIndex-1,1,NumCols)))

Enter this formula in one row in your formula bar, of course, and then copy it to the range D7:D11.

Here, the OFFSET function returns a reference to the corresponding row of cells in the Data worksheet, a range that begins with the beginning date and ends with the ending date. The SUM function returns the sum of the data in that range. 

You could replace SUM with other functions, like AVERAGE, STDEV, MEDIAN, and so on. You also could insert additional columns between columns D and E, and then set up other formulas that rely on data returned by the OFFSET function.

To be clear, column D and other columns that you might want to insert represent the columns of formulas that Ron currently needs to modify manually whenever he enters a new column of data. But with the approach used here, the columns of formulas adjust automatically to the date ranges specified in the Control sheet.

In one sense, the values in columns E through I aren't needed. This is because the formulas in column D are referring to the original data, not the data shown in the figure above. However, showing the data makes it easier to check your work and to print this simple report with all of its data displayed. But if you're only interested in the summary formulas, you could just skip columns E through I in this report.

E6:  =IF(OR($A6="",E$2=""),"",INDEX(Data,$A6,E$2))

Copy cell E6 to the range E6:I11.

This formula returns a null string if the corresponding cells in either row 2 or column A contain a null string; otherwise, this formula returns the value of the designated cell in the Data worksheet.

Check for Errors

Finally, it's always a good idea to set up error tests in the Control sheet, where you can think of potential errors.

For example, if you add more than three rows of data to the database, the Report sheet won't report all the data available. Therefore, I set up the formula in cell B12 of the Control sheet to return TRUE if you need to add more rows of formulas to the Report sheet.

B12:  =INDEX(Codes,NumRows)<>OFFSET(CodeTop,NumRows-1,0)

This formula merely compares the unique Code value in the last row of data in the Codes column with the equivalent value in the Report worksheet. If the two code values don't match, the formula returns TRUE. The most likely cause of this problem is that the Report sheet needs more rows of formulas.