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".
- Click "Pivot table" button.
- Click OK.
- Click and drag "Day" from "Pivot table Field List" to "Column Labels" area.
Resize pivot table columns widths.
- Click and drag "Date" to "Row Labels" area.
Right-click on a date and select "Group".
- Select months and click OK.
- Click and drag "Project" to "Row Labels" area.
- Click and drag "Regular hours" to "Values" area.
- Go to tab "Design".
- Click "Subtotals" button.
Click "Show all subtotals at bottom of group".
- Click 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.