How do i sort and count data into categories using a pivot table?
 

Let's say i have this data to analyze:

Company #of employees State Industry

aaa 326 CA Industry A
bbb 1020 CA Industry C
ccc 575 NY Industry D
ddd 12000 VT Industry C

I would like to create a pivot table that summaries data this way:

# of employees
0-100 0
100-1000 2
1000-10000 1
10000-100000 1

or sort and count by brackets of 50 (# of employees):

0-50 0
50-100 0
100-150 0
etc..

Does anyone know how to do this with pivot tables? I know this can be done
because i've seen it done but can't remember how!!!! : (

How do i sort and count data into categories using a pivot table?
 

I can get it by exact intervals. (i.e.- 100-200, 200-300, 300-400, etc.)
Create your pivot, right click in the Row field.
sleect Group and outline, then Group
starting at 0, end at, say 200, interval 50.
This will auto-sort by the Row criteria.

"munchkin" wrote:

Let's say i have this data to analyze:

Company #of employees State Industry

aaa 326 CA Industry A
bbb 1020 CA Industry C
ccc 575 NY Industry D
ddd 12000 VT Industry C

I would like to create a pivot table that summaries data this way:

# of employees
0-100 0
100-1000 2
1000-10000 1
10000-100000 1

or sort and count by brackets of 50 (# of employees):

0-50 0
50-100 0
100-150 0
etc..

Does anyone know how to do this with pivot tables? I know this can be done
because i've seen it done but can't remember how!!!! : (

How do i sort and count data into categories using a pivot tab
 

Thanks for your reply.

Problem is when i right click anywhere on the pivot table, all i have as
option is "group and show detail" and then if i click on "group", it cannot
group the selection. Am i clicking in the wrong place? (i tried clicking just
about everywhere on the pivot table)..
by the way i have Excel 2003..

"Sean Timmons" wrote:

I can get it by exact intervals. (i.e.- 100-200, 200-300, 300-400, etc.)
Create your pivot, right click in the Row field.
sleect Group and outline, then Group
starting at 0, end at, say 200, interval 50.
This will auto-sort by the Row criteria.

"munchkin" wrote:

Let's say i have this data to analyze:

Company #of employees State Industry

aaa 326 CA Industry A
bbb 1020 CA Industry C
ccc 575 NY Industry D
ddd 12000 VT Industry C

I would like to create a pivot table that summaries data this way:

# of employees
0-100 0
100-1000 2
1000-10000 1
10000-100000 1

or sort and count by brackets of 50 (# of employees):

0-50 0
50-100 0
100-150 0
etc..

Does anyone know how to do this with pivot tables? I know this can be done
because i've seen it done but can't remember how!!!! : (

How do i sort and count data into categories using a pivot tab
 

When you have the pivot, the left column should be the one with values in it.
So:

Values
1
4
7
9
12

etc.

If you right click on one of the numbers, select Group and Outline, then
Group...

It should allow you to group as below. If the rows are not numbers, it won't
allow the group.



"munchkin" wrote:

Thanks for your reply.

Problem is when i right click anywhere on the pivot table, all i have as
option is "group and show detail" and then if i click on "group", it cannot
group the selection. Am i clicking in the wrong place? (i tried clicking just
about everywhere on the pivot table)..
by the way i have Excel 2003..

"Sean Timmons" wrote:

I can get it by exact intervals. (i.e.- 100-200, 200-300, 300-400, etc.)
Create your pivot, right click in the Row field.
sleect Group and outline, then Group
starting at 0, end at, say 200, interval 50.
This will auto-sort by the Row criteria.

"munchkin" wrote:

Let's say i have this data to analyze:

Company #of employees State Industry

aaa 326 CA Industry A
bbb 1020 CA Industry C
ccc 575 NY Industry D
ddd 12000 VT Industry C

I would like to create a pivot table that summaries data this way:

# of employees
0-100 0
100-1000 2
1000-10000 1
10000-100000 1

or sort and count by brackets of 50 (# of employees):

0-50 0
50-100 0
100-150 0
etc..

Does anyone know how to do this with pivot tables? I know this can be done
because i've seen it done but can't remember how!!!! : (

How do i sort and count data into categories using a pivot tab
 

OK here's what happens when i try to group:

when i click anywhere on the left column, it does not group.
however, when i select several cells on the left column, it groups only
those i have selected and:
- does not ask me any details about how i want to group them (by 10s, by 100
etc)
- does not sum up the total counts for that group

Perhaps this is because i have an older version of Excel?? My menu says
"Group and show details" and not "Group and Outline" like yours.




"Sean Timmons" wrote:

When you have the pivot, the left column should be the one with values in it.
So:

Values
1
4
7
9
12

etc.

If you right click on one of the numbers, select Group and Outline, then
Group...

It should allow you to group as below. If the rows are not numbers, it won't
allow the group.



"munchkin" wrote:

Thanks for your reply.

Problem is when i right click anywhere on the pivot table, all i have as
option is "group and show detail" and then if i click on "group", it cannot
group the selection. Am i clicking in the wrong place? (i tried clicking just
about everywhere on the pivot table)..
by the way i have Excel 2003..

"Sean Timmons" wrote:

I can get it by exact intervals. (i.e.- 100-200, 200-300, 300-400, etc.)
Create your pivot, right click in the Row field.
sleect Group and outline, then Group
starting at 0, end at, say 200, interval 50.
This will auto-sort by the Row criteria.

"munchkin" wrote:

Let's say i have this data to analyze:

Company #of employees State Industry

aaa 326 CA Industry A
bbb 1020 CA Industry C
ccc 575 NY Industry D
ddd 12000 VT Industry C

I would like to create a pivot table that summaries data this way:

# of employees
0-100 0
100-1000 2
1000-10000 1
10000-100000 1

or sort and count by brackets of 50 (# of employees):

0-50 0
50-100 0
100-150 0
etc..

Does anyone know how to do this with pivot tables? I know this can be done
because i've seen it done but can't remember how!!!! : (

How do i sort and count data into categories using a pivot tab
 

I have Excel 2000, so it wouldn't be that.

If I select a partial set of cells, it will also group only the selection
for me, so that's the same.

Any chance I can get a subset of the data you are attempting to pivot?

"munchkin" wrote:

OK here's what happens when i try to group:

when i click anywhere on the left column, it does not group.
however, when i select several cells on the left column, it groups only
those i have selected and:
- does not ask me any details about how i want to group them (by 10s, by 100
etc)
- does not sum up the total counts for that group

Perhaps this is because i have an older version of Excel?? My menu says
"Group and show details" and not "Group and Outline" like yours.




"Sean Timmons" wrote:

When you have the pivot, the left column should be the one with values in it.
So:

Values
1
4
7
9
12

etc.

If you right click on one of the numbers, select Group and Outline, then
Group...

It should allow you to group as below. If the rows are not numbers, it won't
allow the group.



"munchkin" wrote:

Thanks for your reply.

Problem is when i right click anywhere on the pivot table, all i have as
option is "group and show detail" and then if i click on "group", it cannot
group the selection. Am i clicking in the wrong place? (i tried clicking just
about everywhere on the pivot table)..
by the way i have Excel 2003..

"Sean Timmons" wrote:

I can get it by exact intervals. (i.e.- 100-200, 200-300, 300-400, etc.)
Create your pivot, right click in the Row field.
sleect Group and outline, then Group
starting at 0, end at, say 200, interval 50.
This will auto-sort by the Row criteria.

"munchkin" wrote:

Let's say i have this data to analyze:

Company #of employees State Industry

aaa 326 CA Industry A
bbb 1020 CA Industry C
ccc 575 NY Industry D
ddd 12000 VT Industry C

I would like to create a pivot table that summaries data this way:

# of employees
0-100 0
100-1000 2
1000-10000 1
10000-100000 1

or sort and count by brackets of 50 (# of employees):

0-50 0
50-100 0
100-150 0
etc..

Does anyone know how to do this with pivot tables? I know this can be done
because i've seen it done but can't remember how!!!! : (