Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Thorsten Pollmeier
 
Posts: n/a
Default Troubel with compression of a very large Excel file

Hi,

When trying to compress a very large Excel file (30MB) either with Winzip
or WinRar, the compression does not result in a significant reduction (max.
-3% only in the best compression mode).

From my experience it seems to me that there is something like a
'hidden/magic' limit around ~25 MB and below this a compression is not a
problem, but above it is. Even if the large file is reduced back to a very
small one, the compression still does not result anymore in a significant
reduction (e.g. when deleting data in the 30MB file to bring it back to 5MB,
the following compression result is still max. -3%).

The size of the 30 MB is mainly driven by: ~2500 data sets as rows with 50
basis data information columns plus further 150 complex calculation columns
including lookup references to around ~30 parameter tables on another sheet.
Additionally, around 30 referenced charts are part of the file plus 1 pivot
overview table for the result.

The most commonly heard resolution approach is to do it at least in Access
and not in Excel due the data amount and required
calculation/parameterization effort. BUT there is a business reason behind
why it is done this way. Hence, does anybody has a good idea how to get the
file compressed to a size ~ 5MB (that's what I reach if the basis file is
<25MB)?

I have also heard that this issue is maybe related to an 'Excel internal
compression algorithm'. Does anybody know something about this?

I really appreciate your feedback/support and would like to thank you for
your help in advance,
Thorsten
  #2   Report Post  
Jim Rech
 
Posts: n/a
Default

It sounds as if you have password protected your workbook. This results in
an encrypted file that has far fewer "patterns" that compression programs
depend in. If you must encrypt the file I'd suggest that you not do it in
Excel but rather do it with the encryption program. I believe they all have
an option to password protect.

--
Jim Rech
Excel MVP
"Thorsten Pollmeier" wrote in
message ...
| Hi,
|
| When trying to compress a very large Excel file (30MB) either with Winzip
| or WinRar, the compression does not result in a significant reduction
(max.
| -3% only in the best compression mode).
|
| From my experience it seems to me that there is something like a
| 'hidden/magic' limit around ~25 MB and below this a compression is not a
| problem, but above it is. Even if the large file is reduced back to a very
| small one, the compression still does not result anymore in a significant
| reduction (e.g. when deleting data in the 30MB file to bring it back to
5MB,
| the following compression result is still max. -3%).
|
| The size of the 30 MB is mainly driven by: ~2500 data sets as rows with
50
| basis data information columns plus further 150 complex calculation
columns
| including lookup references to around ~30 parameter tables on another
sheet.
| Additionally, around 30 referenced charts are part of the file plus 1
pivot
| overview table for the result.
|
| The most commonly heard resolution approach is to do it at least in Access
| and not in Excel due the data amount and required
| calculation/parameterization effort. BUT there is a business reason behind
| why it is done this way. Hence, does anybody has a good idea how to get
the
| file compressed to a size ~ 5MB (that's what I reach if the basis file is
| <25MB)?
|
| I have also heard that this issue is maybe related to an 'Excel internal
| compression algorithm'. Does anybody know something about this?
|
| I really appreciate your feedback/support and would like to thank you for
| your help in advance,
| Thorsten


  #3   Report Post  
Thorsten Pollmeier
 
Posts: n/a
Default

Hi Jim,

excellent. You are right. Your suggestion works. Thank you very much!

"Jim Rech" wrote:

It sounds as if you have password protected your workbook. This results in
an encrypted file that has far fewer "patterns" that compression programs
depend in. If you must encrypt the file I'd suggest that you not do it in
Excel but rather do it with the encryption program. I believe they all have
an option to password protect.

