Author: Oscar Cronquist Article last updated on October 25, 2019

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

  1. Select a cell in your table.
  2. Go to tab "Insert".
  3. Click "Pivot table" button.
  4. Click OK.

Arrange data

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

Download Excel file


* You will also get a weekly newsletter, unsubscribe anytime!

Recommended articles