Numeric grouping in pivot tables

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

redrum - 02 Feb 2008 00:03 GMT

I see that I only get the option to group items from a number to a number
when all my data is numeric. Problem is that because I don't know how many
rows my data will have (I want to be able to paste in new values and just
'refresh' the pivot table), I have used the source "$A:$H".

Is there a way to have a range for my pivot table source that will allow me
to pick up any number of rows but and get the numeric grouping feature?

Thanks in advance for any pointers.

Reply to this Message

Herbert Seidenberg - 02 Feb 2008 02:19 GMT

Select your source data and
Excel 2003 Data > List > Create List
Excel 2007 Insert > Table
When you paste data at the * of the List
or delete rows of the List,
the Pivot Table range will adjust automatically.
Here is a sample file with Date being grouped.
http://www.freefilehosting.net/download/3bb1m

Reply to this Message

redrum - 02 Feb 2008 09:43 GMT

Thanks.

Can you do this in Excel 2000?

> Select your source data and
> Excel 2003 Data > List > Create List
[quoted text clipped - 4 lines]
> Here is a sample file with Date being grouped.
> http://www.freefilehosting.net/download/3bb1m

Reply to this Message

Roger Govier - 02 Feb 2008 11:39 GMT

hi
Regrettably this feature (List) only came in with XL2003

You can create a Dynamic named range to achieve the same thing.
Insert>Name>Define>Name lastrow Refers to =COUNTA($A:$A)
Insert>Name>Define>Name MyData Refers to =$A$1:INDEX($H:$H,lastrow)

Your data range will grow as you add more lines.

With the PT, right click on PT>PT Wizard>back>Data =MyData>Finish

Signature

Regards
Roger Govier

> Thanks.
>
[quoted text clipped - 8 lines]
>> Here is a sample file with Date being grouped.
>> http://www.freefilehosting.net/download/3bb1m

Reply to this Message

redrum - 03 Feb 2008 00:49 GMT

Thanks for that. Very useful.

> hi
> Regrettably this feature (List) only came in with XL2003
[quoted text clipped - 11 lines]
> >
> > "Herbert Seidenberg" <herbds7-msxls@yahoo.com> wrote in message

news:1ef70093-7624-44c8-8311-f81451de8701@s19g2000prg.googlegroups.com...

> >> Select your source data and
> >> Excel 2003 Data > List > Create List
[quoted text clipped - 4 lines]
> >> Here is a sample file with Date being grouped.
> >> http://www.freefilehosting.net/download/3bb1m

Reply to this Message