+ Reply to Thread
Results 1 to 13 of 13

Excel file slow to load and save

  1. #1
    Registered User
    Join Date
    05-21-2009
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2007
    Posts
    94

    Excel file slow to load and save

    I am using Excel 2007. I have a file containing over 5,000 rows, each with a few calculations. Over the last week or so it has been very slow to open and very slow to save (15 seconds). This has only been happening recently. Assuming that it was the large number of calculation which had to be performed I Copy/Paste Valued something like 5,000 rows to remove most of the formulas but it made no difference. What is my problem? Is it simply the size of the file?
    Attached Files Attached Files

  2. #2
    Forum Expert Logit's Avatar
    Join Date
    12-23-2012
    Location
    North Carolina
    MS-Off Ver
    Excel 2019 Professional Plus - 2007 Enterprise
    Posts
    6,986

    Re: Excel file slow to load and save

    .
    Could be two problems :

    #1 - The overall size of the file with 5,000 rows of data.

    #2 - The file may have auto-created "phantom rows" of data. These are rows that visually appear empty but are actually holding something that Excel is
    interpreting as "data".

    For #2 ... click on cell A1 of your sheet that contains the 5,000 rows of data. Now press together CTRL / DOWN ARROW. If your cursor ends up waaaay
    down below the last row of data you can actually see with your eyes, then phantom rows exist. Repeat this process on all sheets in your workbook to
    determine if any of the sheets are holding phantom data.

  3. #3
    Registered User
    Join Date
    05-21-2009
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2007
    Posts
    94

    Re: Excel file slow to load and save

    I did this and the cursor went down to Row 6421 or thereabouts. If I do a Control Shift End the cursor ends up at about Column BDZ Row 6019. If I remember correctly, clicking Control Shift End is supposed to highlight all the cells in your worksheet. I will have to check further.

  4. #4
    Registered User
    Join Date
    05-21-2009
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2007
    Posts
    94

    Re: Excel file slow to load and save

    Further to the above, I found that what appeared to be hundreds of columns to the right of the occupied part of my worksheet are formatted pink, which is only supposed to be the case for the dozen or so columns in the part of the sheet I am using. How can I highlight all the pink formatted columns and either delete them or make them no fill?

  5. #5
    Forum Expert Logit's Avatar
    Join Date
    12-23-2012
    Location
    North Carolina
    MS-Off Ver
    Excel 2019 Professional Plus - 2007 Enterprise
    Posts
    6,986

    Re: Excel file slow to load and save

    .
    Yes, you can delete those pink formatted columns so long as they don't involve anything to do with your formulas / macros, etc.

    There are other things you should look for in your workbook :

    Colored cells / rows / columns that do not contain any data/formulas .... cells / rows / columns that have the borders outlined do not contain any data/formulas.
    Excel has to display everything you have implemented in your workbook, whether or not you are presently using it. For example, if your data goes from Row 1 to
    Row 36, but you have all the rows borders outlined in bold, down to row 1,000 .... resources are being used to display the borders to row 1,000 and that slows
    down your computer to some degree.

  6. #6
    Registered User
    Join Date
    05-21-2009
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2007
    Posts
    94

    Re: Excel file slow to load and save

    Will deleting the pink formatted columns speed up saving/ closing? I started deleting them but the columns are coloured all the way to the far right of the worksheet and it was taking forever.

    Would Copy/Paste Valuing all the old formulas to turn them into values help? I have all the months from January 2011 to December 2021 bordered and coloured. I Copy/Paste Valued all the formulas down to the end of 2019 but it made no difference. The problem arose when I was preparing the worksheet for 2021. I added 480 rows (40 rows per month) and the formulas necessary for those 480 rows. After that the processing time became a problem.

  7. #7
    Forum Expert Logit's Avatar
    Join Date
    12-23-2012
    Location
    North Carolina
    MS-Off Ver
    Excel 2019 Professional Plus - 2007 Enterprise
    Posts
    6,986

    Re: Excel file slow to load and save

    .
    You should plan your workbook so formatting, borders, formulas, etc. etc. are all added when needed ... not down endless rows and columns that
    are not being used for months or even weeks / days prior.

    As said previously, everything you add to your workbook now, even if it is not utilized now, will drag down your workbook performance.

    You should also delete or copy / move unused past records to another workbook for storage purposes.

  8. #8
    Registered User
    Join Date
    05-21-2009
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2007
    Posts
    94

    Re: Excel file slow to load and save

    I understand what you are suggesting. I always prepare a year in advance because I have another worksheet which provides statistical reports and is rather complex. The tables in this worksheet are linked to the main worksheet and are populated automatically as data is addeed to the main worksheet. I would like to be able to delete and store data from previous years (2011 to 2019) but I also produce a report which lists all the items I have sold in date and alphabetical order. I realise that I could find a way around this but it would be even more complicated than it is now. That report selects the items which have sold on the main report to the items which have sold on a second report (I have two eBay sites). The second report is the same in format to the main report but includes far fewr rows. If I moved the old data to a new worksheet I would have to add that worksheet to the other two.

    I realize now that my problem arose only after I added the sections for 2021 (which contains no data but 12 months worth of formulas).

  9. #9
    Forum Expert Logit's Avatar
    Join Date
    12-23-2012
    Location
    North Carolina
    MS-Off Ver
    Excel 2019 Professional Plus - 2007 Enterprise
    Posts
    6,986

    Re: Excel file slow to load and save

    ..
    Ok then .... perhaps the only thing you might consider as a last resort is a computer upgrade ?

    Do you have enough RAM.

    Is the CPU the latest and fastest?

    Does your computer utilize the newer SSD hard drive (Solid State Drive), if so place Excel and all your workbooks on the SSD drive.
    If your computer doesn't have the SSD hard drive ... get one, even if you have to purchase an external drive that connects via USB.

    Best wishes on your journey.

  10. #10
    Registered User
    Join Date
    05-21-2009
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2007
    Posts
    94

    Re: Excel file slow to load and save

    I have an SSD Drive. My D: drive contains Excel, etc. Should I move them to the C: drive? In other words, does the C:drive refer to the SSD?

  11. #11
    Forum Expert Logit's Avatar
    Join Date
    12-23-2012
    Location
    North Carolina
    MS-Off Ver
    Excel 2019 Professional Plus - 2007 Enterprise
    Posts
    6,986

    Re: Excel file slow to load and save

    .
    I can't advise which drive is your SSD drive. I don't have your computer here to review. You'll need to discover that yourself.

    But, generally speaking, moving Excel to the SSD drive and doing all of your coding / saving workbooks to the SSD drive will
    give you a speed advantage. How much remains to be seen and it might not be an amount to change your circumstances.

    The same applies to the computer RAM. I prefer to have a minimum of 16gb of RAM. 32gb is better ... 64gb might be overkill
    but you would certainly have more than enough RAM at 64gb (if you computer is able to be configured that way - some cannot).

  12. #12
    Registered User
    Join Date
    05-21-2009
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2007
    Posts
    94

    Re: Excel file slow to load and save

    Thanks again. I have learned that my SSD drive is C:. I have also found that Excel is already on the SSD drive. I am playing with the idea of Copy/Paste Values my existing large and slow worksheet (which would contain data from 2011 to November 2020) and creating a new worksheet which would start from December 2020. My statistics worksheet would be linked to both the old worksheet and the new one, drawing datafrom the old one (2011 to November 2020) and the new one (December 2020 onwards). which would mean that the old and slow worksheet would only be opened and closed when I updated the statistics worksheet. Still a nuisance as I assume that the linked old worksheet would still take a while to open and close, however the day to day adding of data to the new worksheet would be quite quick (I think).

  13. #13
    Forum Expert Logit's Avatar
    Join Date
    12-23-2012
    Location
    North Carolina
    MS-Off Ver
    Excel 2019 Professional Plus - 2007 Enterprise
    Posts
    6,986

    Re: Excel file slow to load and save

    .
    Another thought you might also consider is possibly breaking down the data to individual workbooks based on individual years. Obviously, if you can reduce
    the size of each workbook to a bare minimum, it would make things run so much quicker. You could code your macro to open a workbook based on a naming
    pattern of Year 2001 ... Year 2002 ... etc. Then continue that naming pattern into the future.

    My 2 cents.

    Best wishes.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Excel File - Slow to Open/Save (2.5MB)
    By asrarrazaq in forum Excel General
    Replies: 3
    Last Post: 06-28-2019, 04:49 AM
  2. Excel glacially slow to load workbooks
    By terrypin in forum Excel General
    Replies: 21
    Last Post: 11-07-2016, 02:33 PM
  3. Excel file size changes after load and save
    By BlairHogg in forum Excel General
    Replies: 0
    Last Post: 06-25-2012, 09:20 AM
  4. Slow Load & Save with no data to speak of.
    By EdCreated in forum Excel General
    Replies: 2
    Last Post: 06-06-2011, 03:22 PM
  5. Excel 2007, Slow to Load and Save
    By excel199 in forum Excel General
    Replies: 7
    Last Post: 07-21-2010, 03:18 AM
  6. Excel is very slow to load files
    By rgrstvr in forum Excel General
    Replies: 12
    Last Post: 12-20-2009, 09:25 AM
  7. [SOLVED] why my excel load slow?
    By please help me..urgent in forum Excel General
    Replies: 1
    Last Post: 02-24-2005, 02:06 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1