Large excel files

[PB]

I use excel files with large amounts of data, sometimes up to 250,000 lines
of data. Pivot tables cause the files to become large and cumbersome and
frequently my computer freezes and I have to start over. Even when I delete
all the PT's to free up space, the files remain over 100mg. I know I should
probably be using access and I will (someday), but does anyone have any
advice for me now as to how to manage these large files in excel. Would
additional memory help? Why doesn't the size of the file reduce once the pt's
are deleted?

[Gord Dibben]

PB

Your used range may be larger than you think.

See Debra Dalgleish's site for methods of re-setting.

http://www.contextures.on.ca/xlfaqApp.html#Unused


Gord Dibben Excel MVP

On Fri, 18 Nov 2005 20:10:03 -0800, "PB" <PB@discussions.microsoft.com> wrote:

>I use excel files with large amounts of data, sometimes up to 250,000 lines
>of data. Pivot tables cause the files to become large and cumbersome and
>frequently my computer freezes and I have to start over. Even when I delete
>all the PT's to free up space, the files remain over 100mg. I know I should
>probably be using access and I will (someday), but does anyone have any
>advice for me now as to how to manage these large files in excel. Would
>additional memory help? Why doesn't the size of the file reduce once the pt's
>are deleted?

[Ken Wright]

I assume you are storing data on different sheets and then pulling them
together using the consolidate ranges option in the Pivot table wizard. Not
only does that make the pivot table less intuitive to play with, but that
data itself will also bloat your file. Personally i hate the view that that
gives me, and would probably simply dump the lot into a single Access table
and then query it using pivot tables from Excel, and you get all the usual
functionality you would expect. Just have Access store the data, that's
all.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

------------------------------*------------------------------*----------------
It's easier to beg forgiveness than ask permission :-)
------------------------------*------------------------------*----------------



"PB" <PB@discussions.microsoft.com> wrote in message
news:55CCC315-947B-4FB1-9364-BE391938E2C8@microsoft.com...
>I use excel files with large amounts of data, sometimes up to 250,000 lines
> of data. Pivot tables cause the files to become large and cumbersome and
> frequently my computer freezes and I have to start over. Even when I
> delete
> all the PT's to free up space, the files remain over 100mg. I know I
> should
> probably be using access and I will (someday), but does anyone have any
> advice for me now as to how to manage these large files in excel. Would
> additional memory help? Why doesn't the size of the file reduce once the
> pt's
> are deleted?