Endorkins

The coder’s high

Endorkins header image 2

Slow Excel Spreadsheet? Try this.

July 13th, 2009 · 39 Comments

There are a number of things that can cause an Excel spreadsheet to slow down. Sometimes, the spreadsheet can become unusable, requiring up to a minute for saving or even the most basic of changes. Here’s a brief checklist of the most frequent causes of Excel slowdowns.

(Have a spreadsheet slow-down that I haven’t described below? Let me know in the comments! Thanks! :) -Rich)

  • Network problems. If the spreadsheet is accessed over the network (local or remote), network issues can delay saving.
  • Macros/VBA code. If you are using macros (or Visual Basic for Applications), make sure you don’t have unnecessary macros running, or macros that perform unnecessary calculations.
  • Burdensome animation. Try disabling animation. 2003: Tools > Options > Edit > Turn off “Provide feedback with animation”. 2007: Excel Button > Excel Options > Advanced > General > Provide feedback with animation (uncheck)
  • Complicated calculations. If you’re doing lots of calculations, you can disable auto-updating of these calculations. When you do so, your spreadsheet will not update until you explicitly desire (e.g. when you hit the F9 key). To disable auto-updating in 2007, go to the Excel Button > Excel Options > Formulas > Workbook Calculation, and select “Manual”.
  • Duplicate Conditional Formatting Rules. Conditional formatting can be a processor-intensive task if your rules aren’t set up right. Manage your conditional formatting rules (2007) via the ribbon at Home > Conditional Formatting > Manage Rules… Try to consolidate similar rules into one rule (e.g. a rule for an entire column instead of a rule for each cell in the column)
  • Funky Formatting. Formatting can really slow down a spreadsheet, especially on slower machines. Try removing all formatting: Removing All Formatting.
  • Unnecessary junk. Sometimes, an Excel file can become backlogged with a bunch of unnecessary junk. Try saving as a CSV (comma-separated) file, which will strip out everything except for the data. (Make sure to not save over your original file in case this workaround does not, well, work! :) )
  • Unknown, but it seems to help sometimes. Install the Lookup Wizard add-in.
  • Other issues. Do you know of another issue that causes Excel to slow down? Let me know in the comments!

Assorted helpful links:

Tags: Uncategorized

