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

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

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

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