--
Jim Rech
Excel MVP
"Thorsten Pollmeier" wrote in
message ...
| Hi,
|
| When trying to compress a very large Excel file (30MB) either with Winzip
| or WinRar, the compression does not result in a significant reduction
(max.
| -3% only in the best compression mode).
|
| From my experience it seems to me that there is something like a
| 'hidden/magic' limit around ~25 MB and below this a compression is not a
| problem, but above it is. Even if the large file is reduced back to a very
| small one, the compression still does not result anymore in a significant
| reduction (e.g. when deleting data in the 30MB file to bring it back to
5MB,
| the following compression result is still max. -3%).
|
| The size of the 30 MB is mainly driven by: ~2500 data sets as rows with
50
| basis data information columns plus further 150 complex calculation
columns
| including lookup references to around ~30 parameter tables on another
sheet.
| Additionally, around 30 referenced charts are part of the file plus 1
pivot
| overview table for the result.
|
| The most commonly heard resolution approach is to do it at least in Access
| and not in Excel due the data amount and required
| calculation/parameterization effort. BUT there is a business reason behind
| why it is done this way. Hence, does anybody has a good idea how to get
the
| file compressed to a size ~ 5MB (that's what I reach if the basis file is
| <25MB)?
|
| I have also heard that this issue is maybe related to an 'Excel internal
| compression algorithm'. Does anybody know something about this?
|
| I really appreciate your feedback/support and would like to thank you for
| your help in advance,
| Thorsten



  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 4
Default Troubel with compression of a very large Excel file

Hi Rech,
I have the same prob but I dont want to encrypt the file beacuse in this
case once file opens user can make changes in file, while I want to protect
my file contents to be edited.
Is there any other way to reduce file size by above 70%


"Jim Rech" wrote:

It sounds as if you have password protected your workbook. This results in
an encrypted file that has far fewer "patterns" that compression programs
depend in. If you must encrypt the file I'd suggest that you not do it in
Excel but rather do it with the encryption program. I believe they all have
an option to password protect.

--
Jim Rech
Excel MVP
"Thorsten Pollmeier" wrote in
message ...
| Hi,
|
| When trying to compress a very large Excel file (30MB) either with Winzip
| or WinRar, the compression does not result in a significant reduction
(max.
| -3% only in the best compression mode).
|
| From my experience it seems to me that there is something like a
| 'hidden/magic' limit around ~25 MB and below this a compression is not a
| problem, but above it is. Even if the large file is reduced back to a very
| small one, the compression still does not result anymore in a significant
| reduction (e.g. when deleting data in the 30MB file to bring it back to
5MB,
| the following compression result is still max. -3%).
|
| The size of the 30 MB is mainly driven by: ~2500 data sets as rows with
50
| basis data information columns plus further 150 complex calculation
columns
| including lookup references to around ~30 parameter tables on another
sheet.
| Additionally, around 30 referenced charts are part of the file plus 1
pivot
| overview table for the result.
|
| The most commonly heard resolution approach is to do it at least in Access
| and not in Excel due the data amount and required
| calculation/parameterization effort. BUT there is a business reason behind
| why it is done this way. Hence, does anybody has a good idea how to get
the
| file compressed to a size ~ 5MB (that's what I reach if the basis file is
| <25MB)?
|
| I have also heard that this issue is maybe related to an 'Excel internal
| compression algorithm'. Does anybody know something about this?
|
| I really appreciate your feedback/support and would like to thank you for
| your help in advance,
| Thorsten



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Excel 2003 FAILS, but Excel 2000 SUCCEEDS ??? Richard Excel Discussion (Misc queries) 2 May 13th 23 11:46 AM
My excel file is too large (5 mb) when it shouldn't be, why? Kenview Excel Discussion (Misc queries) 1 February 26th 05 10:14 PM
sync of large excel files will not completly transfer Ben Excel Discussion (Misc queries) 0 February 24th 05 10:53 PM
Using large excel files Tom Trahan Excel Worksheet Functions 1 January 25th 05 08:35 PM
Difference in number of Excel NewsGroups Hari Prasadh Excel Discussion (Misc queries) 1 January 25th 05 12:32 PM


All times are GMT +1. The time now is 10:33 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"