39 responses so far ↓

  • 1 Michael Baker // Aug 30, 2009 at 6:55 am

    I tend to construct spreadsheets with blocks of common features including conditional formatting. Having copied one area to another I have duplicated the conditional formatting in the process. Do you have any suggestions for automating the process for removing duplicate conditional formatting?

  • 2 Sample newsgrab | Agribusiness Australia // Aug 30, 2009 at 10:29 pm

    [...] read more here [...]

  • 3 TessM // Apr 19, 2010 at 12:00 pm

    I had issue #5 – thx for the tip!

  • 4 admin // Dec 28, 2010 at 5:09 pm

    @Michael: Have you found a solution? I’m not sure I know how to accomplish that. :P

    @TessM: Sure thing! :)

  • 5 Aliente // Jan 6, 2011 at 3:45 am

    Our IT department struggled a lot on a slow Excel sheet and we finally found the problem.
    Users had created tousands of text areas : each time they made anotations they created one more area but when they removed such anotations they just erased the text without removing text area. With the time it ended with 10000 text areas hidden on the datasheet. To select all hit F5, click Cells and select “Objects”.

  • 6 MerkDog // Sep 28, 2011 at 3:55 am

    @ Aliente: simply brilliant! Searched for a solution for this everywhere, and all you ever hear is “make sure there are no macros” or “don’t use volatile functions” etc etc. Even doing Edit > Clear > All had no effect. Thank you! For the benefit of others (Excel 2003): after F5, select Special, then Objects, then Edit menu > Cut. Repeat on all sheets. Save.

  • 7 scott biggs // Oct 11, 2011 at 2:54 pm

    Excel needs lots of memory to run efficiently. If you are multi-tasking with another memory-hog program (like a data base program, for example), there’s a very good chance that your computer will run out of memory for even a moderate spreadsheet. The resulting hard-drive thrashing will slow your work down to a crawl.

    Lots of flashing of the hard-drive light while doing even simple typing is an indicator of this thrashing.

    Solution: quit the other programs. As you work, Excel should speed up.

  • 8 Ferenc // Nov 1, 2011 at 9:13 am

    Hi, just a note – my solution for the slow excel was to disable multi-processer calculations, even though I have a dual-core on my desktop.
    With only that, I managed to boost my Excel up :-)

  • 9 Isaac // Jan 4, 2012 at 1:01 pm

    I was going crazy over a particular spreadsheet which was really lagging when scrolling, paging up or down, etc. I tried everything and after reading this discovered it was some funky conditional formatting someone before used. Awesome tips!

  • 10 David // Feb 22, 2012 at 12:34 pm

    @ Aliente
    Thank you for that last comment about “objects”. Ii had a slow file that I inherited. Thought the cause of the slowness was all the formulas. Turned out the button that was created as a shortcut to print had 3,000 buttons on top of each other.

  • 11 NeilB // Mar 8, 2012 at 2:37 pm

    Embedded Graphics. I had a horribly slow wookbook with 48 worksheets. Then I found 3 embedded pictures hidden in one of the woorksheets (it just looked like a date, the size of one cell) I deleted them and now the whole workbook is blazing fast

  • 12 Raheel // Mar 17, 2012 at 4:14 am

    I’ve same problem in excel 2007 as Isaac mentioned “I was going crazy over a particular spreadsheet which was really lagging when scrolling, paging up or down, etc. I tried everything and after reading this discovered it was some funky conditional formatting someone before used. Awesome tips!”

    I tried following and spreadsheet is now running like a light speed:
    (2007) Go to ribbon at Home > Conditional Formatting > Clear Rules > Clear Rules from entire worksheet

    Its awesome……………

  • 13 Fernando // Jun 25, 2012 at 1:51 am

    Thanks for your tips. I was going nuts with a quite big spreadsheet an my efforts were directed to reduce calculations.

    But the solution came when, as you recommended, I checked the Conditional Formatting. It’s OK now.

    By the way, when I switched from excel 2003 to excel 2007 I could notice what a mess they’d done with condictional formatting. It was very easy in excel 2003 but very, very difficult in excel 2007.

    Fernando

  • 14 Bri // Jul 18, 2012 at 1:41 pm

    I was almost unable to work because of Excel slowness. Trying to page down my worksheet was unbearable. I tried copying my workbook and then removing all formatting and it was super fast so I knew it was a formatting issue. Was really frustrated because I needed it formatted as it was. The fix I discovered was crazy easy. Just be sure your view is set to “normal” and not “page layout.” I never set the view to page layout, so I did’t think to change it. It occured when we upgraded our systems.

  • 15 mad_zoner // Aug 17, 2012 at 9:32 am

    I had a super slow detailed workbook that had ten worksheets linked with formulas. I had copied the three original worksheets all the others were based on from another file. As I worked on the new file, it got very slow to respond and kept freezing up. I tried everything suggested until I removed the conditional formatting off all the sheets (inherited from the previous file). Now it runs fine.

  • 16 Shravan // Sep 3, 2012 at 5:58 am

    Hello there
    I have been having this annoying problem with excel 2007, it stop working frequently at the initiation of very simple / basic operations like highlighting cells, changing tabs, linking numbers, et al. I wonder why, I infact tried all the above possible causes and I don’t seem to be getting anywhere. I says “book1 , not responding” for a while and then comes back to normal. Again I do some operation, the same thing appears again on the top bar for a while (4 secs or so) and then starts working. Kindly help me on this.
    Thanks
    Shrav.

  • 17 Biggo // Sep 14, 2012 at 8:20 am

    Recently, a spreadsheet had become unbearably slow: scrolling, calculations, everything; even clicking on a cell would take 2-3 seconds before the cell was selected.
    The cause: hidden rows in the visible area.
    Everything under my work space was hidden; having recently deleted a few unnecessary rows, the “bottom” of the table was now showing on screen.
    I’ve fixed the problem by revealing a couple rows at the bottom, thus pushing the empty space out of the window.

    To check for this bug: if your sheet has hidden rows at the bottom, just shrink the window and scroll up until the bottom rows are out of sight.
    Sorry if this seems complicated, I really can’t explain myself better than this (not an English speaker) .-)

  • 18 Eddie // Sep 21, 2012 at 2:13 pm

    Thanks for the great tips! I’d like to add this one: Look for hidden worksheets that may be causing the problem, perhaps solvable using tips above. From the workbook tab bar showing all the worksheets, right click, Unhide.

  • 19 Reticuli // Oct 9, 2012 at 1:36 pm

    Excel 2010 has a glitch. Even if you have calculations set to manual, it will recalculate the entire workbook if you attempt to copy a chart into powerpoint or word, or even attempt to do anything to that chart after it is copied.

  • 20 Alastair // Oct 10, 2012 at 9:21 pm

    “Try to consolidate similar rules into one rule (e.g. a rule for an entire column instead of a rule for each cell in the column)”

    I have a very slow spreadsheet which is a course calendar each row is a new day and columns tabulate for each course if one is being run in that subject and where it is.

    I made some conditional rules to highlight the rows with a green background for Weekends (weekday == sat | sun) and another one for Public Holidays ($W2 == True) if a column value is set to true it highlights the row red (well not the whole row just the cells the rule is applied to)

    How do I convert formulas from cells to columns? I have the same formula rule for each cell but it’s applied to every cell individually so I don’t understand the distinction being made.

    I’ve tried different formulas to do the same kind of testing on a TRUE value in a set column or testing date for weekday various ways but their all very slow, especially with three rules.

    Formula’s tried so far that work but are slowing down Excel:
    =INDEX($W:$W,ROW())=TRUE (Public holiday column cell is set to true turns this on)
    =OR(WEEKDAY($A3)=1,WEEKDAY($A3)=7) (Weekends)

  • 21 Alastair // Oct 10, 2012 at 9:22 pm

    I meant to ask how do I convert formulas from cells to ROWS.

  • 22 Beatrice // Oct 25, 2012 at 11:01 am

    I found that I had the view setting on “Page Break Preview” which slowed it down a lot. Putting back to “Normal” worked excellently.

  • 23 Lori // Oct 31, 2012 at 11:22 am

    I copied a filtered spreadsheet (I have auto filter on the original) into a new spreadsheet/workbook. Now when I save the new workbook or open it, it is very slow. I have it saved locally. I’ve tried the above suggestions, but nothing has worked to increase the speed. I think Excel has some funky things going on behind the scenes as the new workbook file is huge (35,000K) compared to the original (111K) even though I only copied this one spreadsheet and the original file has 12 spreadsheets.

    Any ideas on how to get this funkiness out of the new workbook without having to totally redo it?

  • 24 Anita // Nov 6, 2012 at 5:55 pm

    Thanks so much for the tips!!! U saved me today!! :)

  • 25 Happy! // Nov 15, 2012 at 1:50 am

    Thanks a lot, been searching online for slow excel solution for a while and was getting very frustrated. I cleared rules for conditional formatting after reading this page and all seems good now. Have no idea how any rules got there in the first place though??
    Thanks again!

  • 26 Emirul // Jan 2, 2013 at 9:15 pm

    Hi all…I have this one small excel file (230KB) which need 5-10 sec for the words to appear in the box everytime I try to write into it.. I have tried to do all of the above suggestion but the problem still there…btw, I use Excel 2007
    I tried 1 idea which has solve the problem: The problem happen because I put PICTURE LINK in the document!! once i remove all picture links, all problems solve, no more slow typing…

    Anybody knows why this happen??

    I always use picture link (paste picture link feature) in my work, I really need the feature. pls help!

  • 27 Jose // May 3, 2013 at 1:46 pm

    I had Macros/VBA code that were slowing the workbook. However, I wasn’t sure which particular macro. I pressed the ESC key when it delayed during a cell change. That brought up the VBA debug screen. Once selecting debug, it showed the exact macro causing the delay. I then set up that macro to run manually.

    Excel 2003

    Application.Calculation = xlCalculationManual

  • 28 Nishit // Jul 25, 2013 at 12:29 am

    THANK YOU for your suggestion on Conditional Formatting. I had an excel in 2003 format which I started using in Excel 2007 and because of the conditional formatting, even simple things like entering a value was taking time and excel used to hang. After removing conditional formatting, it is working very fast.

  • 29 stefan // Jul 25, 2013 at 12:42 am

    Had the conditional formating problem. Deleted the rules and the sheet and the issue is gone.

    Thanks for the help!

  • 30 firdouz Iqbal // Aug 15, 2013 at 7:18 am

    THUMBS UP BRO
    was searching hi and low and what mostly being said was abt macro But to see it was the objects
    For the benefit of others (Excel 2003):Press F5, select Special, then tick Objects, then just delete. Repeat on all sheets. Save.
    Took some time but did the trick
    Thanks all for posting

  • 31 Pam // Aug 15, 2013 at 9:17 am

    My very small workbook suddenly got extremely slow. I wasn’t sure why it was taking so long to open, save and close the file. I tried CTRL-END on one worksheet and found that my 305 row file had TENS OF THOUSANDS of empty rows. This was the case for each of my 20+ worksheets in this workbook. Rather than deleting all those empty lines, I found it faster to copy ONLY THE DATA from each worksheet into a new worksheet (if you copy the worksheet, you would copy all the blank lines). I had to fix some references and range names but wow, it’s nice to be back to the speedy opening, saving and closing of this file.

  • 32 Sig // Aug 15, 2013 at 2:14 pm

    Smallish spreadsheet (800 kb) lots of tabs. Copying and pasting was SLOOWWW! and plagued with crashes. Removed all conditional formatting (about 200 cells) and fixed! I even replaced the conditional formatting and it’s still fine. Another adventure in computing brought to you by Bill Gates. :-(

  • 33 Brad S. // Aug 15, 2013 at 10:30 pm

    I had a spreadsheet that blew up due to the active window size changing to the entire worksheet. I finally figured it out by looking at the scroll bars. (they were really small when they should have been fairly large). Not sure how it got that way though.

  • 34 MI // Aug 22, 2013 at 8:52 am

    Getting rid of all conditional formatting helped! Thanks :) .

  • 35 paul // Sep 18, 2013 at 4:48 pm

    Thanks everyone for the tips – especially on conditional formatting. I have a VBA application that populates a spreadsheet from a text file, setting up totals etc. I’ve been trying for about two years to work out why it was so much slower on 2010 than 2003, and even recommended that clients keep one PC on 2003 and use it for the loading. Now I’ve removed the coditional formatting (only there to highlight a fairly rare situation) and it’s now running at the same speed on both. Thanks!!

  • 36 Johan // Oct 10, 2013 at 1:04 pm

    Hi. I have a 12 MB excel file with lots of vlookups to internal worksheets as well as to another large workbook. Both are on a server. it opens fairly quick on other pc’s but is slow (5-7 mins) on my laptop (HP Elitebook ). can someone please help to make it faster

  • 37 malay // Nov 22, 2013 at 1:01 am

    normally when i entered a figure in a cell cursor releases after approx 35 sec. My worksheet have 19 sheets, upto 9 sheets no problem after that it will slow any work. How to solve the problem?

  • 38 footy // Nov 22, 2013 at 1:40 am

    # saved my life

    Thank you SO much!

  • 39 footy // Nov 22, 2013 at 1:41 am

    #5 saved my life

    while copying and pasting and moving things around it created too many uncessary conditional formattings (almost 300 of them?)

    after i remove them all it’s a breeze

    Thank you SO much!

Leave a Comment