Pivot tables

bartoni

Active Member
Joined
Jun 10, 2003
Messages
296
Hi,

Im new to pivot tables and i wonder if anyone can help with a simple query. Ive got a list of 500 prds in column A. In column B is a percentage. What i would like to do is put together a pivot table to show which products fall into which percentage ranges which i would have to define . I would like to view the tables at 10% increments so i envisge there being 10 columns beginning 0-10%, 11-20% etc and 500 rows. Is this possible and how would i do it?


Thanks
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

GorD

Well-known Member
Joined
Jan 17, 2004
Messages
1,446
Yes, you should be able to do that.

Pivot table would have
Row Field = product (Col A)
Coulumn Field = %age (Col B)

and data field would be count of %age


Then you need to click a cell on the Column field then right click, grroup and outline.

Then change the settings to start at 0, max 1 and by 0.1 increments.
 

bartoni

Active Member
Joined
Jun 10, 2003
Messages
296
Ive got 2 questions on that:

the first is, "and data field would be count of %age", the wizard only creates a sum of the %ages OR count of prds. Whcih one is correct.

And where you say

"Then you need to click a cell on the Column field then right click, grroup and outline"

Is this after the tables been created. The problem is, my figures are all different and Excel cannot create a Pivot table big enough to house all of the columns derived from each individual percentage value.
 

GorD

Well-known Member
Joined
Jan 17, 2004
Messages
1,446
If you want the count in each category then count of prds is the one.

In answer to number 2, create the pivot table first, if you don't have enough columns then you should get a message saying show as much as possible, then when you do the grouping the whole lot will display.
 

bartoni

Active Member
Joined
Jun 10, 2003
Messages
296
Hi,

Where do i find the part to group by increments. Ive grouped the columns but i cant find the increment part.

Thanks loads!!
 

GorD

Well-known Member
Joined
Jan 17, 2004
Messages
1,446
It should appear whenever you click group.

Your percentages are numbers and not text aren't they?



If they are text you won't be able to categorise numerically
 

Watch MrExcel Video

Forum statistics

Threads
1,129,752
Messages
5,638,157
Members
417,011
Latest member
Amaden95

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
Top