Using "pivot tables" in Excel 2003

A pivot table is a powerful data summarization tool in Microsoft Excel and other electronic spreadsheet programs. Among other functions, it can automatically sort, count, and total data stored in a spreadsheet and create a second table displaying the summarized data. With PivotTables, you can quickly turn rows of data, such as sales information or inventory figures, into summary reports. Once you have the summary, you can then change the format to create other reports or summaries.

To begin select the following rows and copy/paste them into Excel (use Right-click, paste special, text). Now you have the raw data.

Employee	Project	Date	Hours
JOE	AAA	1/1/2006	1
DAN	AAA	1/1/2006	5
JOHN	BBB	1/1/2006	2
JOE	AAA	1/2/2006	6
DAN	AAA	1/2/2006	4
BILL	BBB	1/2/2006	7
MICHAEL	CCC	1/2/2006	2
JOE	AAA	1/3/2006	6
DAN	AAA	1/3/2006	3
BILL	AAA	1/3/2006	1
MICHAEL	AAA	1/3/2006	4

With a bit of effort you can make a quick aggregation mentally and see, for example, how many hours were spent on each project. But what if there were 10000 or more rows? So let's try to do that using the pivot table feature:

• Select any cell in the datasheet. Try A2 for example.
• On main menu click "Data > PivotTable and PivotChart Report..."
• Leave the default settings for the first stept of the wizard and click "Next"
• In step 2 you should select your data ranges. If you use the data provided above the range should be "Sheet1!$A$2:$D$12". You can use the mouse to select it or enter the value in the dialog.
• Click "Next" then "Finish". The wizard result should look like this:
• Now, from the right panel drag "1" below the date value in the "Drop Data Items Here" panel. This will sumarize work hours.
  
  
• Next, drag "AAA" value on the "Total" cell below "Sum of 1". This will split hours by project.
  
  
• If you want to see also how many hours each employee worked on each project you can also drag "JOE" from the list between "Sum of 1 column" and "Total" column. This should be the final result of your work:
  
  

I hope this helps!