WOOHOO Super-Cool Pivot Tables!!!

How to create a super-cool employee times pivot table in Microsoft Excel:
And now the moment you have all been waiting for…
I know, it isn’t what most people think of as super-cool, but I know most people don’t know how to do this on excel and I am over due to write a tutorial. To demonstrate I will be using examples from my Monday morning routine at work. Every week it is my job to sort out each employees’ hours, where they worked and if they worked over time. I spoke with another office assistant over the weekend, and discovered she does the same every other Wednesday, however she doesn’t have a super-cool method like mine. It seems to me that if you do it almost any other way you had better prepare yourself to pull out your hair.


Start with a labeled list.
Mine is labeled with the following information:
Month Week Ending Day Employee Name Project Name Hours Worked RT (Regular Time) OT (Over Time) Check Hours Days Worked


Now I will need a little explaination here on why I need so many catagories. In the office I work for our time cards are by the project, not the employee. Every time card usually has more than one employee, working different number of hours. I am trying to figure out how many hours an employee worked in a week. I have to sort out the time sheets by day, and then by project (because the foremen never leave them in order) prior to entering them. Once I have them entered in Excel I sort them by name. From there it is easier to calculate who has how many hours of overtime.

When I receive my time cards I type the information in the designated fields (i.e. name in the name, project name in the project name, etc.)
The “check hours” and the “Days Worked” automatically calculate themselves (I will explain why in a little bit).

Once I have done this, I sort the list by employee name. I add the hours and find who has worked over 40 hours. All hours which are for hourly rate are entered in to the “RT” and any hours over the 40 hour mark are entered in the “OT” column. The “Check Hours” column adds the OT and RT columns together to help me double check my math. The “Days Worked” column divides the hours worked column by eight for a man days report (which, again, I will explain later)

Now that everything is entered and sorted we do the easy part: the pivot table! Can I get a WOO, WOO!?!

Select all the information in your spreadsheet.

Go to Data: Pivot Table and Pivot Chart Report

Ta-Dum, Excel carries you through the process with a creation wizard.

Now, what can you do with said pivot table?

You can now take this table and drag Employee Name from the Pivot Table Field List (which is a list of all of your column headings) and drop it in to the field on the far left. Next drag the “Project Name” just to the right of the employee names. You can drag the date in to the top of the table and the RT and OT in to the big information field. Now we know what employee worked where and for how long. The Pivot table even adds up the total hours for us, isn’t that nice? Or, if you don’t want it to give you a total you can turn that option off in the Pivot Options menu.

Also, I make these databases for three months, with 19 employees that makes for a long list. What if I just want to see who worked where for how long on only the week of 5/25-5/31? We can now just drag and drop “Week Ending” in to the little title field up at the very top and now select 5/31/09.

If we want to see only work done in May, and not anything from April (even if it is from the same week) we can replace “Week Ending” with “Month” and voilla!

I hope you have as much fun with pivot tables as I always do (however unlikely that may be).