If there is one thing that unites us all, it has to be the frustration to keep up with a slow excel spreadsheets. While the impact on the performance may be negligible when there is less data, it becomes more profound as you add more and more data/calculations to the workbook. 9 out of 10 times, an Excel user would complain about the slow Excel spreadsheets. And there is hardly anything you can do about it. Well, that’s NOT completely true. The way Excel has been made, it does get slow with large data sets. However, there are many speedup tricks you can use to improve the performance of a slow Excel spreadsheet. 10 Tips to Handle Slow Excel SpreadsheetsHere are 10 tips to give your slow Excel spreadsheet a little speed boost, and save you some time and frustration (click to jump to that specific section).  Avoid Volatile Functions (you must).
 Use Helper Columns.
 Avoid Array Formulas (if you can).
 Use Conditional Formatting with Caution.
 Use Excel Tables and Named Ranges.
 Convert Unused Formulas to Values.
 Keep All Referenced Data in One Sheet.
 Avoid Using Entire Row/Column in References.
 Use Manual Calculation Mode.
 Use Faster Formula Techniques.
Volatile formulas are called so because of a reason. Functions such as NOW, TODAY, INDIRECT, RAND, OFFSET etc. recalculate every time there is a change in the workbook. For example, if you use NOW function in a cell, every time there is a change in the worksheet, the formula would be recalculated and the cell value would update. This takes additional processing speed and you end up with a slow excel workbook. As a rule of thumb, avoid volatile formulas. And if you can’t, try and minimize its use. 2. Use Helper ColumnsHelper columns are one of the most underrated design constructs in Excel. I have seen many people shy away from creating helper columns. DON’T DO That. The biggest benefit of using ‘Helper Columns’ is that it may help you avoid array formulas. Now don’t get me wrong. I am not against array formulas. Rather I believe these could be awesome in some situations. But it when you try to do it all with one long formula, it does impact the performance of your Excel workbook. A couple of array formulas here and there shouldn’t hurt, but in case you need to use it in many places, consider using helper columns. Here are some Examples where helper columns are used:  Automatically Sort Data in Alphabetical Order using Formula.
 Dynamic Excel Filter – Extract Data as you Type.
 Creating Multiple Dropdown Lists in Excel without Repetition.
Array formulas have its own merits – but speed is not one of those. As explained above, array formulas can take up a lot of data (cell references), analyze it, and give you the result. But doing that takes time. If there is a way to avoid array formulas (such as using helper column), always take that road. I absolutely love conditional formatting. It makes bland data look so beautiful. Instead of doing the comparison yourself, now you can simply look at a cell’s color or icon and you’d know how it compares with others. But.. here is the problem. Not many Excel users know that Excel Conditional Formatting is volatile. While you may not notice the difference with small data sets, it can result in a slow excel spreadsheet if applied on large data sets, or applied multiple times. Word of advice – Use it Cautiously. Also read: How to Remove Conditional Formatting in Excel (Shortcut + VBA) 5. Use Excel Tables and Named RangesExcel Table and Named Ranges are two amazing features that hold your data and makes referencing super easy. It may take a while to get used to it, but when you start using it, life becomes easy and fast. In creating datadriven dashboards, it is almost always a good idea to convert your data into an Excel Table. It also has an added advantage of making your formulas more comprehensible. For example, what’s easier to understand? =Sales PriceCost Price OR =SUM(A1:A10)SUM(G1:G10) This is a nobrainer. When you don’t need it, don’t keep it. Lots of formulas would result in a slow Excel workbook. And if you have formulas that are not even being used – you know who to blame. As a rule of thumb, if you don’t need formulas, it’s better to convert them into a static value (by pasting as values). Read More: How to quickly convert formulas to values. 7. Keep All Referenced Data in One SheetThis may not always be possible, but if you can do this, I guarantee your Excel sheet would become faster. The logic is simple – formulas in your worksheet don’t have to go far to get the data when it is right next to it in the same sheet. 8. Avoid Using the Entire Row/Column as Reference (A:A)The only reason I have this one on the list is that I see a lot of people using the entire row/column reference in formulas. This is a bad practice and should be avoided. While you may think that the row/column only has a few cells with data, Excel doesn’t think that way. When you reference an entire row/column, Excel acts it as a good servant and check it anyways. That takes more time for calculations. 9. Use Manual Calculation ModeI am just repeating what million people have already said in various forums and blogs. Using Manual calculation gives you the flexibility to tell excel when to calculate, rather than Excel taking its own decisions. This is not something that speeds up your Excel workbook, but if you have a slow Excel spreadsheet, it definitely saves time by not making Excel recalculate again and again.  To switch to manual mode, go to Formula Tab –> Calculation Options –> Manual (press F9 key to recalculate)
Excel gives you a lot of formulas and formulacombos to do the same thing. It is best to identify and use the fastest ones. Here are a couple of examples:  Use IFERROR instead of IF and ISERROR combo (unless you are using Excel 2003 or earlier, which does not have IFERROR).
 Use MAX(A1,0) instead do IF(A1>0,A1,0) – This is a cool tip that I learned from Mr. Excel aka Bill Jelen. His research shows that MAX option is 40% faster than IF option (and I am ready to take this stat on his face value).
 Use the INDEX/MATCH combo, instead of VLOOKUP – This may raise a lot of eyebrows, but the truth is, there is no way VLOOKUP can be faster if you have 100’s of columns of data. The world is moving towards INDEX/MATCH, and you should make the shift too.
