Time Tracking in Excel

The complete guide to building a simple time tracking solution for freelancers, consultants, and small teams.
If your needs are simple Excel can be a great fit for a starter time tracking solution. In this article we build such a solution step by step, and provide some advice along the way to help you achieve your time tracking goals.
Why do this in Excel?
You need to track time for your business so you can bill your clients or pay your people. There are a range of solutions available for this including paid products, and free options. If you are just starting out and on a budget - you might be looking at the free options. If you already own Microsoft Excel then a time tracking template in Excel might be a good approach.

In this article we will look at how you can build a simple spreadsheet to handle simple time tracking needs. The spreadsheet will also include a reporting capability so you can easily calculate staff pay and client billing.

Free Time Tracking Excel Template

In this article we show you how to build a time tracking solution in Microsoft Excel, step by step. Get a free ebook copy of this guide and the completed spreadsheet, ready to use.

Is time tracking in Excel right for you?
Before we go any further - lets make sure that Excel is a good option for your situation. If you answer yes to all of the following questions - then this approach can probably work for you.

  1. ARE YOU JUST STARTING OUT? The key point here is that if you have a lot of historical data that you want to manage then this might not be the best approach. But if you are just starting, or can live with your historical data in whatever form its in, then you are ok.
  2. IS YOUR TEAM SMALL? Maybe just yourself or at most one or two others? This approach won't work very well for larger teams.
  3. DO YOU HAVE A SMALL NUMBER OF CLIENTS? If you have more than a dozen clients, then this may not be for you. 
  4. DO YOU HAVE A SMALL NUMBER OF PROJECTS? Anything more than a handful and this solution will not feel comfortable to you.
  5. CAN YOUR TASK DESCRIPTIONS BE SIMPLE?  If you have to put dense and extremely detailed information on your client invoices, then this solution won't work well for you.
If you answered yes to all of these questions - you can probably do your time tracking in Excel. It has the benefit of being simple (and essentially free) to start. However - a word of warning - time tracking in Excel is not very scalable. If your business grows much in any of these areas - team size, number of clients, number of projects - you won’t want to keep using Excel for your time tracking. Before you make your decision consider the following pros and cons.

PROS

Simple to get started.

Flexible and relatively easy to customize for simple changes.

Free - if you already own Excel.

CONS

Does not scale well for many clients or projects.

Not ideal for multiple users.

A very manual solution.

Long term reporting and analytics are difficult.

Extra effort required to protect sensitive information such as billing or pay rates.

Requires all team members to license Excel.

A word about some of the cons listed above. Excel is an incredibly flexible and powerful tool - and there are multiple ways that any of the challenges listed as Cons above could be dealt with using Excel. Excel’s strength is that it is a generic multi-purpose tool that can be applied to virtually any information management need. However - its strength is also its weakness - you can make Excel do almost anything as long as you have the skill and time to program a custom solution - or are willing to pay someone to do that for you. However, for time tracking - once you get to that point, you are probably better off looking at a purpose built time tracking service.

And if you answered “no” to any of the above questions, it probably doesn’t make sense to do your time tracking in Excel. You probably need a commercial tool. There are many available, including our own tool - TimeSnap, which offers many features to take the pain away from time tracking, and dealing with your time tracking data. You can sign up for
a free trial easily - no credit card required. 

But for some teams - time tracking in Excel is still a very viable solution, as long as your needs are not too complicated, especially for very small firms and freelancers just getting started.

Building your own time tracking spreadsheet

So if you think the Excel solution is a good option for you, at least at this point, then let’s get started. The first step is to think just a little bit about how you are going to use your time tracking spreadsheet and what exactly you need to track. Do you need to track time for purposes of billing clients? For purposes of paying staff? Both? Do you need to capture task descriptions or notes that you can later put on your invoices? How are your rates structured? Do you charge a flat hourly rate or do you have different rates for different clients and projects?

What about your team? If you are solo this doesn’t really matter but if you have a small team of even two or three people, you have to consider not only how you will share these files - but also how you will structure files. You can have one spreadsheet per Client, one per Project, one per Team Member, or maybe one based on the time period - such as one per month, or even one or two per year, depending on your volume. The one or two spreadsheet per year scenario makes the most sense if you need to track both billable and payable hours, otherwise you’ll be doing some manual calculations across spreadsheets that you wouldn’t otherwise need to make.


