How to Reduce the Excel File Size? & Make it work with huge data

[Guest]

I want to reduce the MS-Excel file Size, which is currently 200+MB. Data
Ranges from A-DD Columns and 9000+ Rows with formulas. So the Excel
application hangs everytime I make changes And takes more than 30 Minutes to
make the change.. Is there is any way to reduce the file size without
deleting data and prevent hanging of Excel Application?. Data contains lot of
Formulas.

 

[Guest]

In practice I've found the following usually contribute to a massive file:
1. Is the workbook 'shared'? If so, is tracking turned on.
Taking the book out of shared or deselecting tracking will help.
2. Users have a habit of formatting (borders etc) the entire row or column
instead of only formatting what they need. This forces Excel to assign space
as though all rows and columns have been used.
Remove unwanted formatting.
3. There may also be a lot of 'reserved' cells that are no longer used.
Go to the first blank cell in column A below your data. Hit
[ctrl]+[shift]+[end], this will select a region that goes to the last
'reserved' cell.
You should get a range of cells that is not far below your data; chances are
that
the region selected will extend well beyond your data.
Delete all contents and formats from this region and then select cell A1.
Repeat on each tab.
Close and reopen the workbook; this should reset the 'reserved ' areas.

If none of this helps then you've created a monter and I'd start looking
at the formulas in use and if they can be made more efficient. Badly
constructed error traps are a classic for making formulas unecessarily
complicated.

Try opening excel and setting calculation to manual before opening the file
- this should speed up the open process. and only recalculate after you've
made a block of changes.
Does it look to many other workbooks? If it does and these linked books are
not open in the background then recalculating will take longer.

There are a host of things to look at but hopefully this may get you started.
Finally - is your pc of a decent spec?

HTH

Giz

 

[Pete_UK]

A couple of other points:

a. Do you need all your formulae to be active? Fixing them to values
will reduce the file size and speed up recalculation - highlight the
cells with formulae in, click <copy>, then Edit | Paste Special |
Values (check) OK then <Enter>.

b. Do you use a lot of named ranges, some of which may not be required?
These can also contribute to file size, so remove the ones no longer
needed - Insert | Name | Define and select each one in turn to check
what they refer to - Delete any with #Ref, as these are probably for
rows/columns/sheets which have now been removed.

c. When you make any of the changes suggested, save your workbook
immediately.

Hope this helps.

Pete

In practice I've found the following usually contribute to a massive file:
1. Is the workbook 'shared'? If so, is tracking turned on.
Taking the book out of shared or deselecting tracking will help.
2. Users have a habit of formatting (borders etc) the entire row or column
instead of only formatting what they need. This forces Excel to assign space
as though all rows and columns have been used.
Remove unwanted formatting.
3. There may also be a lot of 'reserved' cells that are no longer used.
Go to the first blank cell in column A below your data. Hit
[ctrl]+[shift]+[end], this will select a region that goes to the last
'reserved' cell.
You should get a range of cells that is not far below your data; chances are
that
the region selected will extend well beyond your data.
Delete all contents and formats from this region and then select cell A1.
Repeat on each tab.
Close and reopen the workbook; this should reset the 'reserved ' areas.

If none of this helps then you've created a monter and I'd start looking
at the formulas in use and if they can be made more efficient. Badly
constructed error traps are a classic for making formulas unecessarily
complicated.

Try opening excel and setting calculation to manual before opening the file
- this should speed up the open process. and only recalculate after you've
made a block of changes.
Does it look to many other workbooks? If it does and these linked books are
not open in the background then recalculating will take longer.

There are a host of things to look at but hopefully this may get you started.
Finally - is your pc of a decent spec?

HTH

Giz

I want to reduce the MS-Excel file Size, which is currently 200+MB. Data
Ranges from A-DD Columns and 9000+ Rows with formulas. So the Excel
application hangs everytime I make changes And takes more than 30 Minutes to
make the change.. Is there is any way to reduce the file size without
deleting data and prevent hanging of Excel Application?. Data contains lot of
Formulas.

 

