Disappearing Pivot Table Fields

Henry · Apr 19th, 2002, 08:56 AM

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)

Mark W. · Apr 19th, 2002, 09:07 AM

Henry, your data really should be in 1st Normal Form (See http://www.stuart.iit.edu/courses/im...tabase/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 ]

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Apr 19th, 2002, 08:56 AM	#1
Henry Board Regular Join Date: Mar 2002 Location: Mike T. Posts: 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)

Apr 19th, 2002, 09:07 AM	#2
Mark W. MrExcel MVP Join Date: Feb 2002 Location: Austin, Texas USA Posts: 11,654	Henry, your data really should be in 1st Normal Form (See http://www.stuart.iit.edu/courses/im...tabase/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 ]