For purposes of the spreadsheet we are going to put together in this article, we are assuming that we are tracking time for client billing (with task notes), for calculating staff pay, and that we charge different rates for different clients. Your situation may vary and you can adjust accordingly.  

So let’s fire up Excel and get started building our spreadsheet. In keeping with our desire for simplicity, we are going to keep this as straightforward as possible. Save your file using a name like “TimeSheet_Template” wherever you want on your computer - we’ll talk about sharing it with your team a little later. 

A simple solution with four worksheets
We will build a simple solution that allows entry of time, ability to track clients and their projects and rates, and staff and their rates, and finally the ability to selectively report this information. We will leverage Excel’s worksheet feature to neatly organize each of the areas of our solution. You can think of each worksheet as a tab, and the whole workbook file itself as “the solution.”
Right click on the default “Sheet 1” worksheet tab and use the rename function and give it the name “Time.” You can click on the small circle with a plus sign in it to add additional worksheet tabs as needed.
You’ll need to create four worksheets in the workbook named “Time,” “Clients,” “Staff,” and “Report.” The result should look like this.

The four worksheet tabs of our solution.

Setting up the Time sheet
Enter some column headers in the top of the “Time” worksheet, including “Date,” “Client,” “Project,” “Team,” “Hours,” “Notes.” It should look like this.

Column headers for the "Time" worksheet

The “Time” worksheet is where you and your team will enter your time tracking entries. We are going to format and size the columns a little to make it more attractive and also easier to use. At the same time we will format columns for data type too where it makes sense. 
If you know how to format columns in Excel, you can skip these steps. But if you need the information here are the step by step instructions for the general procedure on how to format columns.
1. Right-click on a column header - doing this on the column header selects all rows in that column. If you only want to format certain cells - you can highlight and select just those cells. In our scenario we are going to be formatting the whole column.
2. Select “Format Cells” from the pop-up context menu.
3. Select the data type and format you want
4. Select any other formatting options - and there are quite a few. If you are unfamiliar with these you may want to look at some of these options.
5. Finally click “OK” and your settings for the column or cells will be saved.
Using the Format Cells dialog you can set whatever formatting options you need. 

In this example we are setting a "short date" format.

For our “Time” worksheet we need to set the following format options.
1. For the “Date” column - select a short date format (such as 3/14/12)
2. Make the “Client” column a little wider.
3. Make the “Project” column wide enough to accomodate project names.
4. Staff Column - If you have any people with long last names in your team - make the “Staff” column wider.
5. For the “Hours’ column - see the discussion below on Hours formatting, but we will be using a 2 digit decimal setting.
6. Make the “Notes” column wide enough to make a brief comment.
7. Highlight all the column headers, make them bold, and give them a color background to help visually identify them as column headers.
Here is what our "Time" worksheet looks like now.
For readability - I also recommend you center-align your Date and Hours columns.
A word about hours formatting
Traditional hours and minutes format versus decimal format.
In terms of how you deal with hours formatting - you have basically two choices - you can represent hours and minutes, what I would call traditional time formatting, or you can represent hours using a decimal format.
Traditional time formatting for 2 hours and 30 minutes: 2:30


Decimal formatting for 2 hours and 30 minutes: 2.50
I prefer the decimal format as I find it is easier to work with, not only in Excel, but also in my head. You tell me which one is easier to add in your head on the fly?
2:30 + 1:15 or 2.50 + 1.25? 
Using decimal style hours notation is just plain simpler.
Even though we are building a spreadsheet to do your time tracking in - there are still plenty of scenarios where you will need to quickly calculate a sum of hours in your head. However - if you need to track time at the minute level for some reason (sometimes this can be a contract requirement), you may want to adopt the traditional hours:minutes formatting. Tracking exactly 7 minutes of time can be difficult using decimal hours notation. But for most professional services and consulting engagements I would recommend you track time in fifteen minute increments. Round up or down appropriately to get to your fifteen minute increments of time. And of course tracking time using blocks of 15 minutes is ideal for decimal hours notation.
Using decimal style hours notation is just plain simpler. And again we are striving for simple in this exercise - so we will be using decimal time notation.
To set the decimal formatting you’ll need in your spreadsheet, use the default settings for “Number” with two decimal places.

Setting the "Number" format with 2 decimal places.

