Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to Reduce the Excel File Size? & Make it work with huge data
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to Reduce the Excel File Size? & Make it work with huge data
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 "Satish" wrote: 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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to Reduce the Excel File Size? & Make it work with huge data
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 Gizmo63 wrote: 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 "Satish" wrote: 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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to Reduce the Excel File Size? & Make it work with huge da
Thank You
"Gizmo63" wrote: 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 "Satish" wrote: 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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to Reduce the Excel File Size? & Make it work with huge da
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 "Satish" wrote: Thank You "Gizmo63" wrote: 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 "Satish" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pivot Table Data Source external excel file | Excel Discussion (Misc queries) | |||
Retaining formats | Excel Discussion (Misc queries) | |||
How reduce the file size of Excel 2007 files (wasted non value spa | Excel Discussion (Misc queries) | |||
how to reduce file size of excel workbooks | Excel Discussion (Misc queries) | |||
Empty Excel File -- Data Lost? | Excel Discussion (Misc queries) |