Home > Excel
for Business >
Weekly Excel Reporting
"I work for a small (private) company whose owners have
requested WEEKLY dashboards on various aspects of the business. Do
you have a sample spreadsheet that charts data by week that could
save me many hours of development?" -- Ed L.
Excel dashboards are merely good-looking worksheets that use small
charts and tables. Daily, weekly, and monthly reporting uses essentially
the same process, but with different date formulas.
take an example from my dashboard
ebook, which discusses reports about monthly data. This figure
displays Revenues for January 2001 through January 2002.
The following figure is a small section from the Excel worksheet
database with the monthly data that the chart displays. (I've hidden
rows and columns to show the key cells.) The name of the workbook is
And this figure is from the sheet in the dashboard workbook that
supports the chart shown above:
Here are some key formulas from this last figure:
Returns the date serial number for the current month, as entered in the
Control worksheet. (As I explain in the book, the Control worksheet
contains the key setup values that control your report or analysis.)
Returns the date serial number for the month before the month in cell
P7. The date cells to the left of this one work similarly.
Returns the column number where data for this date can be found in the
database (the first figure above) for January 2002.
Returns the row number where the sales data can be found in the
Returns the Sign (Debit = +1, Credit = -1) from the database.
Returns the specified value from the database. Because the value is
multiplied by its Sign, it's positive if the value has its natural
balance, otherwise it's negative.
Using Weekly or Daily Data
Notice in the formulas above that MATCH functions are merely looking
for matching dates. They don't care whether the dates represent months,
weeks, or days.
Therefore, you could enter properly labeled weekly data in the
database shown near the top of this page. Then, in the support worksheet
above (and below), you could enter this formula:
Here, the date serial number for the current week is the serial
number for the prior week, less 7 days. You would copy this formula to
the left, of course.
Of course, to report daily data from the workbook database, you
would merely enter this formula:
Again, copy it to the left.
Displaying the Dates
The following figure shows the range that your chart will reference.
Again, columns are hidden.
have several alternatives for displaying the label for the final month
in the chart:
This formula returns the number of the month, the carriage return
character, and the last two characters of the year. Copy cell P23 to
The carriage return character forces the year part of the label to
wrap to the second line in the X axis of the chart, as shown at the
This formula would have given you the same results:
Here, we use the TEXT function to return the date values. Either formula
The middle months in row 23 in the figure above only
display the month number. For daily or weekly reports, you would display
the day number rather than the month number.
Hope this helps,