Sample Time Entries
Now that our “Time” sheet is set up - lets put in some sample entries so we can see how this will be used. Simply fill in the date, the name or code of your client, the name or code of the project, the hours (in decimal format), and a brief comment.
The comments you use will be based mostly on what kind of information you’ll want to put on client invoices. Some clients will want very detailed information and some will want only a high level summary of hours and projects. If you are not sure what will work well for you, and have not discussed this with your client yet - I recommend to start with simple and very brief comments stating the artifact that you worked on - such as “new sign-up page wireframe.” Another consideration is your ability to review entries made by your team and be able to recognize what they worked on for your own project management purposes. Be sure to discuss with your team how you want your time entries done, and how granular the entries should be.
Here are some comment entries we’ll be using in our sample data. They represent my recommended approach for time entries.
“Wireframe revisions per client feedback”

“Concept document v2”

“Design review with client team” 

“CSS and layout changes for new blog” 

“Front end code for my favorites page” 
And here is how our sample entries look in our spreadsheet.

Sample Time Entries

The basic usage pattern is that you and your team record time spent on project tasks using the “Time” worksheet. We recommend you do this as you complete working for the day on each task, or at a minimum at the end of the day. It's’’ important to create your time entry while the work and the time you spent on it is fresh in your memory.
As we build our workbook one theme we should keep in mind is protecting sensitive information. In a time tracking scenario this usually involves keeping billing rate and staff pay rate information private. Fortunately Excel has ways of doing this and we’ll discuss how to do that in later sections of this article. However at the moment it is worth noting that the “Time” worksheet does not have any need for sensitive information protection.
Setting up the Client worksheet
Our “Client” worksheet is where we will manage our list of clients and projects, and their associated billing rates.
We’ll need four columns: “Client,” “Project,” “Description,” “Billing Rate.”
For formatting, you’ll want format the “Billing Rate” column to use Currency. You’ll also probably want to format the “Description” column to text with vertical alignment set top, and the option for wrapping text selected. This will give you a nice presentation if you end up adding a longer project description.  
Don’t forget to set appropriate column widths and also format the column headers. You’ll also probably want to right-align the “Billing Rate” column. Here is how your columns should look.

Headers in our Clients worksheet.

And here is the sample data that we will be using. Note that we’ve added gridlines and also set a color background for the Billing Rate column. More about these formatting choices in a moment.

Client sample data.

We are coloring the background of the Billing Rate column, and because of that color - we are also adding grid lines to make the information easier to read.
The background color for the Billing Rate column is a cue to help us remember that this information is private. When our team is working on project tasks and entering their time tracking entries - we don’t want them seeing billing rate information - as figuring out how much the company is making off their labor is probably something you do not want them distracted by.
Setting up the Staff worksheet
Our staff worksheet is very simple - its just a list of the names we will use in timesheet entries, along with their pay rates. So only two columns: Staff and Pay Rate.
We will format the headers, and assign the back color to the “Pay Rate” column, and also the grid lines. We will use three sample team members. Here is what our Staff sheet looks like.

Sample Staff Entries.

And again - we will hide the “Pay Rate” column, as we definitely do not want our team members seeing what other team members make. So we are giving the background color to the Pay Rate column as our visual reminder that this is a column we want hidden.  We will explain how to hide information later in this article.
Setting up the Reports worksheet
So far you have a spreadsheet solution where you and your staff can record time entries, and that is great. However - it's not very useful to only record time entries. You need the ability to extract information from your spreadsheet so that you can do your billing, pay your staff, and keep an eye on the hours you are burning on projects. 
This is where the Reports worksheet comes in. We are going to build functionality in the Reports worksheet that will let you filter, calculate, and report the the basic information you need from a time tracking system. Excel is one of the best calculators ever invented and it is more than up to the task of calculating your timesheet entries.

Our Reports worksheet layout.

A brief note on Excel functionality. This is where we are going to get at least a little bit “fancy” and have Excel do some dynamic calculations and data filtering for us. I want to reiterate that there are many ways of having Excel do this kind of thing, including some very complicated ways. The example seen here is only one way. We are striving for simplicity - but having Excel do a little bit of calculations for us gives us the basic value we need from a time tracking solution. 
In our solution we are going to use a little bit of what is called VBA code to have Excel do the filtering and some of the calculations for us. If you have never done any VBA code in Excel - or have no idea what I’m talking about - fear not! We give you very simple step by step instructions and you can simply copy and paste the code in - you do not have to know or understand anything about coding in VBA to pull this off. I promise. However - if you want to learn more about the powerful things you can do in Excel with VBA code - see some of the links in the Resources section at the end of this article.
But first - let’s set up how we want the Reports tab to look. In the upper left, make an area where we will be able to enter filter values. We want to be able to enter values for Start Date, End Date, Client, Project, and Staff. We also want a header called “Filters.”
You can highlight the cells A1 through E1 and then press the Merge & Center button, on the Home tab of the ribbon. Then type “Filters” in the merged cell.