[If you are still confused about what to use, here is a headonhead comparison of VLOOKUP Vs. INDEX/MATCH].  Use — (double negatives) to convert TRUE’s and FALSE’s to 1’s and 0’s (instead of multiplying it by 1 or adding 0 to it). The speed improvement is noticeable in large data sets.
Is this an exhaustive list? Absolutely NOT. These are some good ones that I think are worth sharing as a starting point. If you are looking to master ExcelSpeedUp techniques, there is some good work done by a lot of Excel experts. Here are some sources you may find useful:
 75 Speedup tips by Chandoo (smartly done by crowdsourcing).
 Decision Models Website.
 Mr. Excel Message Board (explore this and you would find tons of tips).
I am sure you also have many tips that can help tackle slow excel spreadsheets. Do share it with us here in the comment section. I also have one request. The pain of working with a slow excel spreadsheet is something many of us experience on a daily basis. If you find these techniques useful. share it with others. Ease their pain, and earn some goodness 🙂 You May Also Like the Following Excel Links:  24 Excel Tricks to Make You Sail through Daytoday work.
 10 Super Neat Ways to Clean Data in Excel Spreadsheets.
 10 Excel Data Entry Tips You Can’t Afford to Miss.
 Creating and Using a dropdown List in Excel.
 Reduce Excel File Size.
 How to Recover Unsaved Excel Files.
 Free Online Excel Training (7part video course)
 Arrow Keys not Working in Excel  Moving Pages Instead of Cells
