Disappearing Pivot Table Fields

Henry

Board Regular
Joined
Mar 7, 2002
Messages
180
Question:

There's basically two ways of preparing databases pre-Pivot Table.

You can keep all your data points (sales figures) in the same Row and use the Column (Field) Heading then go straight to a PT. This keeps the number of Excel Rows at a minimum since all data points are in the same Row.
The other way is to transpose the Column Heading (Fields) of the data points so that all Data Point Column Headings (Fields) are in one column and the Data Points (sales Figures are in an adjacent new Column and a new database. This creates uniqueness.
Drawback to method two: If you have 11 data points in one Row, and you transpose the Field Headings, you now use up 11 Rows for each Product Name since each data point is in its'own unique Row. This fills up Excel real fast.

The first method, which is quick to construct and convert to a PT takes up minimal Excel Rows.

The second method, which PTs prefer, can use many, many more Rows, including quickly approaching 65k and requires a 2nd database.

Now, the main question: Using method one, when I go from the origihal data base to the PT, my Column Fields disappear when I deselect them. They are still on the Wizard, but I always have to go back and reinstall.

How do I get around this? Where do they go and why?

The second method, which is the cleanest, creates a second database, then converts to PT. The Fields are are always there but I can't do Calculations. PT likes this method but it pushes Excel specs and forces me to go to Access for data storage.

Yes, this is a long Post, but I need to understand how to get around these constraints, or fix my apparant lack of understanding.

Thanks ahead of time.

Henry (mtaylor2@satx.rr.com)
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
Henry, your data really should be in 1st Normal Form (See http://www.stuart.iit.edu/courses/im510/database/1NF.htm) -- what you describe as "the 2nd method". If 1NF results in more than 65,536 records you should be using Access (or some other database). I'm sorry to say, I can't reproduce your difficulty with "the 1st method" -- a listing with a repeating group.

Please understand that the justification for 1NF goes well beyond convenience. It allows you to make comparisions and do calculations that you couldn't do otherwise.
This message was edited by Mark W. on 2002-04-19 08:09
 
Upvote 0

Forum statistics

Threads
1,213,557
Messages
6,114,288
Members
448,563
Latest member
MushtaqAli

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top