Using Excel's Merge and Center feature.

Now in cells A2 through E2 - put the headers for each filter value: Start Date, End Date, Client, Project, Staff. Bold these values and give them a colored background. Then apply grid lines to the whole filters area. It should look like this.

Reporting filter headers.

Go ahead and use the Format Cells command to give B5 a number format with 2 decimal places. Cells B6 and B7 should be formatted as currency.
The final piece of layout for the Reports worksheet that we have to build is the headers for the report data. In row 9 enter and style the following headers: Date, Client, Project, Staff, Hours, Notes, Bill Rate, Bill Amount, Pay Rate, Pay Amount. The result should look like this.

The four worksheet tabs of our solution.

Report Filtering and Calculations
Now that we have our Report worksheet layout in place, we’ll begin to take care of the calculations and report filtering logic. DO NOT BE AFRAID - this isn’t as bad as you might expect. Stick with me - I promise you’ll get through it.
Let’s enter the formulas that will calculate the totals. These formulas are simple “sum” calculations that add up the values for a range of cells. Follow these steps.
1. In Cell B5 - enter the following for the cell’s value “=SUM(E10:E9999)”.
2. In Cell B6 - enter the following for the cell’s value “=SUM(H10:H9999)”.
3. In Cell B7 - enter the following for the cell’s value “=SUM(J10:J9999)”.
After entering these three formula values - you should get zeroes in those cells, as there is no data currently listed in the report output rows. Each of these is adding up the values in the specified column (such as “E”) between rows 10 and 9999. We use 9999 in order to make sure we always add up all values entered, with the assumption that we will never enter more than 9,999 rows of data in the same spreadsheet.
Now comes the “hard” part as we use some VBA code. But it’s really not that bad, you’ll see. The code is done for you - you just have to cut and paste.
In order to use VBA in an Excel workbook - you have to save the file as “macro enabled” format. To do this - simply go to the File menu, and select “Save As” - then in the drop down below the file name - select “Excel Macro-Enabled Workbook (*.xlsm)” from the drop down list. Click Save.

Save As Excel Macro-Enabled Workbook (*.xlsm)

Next locate the “Developer” tab in the Excel ribbon bar, and click it. Inside the “Developer” tab - find and click the “Visual Basic” button - it should be the first one on the left.

Click "Visual Basic" in the "Developer" tab of the Ribbon Bar.

Adding the VBA code
When you click this button - you will see a whole new program open - called “Microsoft Visual Basic for Applications” - and you’ll see in the left side of this program a tree structure that shows each worksheet in your workbook. It will look something like this. This is also known as the VBA IDE (which means, if you are curious, Visual Basic for Applications Integrated Development Environment).

Excel VBA Code Editor.

To add the VBA code that we need, follow this simple procedure.
1. Copy the VBA code shown below in the section called “VBA Code for Time Tracking”. Just copy all the text you see between '=== begin copy"  and "===end copy" .
2. Back in the VBA IDE right click on the item in the upper left that says “Sheet4 (Report)” and select the “View Code” option. A blank code window for Sheet4 will open.
3. Paste the code that you copied into the code window for Sheet4.
After following the steps - the VBA IDE should look like the below. Please make sure you save your work by clicking the little save button. You do not need to pick a file location when saving - the code is saved as part of your spreadsheet.

Click the Save button to save the code you added into the spreadsheet.

VBA Code for Time Tracking - this is the code to copy
=== begin copy
Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("A3:E3")) Is Nothing Then
        CopyToReport
    End If
