“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.
Ed,
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.
Let’s take this example, 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 ActGL.xls.
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:
P7: =CurMonth
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.)
O7: =DATE(YEAR(P7),MONTH(P7)-1,1)
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.
P10: =MATCH(CurMonth,ActGL.xls!Date,0)
Returns the column number where data for this date can be found in the database (the first figure above) for January 2002.
B11: =MATCH(A11,ActGL.xls!Name,0)
Returns the row number where the sales data can be found in the database.
C11: =INDEX(ActGL.xls!Sign,B11)
Returns the Sign (Debit = +1, Credit = -1) from the database.
P11: =INDEX(ActGL.xls!DataMonth,$B11,P$10)*$C11
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:
O7: =P7-7
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:
O7: =P7-1
Again, copy it to the left.
Displaying the Dates
The following figure shows the range that your chart will reference. Again, columns are hidden.
You have several alternatives for displaying the label for the final month in the chart:
P23: =MONTH(P$7)&CHAR(13)&RIGHT(YEAR(P$7),2)
This formula returns the number of the month, the carriage return character, and the last two characters of the year. Copy cell P23 to cell D23.
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 right.
This formula would have given you the same results:
P23: =TEXT(P$7,”d”)&CHAR(13)&TEXT(P$7,”mmm”)
Here, we use the TEXT function to return the date values. Either formula will work.
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.
