Home > Excel
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
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
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,
- 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
Enter dates in cells B2 and B3, and format the dates as you wish. Then enter the
following formulas in the cells shown:
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:
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.
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.
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.
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.
Enter this formula in one row in your formula bar, of course, and then copy it to the
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.
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.
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.