×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Microsoft: Office FAQ

PivotTable Tips

Normalize your data using the PivotTable Wizard by SkipVought
Posted: 13 Nov 08

The Dilema

You have a sheet set up something like this...

Acct   Jan Feb Mar
Acct01 234 123 567
Acct02 345 234 678
Acct03 456 345 789

and you're having problems doing the stuff to it that you'd like to do.

That's because your data is NOT NORMALIZED.  You might want to check out this EXCELLENT link 'Fundamentals Of Relational Database Design'

Excel's PivotTable Wizard to the RESCUE!

1) Start the wizard -- Data/PivotTable & PivotChart Report...

2) Step 1 of 3 - Select Option Button: Multiple Consolidation Ranges -- [Next]

3) Step 2a of 3 - Select Option Button: I will create the page fields -- [Next]

4) Step 2b of 3 - With your cursor in the Range Textbox, select the data range on your sheet that you want to normalize -- [Add] -- [Next]

5) Step 3 of 3 - [Layout...]

6) Drag the Row and Column buttons OFF the Layout -- [OK] -- [Finish]

7) Mysteriously, you are on another sheet that has a 4-cell pivot table. Double click the BOTTOM RIGHT CELL

8) Again, on another sheet -- This is you data normalized, or at least closer to it.  You'll need to change headings at least.

Row     Column Value
Acct01  Jan     234
Acct01  Feb     123
Acct01  Mar     567
Acct02  Jan     345
Acct02  Feb     234
Acct02  Mar     678
Acct03  Jan     456
Acct03  Feb     345
Acct03  Mar     789

 

Back to Microsoft: Office FAQ Index
Back to Microsoft: Office Forum

My Archive

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close