For business users of Microsoft Excel Free guides and templates
Home >  Excel Help Questions > 

An Excel Tutorial

Weekly Dashboard Reporting with Excel

When your Excel formulas get their data from an Excel data worksheet, it's easy to set up weekly dashboard or other reports in Excel.


by Charley Kyd, MBA
Microsoft Excel MVP, 2005-2014
The Father of Spreadsheet Dashboard Reports
"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.

Sample Excel chart figure.Let's 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 ActGL.xls.

Extract from the sample Excel worksheet database.

And this figure is from the sheet in the dashboard workbook that supports the chart shown above:

Sample from the chart Figure Data Support sheet in Excel.

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.

Area that the chart figure references.

Sample Excel chart figure.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.

Free Excel Dashboards

Click to see customer testimonials

Charley's SwipeFile charts