End Sub
Private Sub CopyToReport()
    Dim cli As String
    Dim prj As String
    Dim stf As String
    'clear the report area
    Me.Range("A10:J9999").ClearContents
           
   
    'get the criteria
    dteStart = Cells(3, 1)
    dteEnd = Cells(3, 2)
    client = LCase(Trim(Cells(3, 3)))
    project = LCase(Trim(Cells(3, 4)))
    staff = LCase(Trim(Cells(3, 5)))
   
    'test the date values
    If Len(Trim(dteStart)) = 0 Then dteStart = #1/1/1900#
    If Len(Trim(dteEnd)) = 0 Then dteEnd = #12/31/3000#
   
    If IsDate(dteStart) = False Then
        MsgBox "Start date is not valid. Please enter a valid start date.", vbCritical + vbOKOnly, "Bad Start Date"
        Exit Sub
    End If
    If IsDate(dteEnd) = False Then
        MsgBox "End date is not valid. Please enter a valid end date.", vbCritical + vbOKOnly, "Bad End Date"
        Exit Sub
    End If
   
    'now loop thru the time entries on sheet 1 and copy them to sheet 4
    'if they match the filter criteria
    Sheets("Report").Select
    nextRow = 10 'starting row value for copying into report output area
    For i = 2 To 9999
        dte = Sheets("Time").Cells(i, 1)
        If IsDate(dte) Then
            If dte >= dteStart And dte <= dteEnd Then
                cli = LCase(Trim(Sheets("Time").Cells(i, 2)))
                If (client = "") Or (client = cli) Then
                    prj = LCase(Trim(Sheets("Time").Cells(i, 3)))
                    If (project = "") Or (project = prj) Then
                        stf = LCase(Trim(Sheets("Time").Cells(i, 4)))
                        If (staff = "") Or (staff = stf) Then
                            'passed all criteria checks, so copy the row to report output area
                            'Sheets("Time").Cells(i, 1).Resize(1, 6).Copy
                            For col = 1 To 6
                                Sheets("Report").Cells(nextRow, col).Value = Sheets("Time").Cells(i, col).Value
                            Next col
                            'populate the calculated values
                            Sheets("Report").Cells(nextRow, 7).Value = GetBillingRate(cli, prj)
                            Sheets("Report").Cells(nextRow, 8).Value = (Sheets("Report").Cells(nextRow, 5).Value * Sheets("Report").Cells(nextRow, 7).Value)
                            Sheets("Report").Cells(nextRow, 9).Value = GetPayRate(stf)
                            Sheets("Report").Cells(nextRow, 10).Value = (Sheets("Report").Cells(nextRow, 5).Value * Sheets("Report").Cells(nextRow, 9).Value)
                            nextRow = nextRow + 1
                        End If
                    End If
                End If
            End If
        End If
    Next i
           
End Sub
Private Function GetBillingRate(client As String, project As String) As Currency
    Dim vReturn As Variant
    Dim v As Variant
    Dim cli As String
    Dim prj As String
    v = Application.Evaluate("Clients!$A$2:$D$9999")
    For i = 1 To UBound(v)
        cli = Trim(LCase(v(i, 1)))
        prj = Trim(LCase(v(i, 2)))
        If LCase(client) = cli And project = prj Then
            vReturn = v(i, 4)
            Exit For
        End If
    Next
    GetBillingRate = vReturn
End Function
Private Function GetPayRate(staff As String) As Currency
    Dim vReturn As Variant
    Dim v As Variant
    Dim stf As String
    v = Application.Evaluate("Staff!$A$2:$B$9999")
    For i = 1 To UBound(v)
        stf = Trim(LCase(v(i, 1)))
        If LCase(staff) = stf Then
            vReturn = v(i, 2)
            Exit For
        End If
    Next
    GetPayRate = vReturn
End Function
'=== end copy

How the code works

If you have no interest in how this code works - you can skip this section. However - for those who are curious here is a brief explanation. There are two subroutines and two functions defined in the code. They work together as follows.

1. Worksheet_Change - this subroutine detects when changes happen to the area where     filters can be entered, and if any values have been entered calls the CopyToReport     subroutine.

2. The CopyToReport subroutine does the following:
        a. Clears out any previous reporting results
        b. Gets clean copies of any filter values entered
        c. Loops through each row of data entered in the Time worksheet - and compares             that entry to the filters.
        d. If the row of data matches the filters - it is copied to the Report worksheet output              area (starting at row 10).
3. The GetBillingRate function determines what billing rate to use in the reporting     output. It takes a client name, and a project name, and uses that to look up the billing     rate in the Clients worksheet. The CopyToReport subroutine calls the GetBillingRate     function as it is copying each line that matches the filters, then uses that to populate     the billing rate, and calculate the total billing amount for that time entry.
4. The GetPayRate function takes the staff person’s name and looks up their pay rate in     the Staff worksheet. It is called by the CopyToReport subroutine as each row is     populated in the output, and uses the result to populate the pay rate value and then     calculate the pay amount value.