FREE EXCEL BOOK Get 51 Excel Tips Ebook to skyrocket your productivity and get work done faster Name Email YES  SEND ME THE EBOOK 30 thoughts on “10 Tricks to Fix Slow Excel Spreadsheets (Speedup Excel)”krist0ph3r March 2020 at 12:41 am I’ve noticed my Excel runs extremely slow when I have lots of formulae and am playing around with filters. Every change in filter results in formulae recalculating, which is unexpected because the filters are simply showing/hiding data and not changing contents. Is there a way to turn this off (other than switching to manual calc) Kanhaiyalal Newaskar February 2020 at 1:44 pm Sir, 24th Feb.2020. I must thankful to you find such useful notes. Hoping to receive such valuable notes in future too. Kanhaiyalal Newaskar. Gabriel January 2020 at 12:49 am Those are all great suggestions. To help know what parts of your spreadsheet are slow you can also download this addin to help you measure how long it takes to calculate your spreadsheet. Apurva D October 2019 at 1:23 pm Hi! This helps a lot, thanks for putting it all in one place! 🙂 Hacker0007 August 2019 at 11:07 am That was very helpful content, but i will add something to it. if you use IFERROR instead of IF the problem it will cause is, Iferror first evaluate the value which is formula and if it cause an error then it evaluate the false argument which is waste of resources and time. Pravin Singh March 2019 at 7:46 pm Thanks a lot for your support Kenneth Drayton January 2019 at 6:22 am So in other words, don’t use Excel! You just wiped out most of the most convenient tools. I love Excel but they didn’t think this through, I say so as an Electronics Engineer (both s/w & h/w). Sean Kane October 2017 at 9:38 pm i had a super slow (but <1MB) file and i could not figure out why…. it was #8! completely fixed it when i got rid of entire column references on one of the sheets. TY! Sumit Bansal October 2017 at 10:09 am Glad you found the tips useful Sean! Heidi Eugster September 2020 at 2:14 am how did you find which sheet and column ?/
Excel Everest March 2017 at 12:20 pm The Formula shown above was really helpful for me learn Excel. Everest offers Microsoft Excel Tutorials: Our administrations are Microsoft Excel Training, Online Excel Training,Excel Courses, Excel Classes, Learn Excel. Excel Everest January 2017 at 11:52 am l like this blog Excel Everestoffers Microsoft ExcelTutorials: Our administrations are Microsoft Excel Training, Online Excel Training,Excel Courses. Henrik November 2016 at 9:28 pm Hi Sumit Thanks for the list. I agree to most of your points. But especially no. 8 doesn’t make a difference (at least not any more). Also the effect of most of the points under no. 10 are very small (VLOOKUP vs. INDEX/MATCH for instance). I measured the difference in calculation time: There seem to be other factors even more relevant. The whole study is linked here: http://professorexcel.com/performanceexcelstudy/ Splonds February 2019 at 5:10 pm No.8 makes a massive difference and has thankfully stopped all of my company’s large sheets repeatedly hanging and then crashing excel.
Nelson October 2016 at 12:44 am My dear friend. Thankyou. Conditional formula was the culprit. Best regards. khairul June 2016 at 11:03 pm Hallo Mr. Sumit. Its really helpful but how its possible in match/index function to avoid selection of whole column and row when data will be aded in next time. As it is not possible to change formula anytime. Alok Morya May 2016 at 11:07 am What is helper Column, Can you please describe…. ? Selcuk Olzker March 2016 at 10:41 pm Useful but this seems aimed at “its the users fault”, which might be true for people using particular worksheets. Its not aimed at a problem where EVERY worksheet has slow entry, which is usually do to poor or hostile software development by Microsoft and requires monkeying with kernel settings and disabling features like network and help settings, product activation etc, and generally other garbage M$ and NSA uses for its various protectionist schemes and to spy illegally on its users. Sumit Bansal March 2016 at 4:40 pm Thanks for commenting Selcuk.. It’s interesting what you have shared here. Definitely, there are a lot of systemic issues that are practically user independent and makes the workbook slow. I really hope MS will come up with more powerful Excel that can handle large data sets and still be fast. Selcuk Olzker March 2016 at 2:33 am Thanks. Yes basically i7 processors and GHz speeds, our computers software should have lightning fast with data entry, saves, folder transfers, etc…todays computers have real processing power of the supercomputers of yesteryear…yet somehow they don’t. Why? Well, the reason is simple…M$ maximizes this extra power for its own purposes, rather than the users, which on the scale it is doing it, is basically theft on a scale much larger than socalled software pirates which is all you hear about in the media. The real pirates (of our CPU cycles and zombified computers) is M$ itself and whatever third party they are acquiescing to (RIAA,NSA,etc). The practice should be considered illegal by any reasonable definition of the law, however M$ just cowardly hides behind proprietary kernel code and encrypted data transfers to Redmond and elsewhere. Recently, I found M$ downloading 3GB of Winn10 software onto my personal computer. Did they pay for the bandwidth…no..did they let me know they were doing this illegal thing…no. They even place it in a hidden protected file. Its a huge waste of economic productivity, which depends so critically on computers today. Things are so rotten at M$, the governement really should consider breaking them up. All those talented software engineers just producing one garbage product after another because all the brass cares about is money and ruining the competition by its resting on its laurels with patent thickets and protectionism rather than concentrating on making a better product. Oscar May 2017 at 12:32 am chill pill time? Selcuk Olzker May 2017 at 1:59 am There is no time to chill. I’ve literally spent “years” of wasted time and so have many others working with Microsofts user hostile software which waste and steal the users purchased hardware for its own purposes. The cost to our economy is far more than their market share. The company should be dismantled for everyones benefit, as the antitrust judges orderered over a decade ago. Its long overdue. Linux June 2019 at 4:30 am You are off your rocker. Move to Open or Libre Office, then you may find peace and tranquility in knowing it is not Microsoft taking your sanity…
Jon Acampora May 2014 at 9:00 pm Hi Sumit In regards to #8, I recently learned that whole column/row references are ok for some of the functions. Some of the functions like SUM or SUMIF will automatically recognize the last used row/column. The following Microsoft article explains this in more detail. http://msdn.microsoft.com/enus/library/ff726673(v=office.14).aspx I still agree with your suggestion though. As a best practice I still don’t recommend referencing whole rows/columns because I am not exactly sure how the function determines the “last used row/column”. As we know with VBA, this can sometimes be misleading. The last used row/column could be much farther down on the sheet if data has been deleted. And this means the function could be including unused rows/columns in the calculation. However, I think it’s good to know that some of the functions do consider this. I will have to see if there is a full list of the functions that do recognize the last used row/column. Thanks for the great tips! Sumit Bansal May 2014 at 9:38 pm Jon – That’s good learning for me too.. Thanks for sharing 🙂 You are right, its good to know of formulas that can handle this Chris Macro May 2014 at 1:29 am I’m right there with Jon on #8. I always use whole column/row references with Vlookups and SumIfs. This ensures that if my data range changes, my formulas are guaranteed to pick up the added data. I have run into too many headaches with formulas not picking up all the data (especially with repetitive data extractions). But NEVER use entire column/row references with SumProduct!!! I found out the hard way this that function 🙁
How do I fix a slow opening Excel file?
Solution 1: Start Excel in Safe Mode
To fix the Excel slow to respond issue start Excel file in safe mode. To do so follow the steps given in the article: Close Excel completely > hit Windows + R > then in the Run dialog box type excel –safe > press Enter.
How can I speed up my Excel file opening?
10 Tips to Handle Slow Excel Spreadsheets. Avoid Volatile Functions (you must).. Use Helper Columns.. Avoid Array Formulas (if you can).. Use Conditional Formatting with Caution.. Use Excel Tables and Named Ranges.. Convert Unused Formulas to Values.. Keep All Referenced Data in One Sheet..
Why is Excel struggling to open?
Another possible reason that causes Excel files to not open could be that your Microsoft Office application is corrupt and need to be repaired or reinstalled. But since reinstalling is a little more work, let's try the repair option first.
