Author: Oscar Cronquist Article last updated on November 13, 2012

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 for 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

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 time sheet by project with overtime hours.

Download excel *.xlsx file

Monthly time card by project.xlsx