When you are done looking at the code you can close the VBA IDE. The code is actually saved inside the spreadsheet file, so you will not lose anything as long as you have clicked the save button. If you ever need to tweak the code for some reason - you can go back in by clicking on the Developer tab and then the Visual Basic button again.

Trying out your time tracking report
If you have entered the sample data that we suggested, you can try out your reporting worksheet right away. 

You can filter your report data by Date Range, Client, Project, and Staff. All of these filter values are optional. If you leave the date range blank, it will select all data in the time sheet. But if you do enter values in Start Date and End Date - they have to be valid dates or you’ll get an error message. 

Anything you enter for Client, or Project, or Staff - has to match the values as shown in the Clients and Staff worksheets. And also - in the Time worksheet entries - these values must match what is in the Clients and Staff worksheets. Everything must match for these worksheets to work together.

The filtering behavior is flexible. You can enter any combination of the filters that you would like. For example you can see all the entries for a given staff person for a given project for a given client. Or you can see all the entries for a specific client, regardless of project or staff member.

Filtering time entries for a particular client.

Keeping rate information private
Excel has a built in capability to prevent certain information from being seen. This is good, because we do not want staff worrying about billing rates, or pay rates for other team members, or the reporting totals, so we will use Excel’s cell protection features to hide this information. This feature lets us select certain columns or rows and hide them from prying eyes. 
These protection features are based on using a password. Only by entering the password can you see the cells that are protected. For our spreadsheet we will be using “MyEyesOnly” as our password. Obviously you should select your own password. Like all good passwords - it should be easy for you to remember, and hard for someone else to guess.
So let's set up data protection for our “Billing Rate” column in our Clients worksheet. Excel manages this feature in multiple places - at the cell level and the worksheet level, and also the workbook level. By default cells are “locked” - but this only applies if you choose to protect the worksheet. So our first step is to set the Client, Project and Description columns to be unlocked. 
1. Click on the Clients worksheet tab
2. Click on column header for A and drag to the right through to the C column header, highlighting all cells in A, B, and C columns.
3. Right click on the highlighted columns and choose “Format Cells” and then go to the “Protection” tab.
4. Clear the check mark in the “Locked” checkbox - because we want our team to have normal access to these columns. Click OK.

Clearing the "Locked" checkbox.

5. Now click on the “Billing Rate” column - and select “Hide” - you will see it disappear (don’t worry you can get it back whenever you need it).
Now that the columns we do not need to hide are “unlocked”, and the “billing rate” column is hidden - we need to enable protection of the worksheet itself.
1. Click on the Review tab
2. Then click on the “Protect Sheet” button
3. Enter the password you want to use (we are using “MyEyesOnly” in our example)
4. Leave the default checkmarks in place
5. Click OK and then confirm your  password by entering it again

Click "Protect Sheet" in the "Review" tab of the Ribbon Bar.

Enter your password to protect the sheet.

Now users of the spreadsheet cannot unhide that column. They will have to “Unprotect” the worksheet first. And to do that - they will have to know the password.
Your team will need to refer to the Clients worksheet, so that they can see the client and project names to enter in their timesheet, but they will not be able to see the Billing Rates. Here is what they will see.

Clients worksheet with Billing Rate column hidden.

We also need to hide the Pay Rate column in the Staff worksheet. You probably do not want your staff to have access to the rates that other staff members make.The summarized steps for that procedure again are as follows:
1. Unlock column A - the “Staff” column using Format Cells / Protection.
2. Hide column B - the “Pay Rate” column, with right click / hide.
3. Protect the sheet - Ribbon bar - Review tab / then Protect Sheet button
4. Enter the same password, click OK and confirm the password.
This leaves a very simple looking list of team member names.

Staff worksheet with hidden Pay Rate column.

Securing the Reporting Worksheet
For the Report worksheet - you probably will also want to hide the billable and payable information. You have a choice here - you can either hide the cells that contain those values, or you can hide the whole Report worksheet. I recommend hiding the whole Report worksheet, and here is how you can do that.
1. Right click on the “Report” worksheet tab - and select Tab Color and then choose the same color value you have used for background colors on other elements you have hidden. Again - this is a visual cue to you that this is info you want hidden.
2. Right click on the “Report” tab again - and select “Hide” - the whole tab should disappear. Don’t worry you can get it back by clicking on any tab and selecting Unhide.

