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

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" <[email protected]> wrote in
message news:[email protected]...
| 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" <[email protected]> wrote in
> message news:[email protected]...
> | 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
>
>
>