Excel Timesheet Dashboard
In this tutorial I will demonstrate how you can create an Excel timesheet and dashboard that will allow you to keep track of the various daily components of your work.
I will also demonstrate how you can add a little Excel timesheet dashboard to give you some charts and basic information about your weekly work.
Download the Template
The template has been provided so that you can quickly work your way through this project. Those who experience problems with a project almost invariably are those who do not use the template or modify the template before completing the project. So I can assure you that you will be saving yourself both time and heartache if you use the template and follow the instructions on this webpage to complete the project before modifying it.
Dynamic Named Range
Add this dynamic named range into the Name Manager and make sure that you name it correctly.
If you need more information about creating dynamic named ranges then please click on the tutorial link below.
The name for our dynamic named range is "ProjectsList"
Static Named Ranges
Add 2 static named ranges. If these named ranges look long and complicated then watch the video to see how you can add them very simply.
To create these two static named ranges use the Ctrl and Sift keys to highlight the ranges and then type the name into the name box at the top left of the spreadsheet and press enter.
With the "ClearAll" named range remember to omit the columns with the formulas
Static named range called "ClearAll"
Static named range called "Combined"
Create as shown in video.
We need to add some data validation to the ranges that are listed below. This data validation will be in the form of a List and the Source for this list will be our dynamic named range "ProjectsList".
Again if you are uncertain as to the process in creating and copying this data validation and please make sure you watch the appropriate section of the video above.
Data Validation / List /Source=ProjectsList
In this section I have listed all of our formulas for this project. In many cases you will be able to add the formula to one cell than copy that formula and highlight the range to which it needs to be inserted and then paste the formulas. This is demonstrated simply in our video.
Totals for hours and days
copy to C7:C23
copy to C7:C23
Number of hours
copy to E27:E41,K27:K41,Q27:Q41,E46:E60,K46:K60,Q46:Q60,E65:E79
=B7 =B8 =B9 =B10 =B11 =B12 =B13 =B14 =B15 =B16
=C7 =C8 =C9 =C10 =C11 =C12 =C13 =C14 =C15 =C16
There are two macros that we need to add into the VBA editor. To open the VBA editor hold down the ALT key and then push F11.
You will notice a module called Timesheet. Double-click on that module and copy and paste the code below into the timesheet module.
This is the macro to combine the data
Dim NRow As Range
Dim c As Range
On Error GoTo errHandler:
Application.ScreenUpdating = False
For Each c In Range("Combined")
'find first cell to paste to
Set NRow = Cells(Rows.Count, 20).End(xlUp).Offset(1, 0)
'check for value
If c.Value <> "" Then
c.Range(Cells(1, 1), Cells(1, 5)).Copy
'cell.Range("A1:C1").Copy'alternative with range reference
'get location of next row
NRow = NRow
'move to next rowNext c
Application.CutCopyMode = False
On Error GoTo 0
'show error information in a messagebox
MsgBox "An Error has Occurred " & vbCrLf & _
"The error number is: " & Err.Number & vbCrLf & _
Err.Description & vbCrLf & "Please notify the administrator"
This is the macro to clear all the data.
Dim cDelete As VbMsgBoxResult
cDelete = MsgBox("Are you sure that you want to delete this training", _
vbYesNo + vbDefaultButton2, "Are you sure????")
If cDelete = vbYes ThenRange("ClearAll").ClearContents
Assigning the macros to the shapes
In the template notice there are two shapes one to clear all the data and the other to update all of the data.
To assign these macros to the shapes right click and choose Assign Macro, then select the appropriate macro and then click OK.
Creating our Charts
In the demonstration project shown in the video for our Excel timesheet dashboard I have added two charts, one in the shape of a barometer and the other a simple flat bar chart.
The bar chart is created from an summary table in the cell references I6:R7.
At the data for our barometer is a single cell which is the percentage of the week worked. The cell reference is I2.
Adjusting to suit your needs
No doubt that you want to adjust this basic project to suit your own personal needs. If you understand the concepts involved that will be simple. So why not I take the time to read through the code and to look into the formulas to understand what they are doing. This will improve your overall understanding of VBA and also of Excel formulas and that then make your adaptation of this project so much easier.