The Report worksheet tab with colored background.

Because you have hidden a whole worksheet - you now need to protect the spreadsheet at the workbook level. To do this - perform these steps.
1. Click “Review” in the Excel ribbon bar, then the “Protect Workbook” button.
2. Enter the password you are using (we are using “MyEyesOnly” in our example), click OK
3. Enter the password again to confirm, click OK.

Click the "Protect Worksheet" button in the "Review" ribbon.

Now none of the hidden elements, including the Report worksheet, can be unhidden without entering the password.
I recommend protecting the Clients and Staff worksheets at the worksheet level, and also protecting the Report worksheet at the workbook level because most of the time you will only need to unprotect the workbook to run a report. And doing that leaves the Clients and Staff worksheets protected. You won’t have to add new staff or new clients and projects as often as you will need to run reports, so this works out well.
When you need to run a report you will need to temporarily unprotect the workbook. To do this - follow these steps:
1. Click on the “Review” tab in the ribbon bar, and then click the “Protect Workbook” button.
2. Enter your password.
3. Right Click on any worksheet tab at the bottom of the Excel window and select “Unhide”
4. Select your “Report” tab in the Unhide sheet dialog, and click OK.

Unhide the Report worksheet.

If you need to change billing or pay rates in either the Clients worksheet or the Staff worksheet, the process is similar but at the worksheet level.
1. Click the tab for the worksheet that you need to edit, for example “Clients”
2. Click the Review tab and then the “Protect Sheet” button - enter your password.
3. Drag in the Excel column headers across where the hidden column is - for example in “Clients” - drag to highlight from column C through column E. This selects column D even though it is hidden.
4. Right click and select “Unhide” and your hidden column is now visible.
Caution - please remember to protect anything that you unprotected when you are finished and before your team starts using the spreadsheet again.
Putting your Excel based time tracking solution to work
Getting Real. Save the file into a new name, maybe something like “Time_blank.xlsm”. Remove the sample data and build out your real list of clients, projects and staff, billing and pay rates. Keep this file as a template that you can just use to Save As and create a new file. You may want to mark this file as read only and probably make a backup copy somewhere.
How many files? Consider how often you’ll make a new file. Maybe a new one for every couple months, or if you are not tracking entries too granularly then maybe two files per year. Think about billing and pay periods and make sure you can easily accommodate that. For example if you pay every two weeks - monthly files might be a problem as you’ll have some part of the pay period in one file and some part of the pay period in a second file. You’ll need to avoid that sort of thing to keep your sanity. Start your first file by doing save as with a new name such as “2018Time01.xlsm” or whatever convention makes sense for you.
Sharing the file. Decide how to share the excel file within your team. Everyone will need easy access to the file. If you are co-located then you can simply share the file on the network. If you do not share workspace then you have such as Dropbox, OneDrive, Google Drive or other file sharing services. Just keep in mind that only one person can use the file at the same time. 
Instructions for your team. Take some time to put together brief instructions for your team, particularly around how granular you want entries to be made, and any special requirements particular clients have. Perhaps make some entries in an example file so they can see what you want.
Review and Go Live. After everything is set - review everything once more - then roll it out to your team and you can “go live” with your new home grown Excel based time tracking solution!
Good luck with your time tracking!
Best of luck with your time tracking efforts. If you have any questions or need help - you can contact us and we'll do our best to help.
And finally, if you would like a free copy of the Excel file we created in this article - you can get that, and a PDF E-Book copy of this article by providing your email address below. We’ll also send you an update about any new articles we post.  

Free Time Tracking Excel Template

In this article we show you how to build a time tracking solution in Microsoft Excel, step by step. Get a free ebook copy of this guide and the completed spreadsheet, ready to use.

Share this page

Article Resources

Data Protection and Privacy in Excel
        
How to lock Cells and Protect Worksheets in Excel, by Ted French

How to Hide Excel Formulas and Protect Your Worksheet, by Christy Perry

Learning Excel VBA

Probably one of the best beginning VBA tutorials out there - Guru99 VBA

This is a great resource site for Excel VBA - Chandoo's Blog

And if you want to be blown away with some of the amazing things that can be done in Excel - check out this blog: Excel Hero

We are going to keep this resource section limited, and only focused on topics relevant to this article. But if you are the owner of a high quality resource that is relevant to this article you can contact us and we'll consider including a link.

© Copyright 2018 Snap Data & Logic, Inc. - All Rights Reserved