none
Excel spreadsheet slow to open and save

    Question

  • I have a 35MB Excel spreadsheet with approx 10 worksheets. It looks like one sheet in particular is taking a long time to load and save. The worksheet is approx 80k rows and is full of formulas. I have changed the Calculation options from Automatic to Manual with no success. The department who is relying on this worksheet has several other Excel spreadsheets of smaller size (in MB and formulas) that are also taking long to open and save (approx 15 - 20 min).

    Hardware and network are not an issue. This occurs on Win XP SP3 or Win 7 machines. Single core or multi core. 2GB or 8GB of RAM. On a network share or local desktop, the result is pretty much the same.

    Any thoughs on what to look for to remedy the situation? According to the department head these docs are going to grow in complexity and size.

    Thanks,


    - Gymmbo
    Saturday, January 28, 2012 12:46 AM

All replies

  • Gymmbo,

    Whenever I take part of stories like this one my first suspect is always the anti-virus software. In the past Excel files was potential carries of data virus and therefore all the file extension of Excel became part of the scanning list.

    Just as a test, turn temporarily off the AV-software and open and save Excel files. Any changes? 


    Kind regards, Dennis (http://xldennis.wordpress.com/)
    Monday, January 30, 2012 1:03 PM
  • Thank you for the response. I tried that already and as I have my task manager open during the opening of the spreadsheet the only processes working are the System Idle and Excel.exe.
    - Gymmbo
    Monday, January 30, 2012 4:42 PM
  • Gymmbo,

    1. I assume we can also exclude any recent update(s) of various driver(s) like printer et al.

    2. Does the workbook contain any ActiveX controls? If so then You may consider to replace them with the Form's controls.

    3. Is this a file that has been around for a while, adding data, removing data? The truth is that Excel files can get corrupted in one or another way. A slow open/saving can indicate an issue with the file, especially now that external aspects have been investigated. Create a new workbook file and move the sheets to the new file. 


    Kind regards, Dennis (http://xldennis.wordpress.com/)
    Monday, January 30, 2012 7:58 PM
  • No recent updates that I am aware of. No ActiveX controls that I am aware of. It looks like upon further review I have 12 columns and 80K rows of formulas. Just copying this group of cells to a new worksheet takes approx 15 min and the majority of the cells are not even populated w/ data as this is a forecasting spreadsheet.

    I have included a couple of the formulas, not sure if any of this will help. I am wondering if there is a way to streamline the input.

    =SUMIF('[New Inventory Forecast 01.23.12.xlsx]Demand'!$A:$A,$B29,'[New Inventory Forecast 01.23.12.xlsx]Demand'!#REF!)

    =IFERROR(VLOOKUP($B29,'[New Inventory Forecast 01.23.12.xlsx]POs & TBR'!$A:$X,MATCH('[New Inventory Forecast 01.23.12.xlsx]Model'!D$4,'[New Inventory Forecast 01.23.12.xlsx]POs & TBR'!$A$4:$X$4,0),FALSE),0)

    I can provide a copy of the workbook or worksheet for review.


    - Gymmbo

    • Edited by gymmbo Monday, January 30, 2012 9:35 PM
    Monday, January 30, 2012 8:45 PM
  • hi,

    Before my few days i solve from my network.

    please completely read before do something.

    actualy user apply some formating, instructions, color, font, fontstyle and color for whole row or column by pressing Alt+Space or Ctrl+Space. In this case or similer case your excel sheet look like you want but actualy they consume all over sheets 1000+ rows 1000+column.

    for remove: focus your working area. then after last row of your working area press Ctrl+Space to select these row and press "Shift+Ctrl+Down" Keys now you have select all rows from your end of working area to Down and delet it and save it. and do it for column also, of all sheets then save it.

    once you finish this please re open and check its magicaly responce.

    Note: please backup your file for your safety

    is your file need this operation not : please see below image

    http://www.shariqdon.com/thread/excelslow.png


    www.shariqdon.com

    Wednesday, November 07, 2012 10:40 AM
  • hi,

    Before my few days i solve from my network.

    please completely read before do something.

    actualy user apply some formating, instructions, color, font, fontstyle and color for whole row or column by pressing Alt+Space or Ctrl+Space. In this case or similer case your excel sheet look like you want but actualy they consume all over sheets 1000+ rows 1000+column.

    for remove: focus your working area. then after last row of your working area press Ctrl+Space to select these row and press "Shift+Ctrl+Down" Keys now you have select all rows from your end of working area to Down and delet it and save it. and do it for column also, of all sheets then save it.

    once you finish this please re open and check its magicaly responce.

    Note: please backup your file for your safety

    is your file need this operation not : please see below image

    http://www.shariqdon.com/thread/excelslow.png


    www.shariqdon.com

    also you can make sense to see scrolbar size. means its size and scroll belong to your sheet.

    please see above image.


    www.shariqdon.com

    Wednesday, November 07, 2012 10:42 AM
  • i also come across situation like reasonable data but very big file size. there are several area you can check:

    1. Is there any cells with no data but excel consider they are effective range?

    Suggestion: please press "Find and Select" button --> Go To Special --> Last cell; that is excel effective range, you cannot delete un-used row or un-used column if the "Last cell" was already marked by excel, but you can copy your data range to new workbook.

    2. Is there any hidden drawing/icon/picture within your worksheet?

    Suggestion: please press "Find and Select" button --> Go To Special --> Objects; all objects will be displayed and you can delete unnecessary/duplicated objects.

    3. Is there any array formula within your workbook?

    Suggestion: try to get away from array formula (which need Ctrl + Shift + Enter for data input) as it consume resource which may slow down response time.

    Friday, November 09, 2012 1:48 PM