Create monthly time sheet using a Pivot Table
Today I am going to demonstrate how amazing pivot tables are! Take a look at this time sheet.
You can expand each month and see time spend on each project. The pivot table also shows a summary of both months and projects.
I made the pivot table from a simple table containing fake project time data.
This table can have duplicate dates, as you may be working on multiple projects in a single day.
How I created the Pivot Table
- Select a cell in your table.
- Go to tab "Insert".
- Press with left mouse button on "Pivot table" button.
- Press with left mouse button on OK.
Arrange data
- Press with left mouse button on and drag "Day" from "Pivot table Field List" to "Column Labels" area.
- Resize pivot table columns widths.
- Press with left mouse button on and drag "Date" to "Row Labels" area.
- Press with right mouse button on on a date and select "Group".
- Select months and press with left mouse button on OK.
- Press with left mouse button on and drag "Project" to "Row Labels" area.
- Press with left mouse button on and drag "Regular hours" to "Values" area.
- Go to tab "Design".
- Press with left mouse button on "Subtotals" button.
- Press with left mouse button on "Show all subtotals at bottom of group".
- Press with left mouse button on and drag overtime hours to "Values" area.
- Change headers "Sum of Regular hours" to R and "Sum of overtime hours" to O.
Picture of a monthly timesheet by project with overtime hours.
Recommended articles
Table category
This article demonstrates different ways to reference an Excel defined Table in a drop-down list and Conditional Formatting. There are […]
This article demonstrates two formulas that extract distinct values from a filtered Excel Table, one formula for Excel 365 subscribers […]
This article demonstrates a formula that counts unique distinct values filtered from an Excel defined Table. Debra Dalgleish described in […]
Pivot table category
In a previous post:Â How to create a dynamic pivot table and refresh automatically I demonstrated how to refresh a pivot […]
Sean asks: Basically, when I do a refresh of the data in the "pivotdata" worksheet, I need it to recognise […]
In this article, I am going to show you how to quickly change Pivot Table data source using a drop-down […]
Templates category
This article describes how to build a calendar showing all days in a chosen month with corresponding scheduled events. What's […]
I will in this article demonstrate a calendar that automatically highlights dates based on date ranges, the calendar populates names […]
Geoff asks: Hi Oscar, I have a cross reference table we use for shift scheduling. The x-axis is comprised of […]
How to use Excel Tables
2 Responses to “Create monthly time sheet using a Pivot Table”
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
Oscar,
I've been experimenting with various formats (in Excel ofcourse) for tracking my time, but have never been totally satisfied. This PivotTable might just be the ticket! Thanks for sharring!
Cheers, Lukas
Lukas,
I am happy you like it!