[Guest]

Thank You

In practice I've found the following usually contribute to a massive file:
1. Is the workbook 'shared'? If so, is tracking turned on.
Taking the book out of shared or deselecting tracking will help.
2. Users have a habit of formatting (borders etc) the entire row or column
instead of only formatting what they need. This forces Excel to assign space
as though all rows and columns have been used.
Remove unwanted formatting.
3. There may also be a lot of 'reserved' cells that are no longer used.
Go to the first blank cell in column A below your data. Hit
[ctrl]+[shift]+[end], this will select a region that goes to the last
'reserved' cell.
You should get a range of cells that is not far below your data; chances are
that
the region selected will extend well beyond your data.
Delete all contents and formats from this region and then select cell A1.
Repeat on each tab.
Close and reopen the workbook; this should reset the 'reserved ' areas.

If none of this helps then you've created a monter and I'd start looking
at the formulas in use and if they can be made more efficient. Badly
constructed error traps are a classic for making formulas unecessarily
complicated.

Try opening excel and setting calculation to manual before opening the file
- this should speed up the open process. and only recalculate after you've
made a block of changes.
Does it look to many other workbooks? If it does and these linked books are
not open in the background then recalculating will take longer.

There are a host of things to look at but hopefully this may get you started.
Finally - is your pc of a decent spec?

HTH

Giz

I want to reduce the MS-Excel file Size, which is currently 200+MB. Data
Ranges from A-DD Columns and 9000+ Rows with formulas. So the Excel
application hangs everytime I make changes And takes more than 30 Minutes to
make the change.. Is there is any way to reduce the file size without
deleting data and prevent hanging of Excel Application?. Data contains lot of
Formulas.

 

[Guest]

Satish,
if the changes you are making at once are too numerous, you can halt the
recalculation by switching to manual mode.
By macro it sounds ie.:

Sub AutoManuCalculation()
'Macro toggles the calculation mode from automatic to manual and backwards.

With Application
If .Calculation = xlCalculationAutomatic Then
.Calculation = xlCalculationManual
.StatusBar = "ATTENTION! Manual calculation!"
Else
.StatusBar = False
.Calculation = xlCalculationAutomatic
End If
End With
End Sub

Regards
Petr
--
Petr Bezucha

Thank You

In practice I've found the following usually contribute to a massive file:
1. Is the workbook 'shared'? If so, is tracking turned on.
Taking the book out of shared or deselecting tracking will help.
2. Users have a habit of formatting (borders etc) the entire row or column
instead of only formatting what they need. This forces Excel to assign space
as though all rows and columns have been used.
Remove unwanted formatting.
3. There may also be a lot of 'reserved' cells that are no longer used.
Go to the first blank cell in column A below your data. Hit
[ctrl]+[shift]+[end], this will select a region that goes to the last
'reserved' cell.
You should get a range of cells that is not far below your data; chances are
that
the region selected will extend well beyond your data.
Delete all contents and formats from this region and then select cell A1.
Repeat on each tab.
Close and reopen the workbook; this should reset the 'reserved ' areas.

If none of this helps then you've created a monter and I'd start looking
at the formulas in use and if they can be made more efficient. Badly
constructed error traps are a classic for making formulas unecessarily
complicated.

Try opening excel and setting calculation to manual before opening the file
- this should speed up the open process. and only recalculate after you've
made a block of changes.
Does it look to many other workbooks? If it does and these linked books are
not open in the background then recalculating will take longer.

There are a host of things to look at but hopefully this may get you started.
Finally - is your pc of a decent spec?

HTH

Giz

I want to reduce the MS-Excel file Size, which is currently 200+MB. Data
Ranges from A-DD Columns and 9000+ Rows with formulas. So the Excel
application hangs everytime I make changes And takes more than 30 Minutes to
make the change.. Is there is any way to reduce the file size without
deleting data and prevent hanging of Excel Application?. Data contains lot of
Formulas.