Optimizing Spreadsheet for Pivot Table

N

Ned

Hello,

I am trying to create pivot tables from existing spreadsheets that track
staff labor hours. Yet, because these spreadsheets' cells of hours (in
effect, a single 'record') reference both the columns (individual staff
person) and rows (the tasks), MS Excel cannot create pivot tables (if I
understand this functionality well enough): Excel needs to have all that data
(i.e. a single 'record' with person, task, & hours) in a single row with the
appropriately identified columns headers to reference to build the pivot
table.

1) Am I correct in my assessment that Excel requires each 'record' of
hours/staff/task to be on a single row?

2) If #1 is correct, does Excel offer some functionality that transpose each
'record' into a single row?

Thank you kindly.
 
N

Ned

Thanks for the response and apologies for not providing an example
originally.

Excel cannot make pivot tables for data in its current state (simple example):
Week of 4/15/06
Staff1 Staff2 Staff3 Staff4
Task 1 2hrs 3hrs
Task 2 1hrs 4hrs
Task 3 2hrs 2hrs 2hrs 2hrs
Task 4 8hrs


Excel needs (correct?) the same data that is found above in the following
format to create Pivot Tables:

Staff 1 Task 1 2hrs
Staff 1 Task 3 2hrs
Staff 2 Task 1 3hrs
Staff 2 Task 2 1hrs
Staff 2 Task 3 2hrs
Staff 3 Task 3 2hrs
Staff 4 Task 2 4hrs
Staff 4 Task 3 2hrs
Staff 4 Task 3 8hrs

Does Excel offer an automated means to transform format from current state
(top) to required state for Pivot Tables (below)?

Thank you.
 
N

Ned

Tab spacing did not hold in last posting. So added '.' for spaces:
Excel cannot make pivot tables for data in its current state:

Week of 4/15/06
………………..Staff1..Staff2..Staff3..Staff4
Task 1………..2hrs......3hrs………………….
Task 2…………………...1hrs…………...4hrs
Task 3………..2hrs….2hrs…..2hrs…..2hrs
Task 4……………………....……………...8hrs


Excel needs (correct?) the same data that is found above in the following
format to create Pivot Tables:

Staff 1.……….Task 1………..2hrs
Staff 1.……….Task 3.……….2hrs
Staff 2.……….Task 1.……….3hrs
Staff 2.……….Task 2.……….1hrs
Staff 2.……….Task 3.……….2hrs
Staff 3.……….Task 3.……….2hrs
Staff 4.……….Task 2.……….4hrs
Staff 4.……….Task 3.……….2hrs
Staff 4.……….Task 3.……….8hrs

Does Excel offer an automated means to transform format from current state
(top) to required state for Pivot Tables (bottom)?
 
Top