10 Tricks to Fix Slow Excel Spreadsheets (Speed-up Excel)
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 speed-up tricks you can use to improve the performance of a slow Excel spreadsheet.
10 Tips to Handle Slow Excel Spreadsheets
Here 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.
1. Avoid Volatile Formulas
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 Columns
Helper columns are one of the most under-rated 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:
3. Avoid Array Formulas
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.
4. Use Conditional Formatting with Caution
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.
5. Use Excel Tables and Named Ranges
Excel 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 data-driven 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 Price-Cost PriceOR=SUM(A1:A10)-SUM(G1:G10)
6. Convert Unused Formulas to Static Values
This is a no-brainer. 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 Sheet
This 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 Mode
I 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)
10. Use Faster Formulas Techniques
Excel gives you a lot of formulas and formula-combos 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 head-on-head 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 Excel-Speed-Up techniques, there is some good work done by a lot of Excel experts.
Here are some sources you may find useful:
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:
When your Microsoft Excel spreadsheet slows to a crawl, you can’t help but notice. It may take longer to open and save your files, longer for Excel to calculate your formulas, and longer for the screen to refresh after entering data, or sorting and formatting the cells. System memory is the other issue that relates to Excel’s slowness.
Slow spreadsheets take longer to manage and, as always, time is money. We’ll show you how to tackle this problem.
When Excel spreadsheets get too big
Excel is capable of creating a very big spreadsheet, but the bigger it gets, the more memory is needed to keep it open on your PC.
In the current version of Excel, each spreadsheet has 1,048,576 rows and 16,384 columns (A1 through XFD1048576). Each cell can hold a maximum of 32,767 characters. I would not advise pushing these limits.
The number of records (rows), fields (columns), and formulas can slow down performance considerably. Every time you add new records, then press the Enter key—or use features such as Sort, Format cells, or Insert/Delete Columns or Rows—Excel recalculates all those formulas. This can cause a lag time of several seconds or more between each process. Using a lot of graphical elements can also hinder performance.
One solution, and one that I highly recommend, is to keep your spreadsheets small and tight, with fewer fields and, if necessary, fewer records. You can accomplish this by creating multiple spreadsheets in a single workbook, with links or three-dimensional formulas. You could also create Relational Database spreadsheets that connect your tables with unique, key fields.
Turn on Manual Calculation and use F9
Another solution is to turn off the Automatic Workbook Calculation option, instead using the Function key F9. When Manual Calculation is selected in the Calculation Options, Excel withholds calculating your formulas until you press F9.
1. Select File > Options > Formulas.
2. In the first section: Calculation Options under Workbook Calculation, click the Manual button.
3. Check the Recalculate Workbook Before Saving box if you want to ensure that the spreadsheet calculation is always current. Or uncheck this box if you plan to calculate the spreadsheet manually using the F9 key before exiting.
5. When finished, click OK.
Excel memory limits
Users constantly ask me: Why does my spreadsheet say “Excel cannot complete this task with available resources. Choose less data or close other applications?” Similar errors include “Not enough System Resources to Display Completely,” or “There isn’t enough memory to complete this action. Try using less data or closing other applications,” or just “Out of Memory.”
Although memory does not affect Excel’s calculation or manipulation speed, the size of your database (number of columns and rows used) is affected by the amount of available RAM in your system. Remember, just because your computer has 8GB of RAM, that doesn’t mean you have that much available to work with.
Excel has its own memory manager and memory limits. The 32-bit version has a limit of 2GB of virtual memory, while the 64-bit version offers up to 8TB of virtual memory. Contrary to some rumors, those numbers include the software itself, plus any add-in programs you have installed.
And that’s just in Excel. Other demands on your system’s memory include the OS, all the other applications that are currently open on your computer, plus a dozen other hidden processes such as DLLs, drivers, and a long list of .exe (executables) that are running in resident memory and/or in the background. Graphics, charts, formulas, and features such as the spell checker, sorting, and printing also consume memory.
For the many users still working with the 32-bit version of Excel, if your spreadsheets are less than 2GB and you’re still receiving memory error messages, try closing all other programs that are running (including the Internet and your email program) to gain additional working memory.
When it’s time to move from 32-bit to 64-bit Excel
If the performance and memory tips above both fail to increase your system’s performance or reduce the number of memory errors, then maybe it’s time to switch to the 64-bit version of Excel. This version does not limit your file sizes, but instead enforces limits only by available memory and system resources. This means if your system has 8GB of memory, Excel can access all of that minus whatever the system uses.
If you’re considering a change from Excel 32-bit to Excel 64-bit, here’s what to keep in mind:
1. Check out the Large Address Aware update. Microsoft rolled out this patch in June 2016, for 2013 and 2016 Excel versions. This update alters the 2GB limit on address space to 4GB when installed for the 32-bit version of Excel in the 64-bit version of Windows. For 32-bit Excel running in 32-bit Windows, the 2GB address space limit is increased to 3GB.
2. Other files are affected when you install this update: For example, for 32-bit Excel with 32-bit Windows, you must make a change in your boot file. Be sure to read Microsoft’s documentation on the Large Address Aware update before you install anything or make any changes.
3. 64-bit Office only works with 64-bit Windows. You cannot run the 32-bit and 64-bit versions of Office on the same computer. If you attempt this, Microsoft displays an error message.
4. If you want to upgrade from your 32-bit version to the 64-bit version, you must uninstall and then re-install Office. The reverse is also true.
32-bit vs. 64-bit Excel: Features you’ll lose
Despite the performance beneifts of 64-bit Office, Microsoft actually recommends the 32-bit version of Office for most users, because of its greater compatibility with other applications (particularly third-party add-ins). Also, some of Office’s application features are not supported in the 64-bit OS, such as:
1. The legacy versions of Equation Editor and Equation Builder are not supported
2. The Word Add-in libraries are also not supported (many dozens available online for free or for a minimal cost).
3. Some ActiveX controls and some VBA codes are not compatible.
4. Some database files in Microsoft Access have source code issues.
5. Outlook MAPI applications must be recreated, and
6. The Graphics Device Interface (GDI) rendering may have performance issues due to incompatibilities between the 32-bit and 64-bit devices.
What To Do When Excel Opens . . . Very . . . Slowly . . .
Followup 07/2017: A Possible Fix For The Excel Slow File Opening Bug
This bug only affects a small number of people, but it’s an interesting one because it’s been a problem for almost a year and there’s still no clear understanding of what’s going on.
It happens on Windows 10 computers running Excel 2013 or Excel 2016.
On some computers, double-clicking to open a .XLSX spreadsheet file is followed by a 30-60 second delay before the Excel logo appears and the spreadsheet loads normally.
There are some strange things about what happens.
• There is no delay if Excel is already running when you double-click the file. If you remember to start Excel first, or if it is already running with another file open, then the clicked file will load immediately.
• This only affects Excel. All other programs (including Word) are completely normal.
• During the 30-60 second delay, nothing is happening. I mean, nothing. People have sniffed with Process Explorer and Task Manager and looked at memory use and there is zero indication that anything is proceeding under the hood. Thirty seconds later, out of nowhere, Excel starts up.
There is very little pattern to when this problem occurs, as far as I can tell from Googling around. It has happened to me on my desktop and on several laptops; on computers upgraded from Windows 7/8 and computers with clean installs of Windows 10; on computers with Office 2013 and Office 2016. I replaced my desktop computer in part to get a fresh start on this problem; it turned up almost immediately and has never left. Yet I have only rarely heard about it from clients, and there are only scattered reports online.
In the forums where people have discussed this problem (for example here, here, and here), there have been various theories that have been floated and discarded. At first I thought it only happened when I was clicking on files stored on a network shared folder instead of my local hard drive, as suggested in a few forum posts. I actually moved all my files from a Synology NAS to my computer as part of troubleshooting a few months ago and was disappointed to discover it didn’t make any difference.
Running into it consistently on my own computers made me suspect an add-in or third-party program that was interfering somehow but there doesn’t seem to be any common thread between the programs I run and the ones running on all the computers of other people with the same symptoms.
There are a number of troubleshooting tips that apparently work for some people. If this is a problem for you, it won’t hurt to try them.
• Do a quick repair of the Office programs (Control Panel / Installed Programs / Change).
• If you use a third-party antivirus program, disable it and do some testing. (I use Windows Defender, built into Windows 10 by Microsoft.)
• Check add-ins that run with Excel (File / Options / Add-ins) to see if anything looks dicey.
• Experiment with the options in Excel to “disable hardware graphics acceleration”, and “ignore other applications that use Dynamic Data Exchange.” They don’t seem to make a difference for most people with this problem but you never know.
People have tried creating new user profiles. One person thought the problem reappeared after running Edge for the first time, as if the web browser had somehow affected Excel.
There’s only one solution that has consistently worked for some people. Rod Trent wrote an article about it over at Windows Supersite after it fixed the problem for him. It has helped many people in the forums. It’s simple and it makes no sense at all.
You may be able to fix the slow Excel problem by turning off Cortana.
Cortana is the built-in search assistant in Windows 10. There’s an option to summon Cortana by saying “Hey Cortana” out loud, like Siri and Google Now. Turning off “Hey Cortana” and restarting the computer cures the Excel bug for some people.
Others report that their problem is fixed if they turn off all the Cortana options – open Cortana and go to Settings and flip all the sliders to Off.
As it happens, the Cortana fix has never worked for me. I’ve experimented with all the other possible fixes in the last year and nothing has made any difference. It doesn’t happen all the time but that’s part of the mystery, because I have no idea when I’ll have an unexpected delay while a client waits on the phone for me to open up their records. It’s maddening.
This has been a problem ever since Windows 10 was released and it continues on my computer today. I’ve given up hope of a fix. Instead I’ve had to develop the habit of starting Excel before I try to open a spreadsheet. That’s not a terrible workaround but I still run into this bug nearly every day when I forget.
The Office programs are solid and stable and polished, the product of more than twenty years of steady development. It is just odd to have a bug that leaves people fuming every day with no response from Microsoft, and no fix except turning off one of Windows 10’s flagship features.
These are odd times in our Windows technology world.
How to Resolve "Excel Running Slow"?
Today, Excel running slow is the most common problem faced by users. Recently, a lot of users have a complaint about this problem. There is no doubt that Microsoft Excel is a useful and powerful program, but it starts to run slow when you are dealing with large data set. It is a frustrating problem that occurs randomly. The frustration of the users increases when their excel files get corrupted inevitably. So, if you are also going through this same situation, then you have come to the right page. Here, in this post, you will get to know what are the reasons and symptoms behind the problem and how you can fix it effectively.
Part 1: Causes of "Excel Running Slow":
You might be wondering to know what the probable reasons behind Excel processing slow are and so, here are all major causes of Excel running very slow.
1. Invalid names in Name Manager
Excel has a "Name Manager" feature to manage names and the name ranges in Microsoft Excel that has numbers, letters, and special characters. And it might be possible that Excel can't able to resolve the names because as worksheets grow, you also end up with more and more name referencing. The best thing you can do in this type of situation is to move to the name manager and remove invalid names.
2. Excessive Volatile Functions
The excessive volatile functions can also probably slow down your Excel when you are tackling large datasets. So, if you are using volatile functions such as VLOOKUP excessively, then it can be the reason why your Excel running slow. That's also the reason why most businesses don't prefer volatile functions when it comes to large data sets.
3. Unresolved Formulas
Another reason for Excel lagging is Unresolved Formulas. So, if you have a sheet in your excel whose cell has a value>0, then it means a cell having unresolved formulas. And that can also be the reason why you have encountered this problem.
4. Column References
Sometimes, not giving the optimizing column references can also slow down the performance of Excel. You need to optimize references. It is recommended to minimize the number of cells you are referencing in your Excel worksheet.
Moreover, if you are using a formula like VLOOKUP that has to look at million records, then it will certainly slow down your Excel performance. Thus, it is also recommended not to use a formula that needs to look at a lot of records. Otherwise, you will face a performance issue in Excel.
5. Too big spreadsheet
Are you having a too big spreadsheet in your Microsoft Excel? If your answer is yes, then it is also one of the reasons behind Excel working slow. Many users have a bad habit of storing a lot of data in one sheet. Moreover, if you are using volatile functions along with too big spreadsheets, then it will slow down your Excel performance. Thus, in this scenario, all you have to do is to reduce file size in Excel to come out of this problem.
6. Automatic Formula Calculation
Automatic Formula Calculation is also the cause of Excel running slowly. In the case of automatic calculation, formulas are recalculated whenever you make any change. This sometimes makes Excel slow and that's why it is recommended to enable the manual calculation.
Part 2: Symptoms of "Excel Running Slow":
Now, let's explore what can be the symptoms of Excel running slow, and below, we have listed every possible sign of the problem.
1. Excel processing slow
The first and most common sign of Excel running very slow is that it is processing slow. It is taking too much time to process the operations you are doing in your Excel sheet. The reasons why your Excel processing is slow can be because of Array formulas or volatile functions you are using.
2. Excel slow calculating
Another symptom of Excel running very slow is there is a performance issue in doing the calculation. Poor calculation speed further affects productivity and enhances user error. The reasons why your Excel is calculating formulas slowly maybe you are using volatile functions in excess or not keeping all referenced data in one sheet. So, avoid volatile functions to get rid of Excel's slow calculating problem.
3. Excel is slow to open files
If your Excel is taking a very long time to open certain files, then it directly shows that your Excel is running slow and you need to do something to fix it. There can be many reasons why Excel slows to open files. First of all, it can be because you have stored a lot of data in one file or you have used too many volatile or complex functions in the Excel file.
4. System Memory
There is no doubt that Excel has enough capability of creating big sheets, but as the sheet gets bigger, it requires more memory. If your system memory is low, and you are having a too big spreadsheet, then it will probably slow down your Excel. Thus, you need to manage your spreadsheet size as per your system memory. Otherwise, you will have to face performance complications.
Part 3: How to Fix "Excel Running Slow"?
You now are well familiar with the causes and symptoms of Excel running very slow. Let's move towards its fixes to speed up Excel.
Solution 1: Keep Spreadsheet Small
Too big spreadsheets in Excel can slow down the Excel speed because big Excel spreadsheets also take large space on your system.
You also know that you need enough free storage space on your system so that you can do your task on the system smoothly and efficiently.
Here are some ways to reduce the file size in Excel:
- Save Excel File as the .xlsb file format
- Eliminate Unused Cells
- Eliminate Cell Formatting
- Remove Unwanted Formulas
- Compress Images
Solution 2: Enable Manual Calculation
Another solution to fix the problem is enabling the Manual Calculation mode in your Excel. When manual mode is turned on, the Excel will not perform calculations until you don't press F9. The Automatic calculation mode will make Excel do calculations automatically and that can slow down the Excel speed to a great extent. Thus, the best thing you can do is turning on the Manual Calculation Mode. Below are the steps on how
you can do it to fix Excel working slow:
Step 1: To begin the process, choose "File" and then, "Excel Options" and then, "Formulas" tab.
Step 2: After that, go to "Calculation Option" and then, select the "Manual" button under the "Workbook Calculation.
Step 3: Also, check the "Recalculate Workbook before saving" option if you like to make sure that the workbook calculation is always current. You can uncheck it if you want to calculate the formula using the F9 key before you exit.
Solution 3: Avoid Volatile Formula
As we told you earlier, volatile formulas are one of the reasons for this problem. So, the best thing is to get rid of the volatile formula to speed up your Excel. Some examples of volatile formulas are NOW, INDIRECT, OFFSET, etc. For instance, if you use the INDIRECT formula in a cell, and when you make any change, then the formula will also be recalculated.
So, the volatile functions take more processing speed, and then, you end up facing the slow Excel speed. Thus, avoid using the volatile formula or if you can't, then try to minimize its use.
Solution 4: Convert Formula to Value - change the unused formulas to static values:
There are many formulas in Excel that lead to its slow performance. And thus, if you have unused formulas in Excel, then it is better to change them to static values. There are many ways to convert unused formulas to static values in Excel. Here are shown using the Paste special method.
Step 1: Choose the cells you want to change formulas to values. After that, copy the cells using CTRL+C.
Step 2: Then, move to "Home">" Clipboard">" Paste">" Paste Special".
Solution 5: Clear Rules - avoid using the entire row/column as a reference (A: A)
When you reference the whole row or column as you think it consists of a few cells, Excel checks the reference row or column anyways. This takes a lot of time to do calculations and that ultimately slowdowns the Excel speed. Thus, if you want to fix the Excel processing slow, then you need to avoid using the entire row/column as a reference.
Solution 6: Reference Fewer Cells
A cell reference is used to determine a particular cell or cell range in the sheet. It can be used in function so that Excel can find the data easily you want in function to calculate. So, if you refer to a lot of cells, then it can affect the Excel performance. That's why it is always recommended that you should reference fewer cells so that Excel calculate formula or function smoothly.
Solution 7: Repair Excel File
If Excel slows to open files, then you can repair Excel files to resolve the problem with ease. Luckily, there are a lot of third-party tools available that can help you to do this. The Internet is full of third-party tools and they make it easy for users to fix Excel errors and problems. By the way, you may also see another Excel issue "out of memory".
Part 4: How to Repair Excel File?
When it comes to repairing an Excel file, the Stellar Excel Repair software stands out from the huge crowd. As you need a powerful tool to repair Excel files and this software can make it possible.
1. Free Download Stellar Excel Repair
If you are looking for third-party software for repairing damaged or corrupt Excel, then don't go further from the Stellar Excel Repair. It is the most efficient Excel file repair tool that can help you to retrieve everything from a damaged file to a new Excel file with ease. The Stellar Excel Repair comes with a lot of remarkable features that make it the most reliable tool. It can aid you to recover charts, cell comments, formulas, tables, etc from the corrupted Excel file.
With it, you can repair both single and multiple files (XLS/XLSX). This all-in-one tool can fix every kind of Excel corruption errors. Moreover, it is compatible with a lot of MS Excel versions, such as 2016, 2013, etc. The software can also help you to come out of the Excel lagging issue due to large spreadsheets.
Here are key features of Stellar Excel Repair:
- This software can aid you to repair a batch of Excel files at the same time in a single attempt.
- It has a simple and easy-to-use interface and that's why you can easily the tool even if you are a beginner-level user.
- With this tool, you can repair your damaged Excel file and retrieve all properties of the file.
- It will aid you to recover Excel files without any trouble or hitch.
2. How to Repair MS Excel File
Here is the step-by-step guide on how to repair MS Excel Files with the help of Stellar Excel Repair software.
- Repair 4 types of files, namely Word, Excel, PowerPoint, and Zip.
- Offer excellent search options to find corrupted files instantly.
- Preview and recover all the data contained in the corrupted files.
- Repair files are corrupted due to different causes and from all devices.
Step 1: Download and Install Software:
To begin the process, download the software from its official site on your computer. After installing it successfully, run it.
Step 2: Choose Excel File:
After that, you need to choose the Excel file you want to repair. To do this, tap on the "Select File Button". Or tap on "Search Folder" to look at the file in a particular folder. Or tap on "Search File(s)" to look at the file in a particular drive.
Step 3: Start Scan Process:
Once selected the Excel file, click on the "Scan" button to begin the scanning process. When the scanning process is completed, you will get a preview of the repaired file.
Step 4: Save Repaired Excel File:
Thereafter, tap on the "Save" button to save the Excel file that the software has repaired. You can save the repaired Excel file either at default or a new location.
Step 5: Repair Process Completed:
Once you saved the repaired Excel file, you will get a prompt message that says your Excel file repair process is finished.
Part 5: Tips for Avoiding "Excel Running Slow":
Want tips to avoid Excel working slow problems? Here are some effective tips that can aid you to avoid the problem in the future.
1. Keep Everything In Single Workbook
When you keep everything in a single workbook rather than using multiple workbooks, it will probably aid you to avoid the slow performance in Excel. More workbooks mean more references and that's why when you can multiple sheets, your Excel slowdowns.
2. Sort Data
Another common reason why Excel speed becomes slows is the lack of sorting. To have great Excel performance, you need to sort the data in a well-mannered way.
3. Don't Use Volatile Formulas
The volatile formulas are recalculated whenever you make a change in your sheet. The examples of the volatile formulas are NOW, INDIRECT, etc. When sheets in your Excel consist of many volatile formulas, your Excel speed becomes very slow. Thus, it is better to get rid of volatile formulas to optimize Excel performance and speed.
4. Enable Manual Calculation Mode
When you enable the manual calculation mode, you get an excess to allow Excel to do the calculation, rather than Excel do it automatically.
This will certainly improve the Excel speed and help you to come out of the Excel lagging problem with ease.
5. Avoid Using the Entire Column/Row as Reference
If you have a bad habit of using the whole row or column as a reference, then you should get rid of it as soon as possible. Otherwise, you will probably go through the performance issue in your Excel.
6. Disable Excel Add-ins
No doubt, the Excel Add-Ins are helpful, but sometimes, they slow down Excel, and then, Excel leads to freezing and crashing. That's why it is better to disable the entire Excel Add-ins. To do this, move to "File">" Options">" Add-Ins".
That's all on how to come out of the Excel running slow problem. Here are probable methods that can fix the problem. No doubt, it is very annoying when your Excel speed becomes slow, but still, there are a lot of ways that can aid you to resolve the problem. And if in any case your Excel file gets corrupted or damaged, then you can use Stellar Excel Repair software that can restore everything from a damaged file to the new one. As it is a free tool and thus, give it a try and you will get to know how powerful it is to fix all Excel problems.
Loading slow excel
"Request Was Taking Too Long" when you open a workbook in Excel Online
- 2 minutes to read
- Applies to:
- Excel Online
This article was written by Tom Schauer, Technical Specialist.
When you open a specific workbook in Excel Online, you receive a "Request Was Taking Too Long" error message.
This issue occurs if the workbook takes longer than 30 seconds to open in Excel Online.
Although there can be many reasons why a file is slow to open, excess formatting is a very typical cause.
The Excel client team has written an excellent article about how to clean up a workbook so that it uses less memory. We can use the same method to avoid the "Request Was Taking Too Long" errors in Excel Online. This is because all the steps that are mentioned in the referenced post relate to tidying up a file to make it consume fewer resources, and open and run more quickly.
A very complex workbook that contains many workbook elements may take a long time to open. The following are some common problem areas to consider when you troubleshoot this issue:
- Worksheet protection: Removing the worksheet protection probably isn't the best option because this method prevents users from being able to edit certain cells that most likely contain formulas.
- VLOOKUP formulas: If you remove VLOOKUP formulas, you may break the calculation chain of your workbook. Therefore, this most likely isn't a viable solution.
- Defined names: You probably can't remove defined names because those may be necessary for data validation or an Excel Web Access web part that uses those named items.
- Styles: When a workbook has too many cell styles, shapes, or formatting, it can cause Excel Online to take many times longer to open a file than usual. To see the cell styles for an Excel workbook, locate the Home tab, and then click the Down arrow in the lower-right corner of the Styles group.
To view existing styles in a workbook, locate Home tab > Styles > Down arrow.
In a new workbook, your list of styles should resemble the following list.
Here are examples of duplicate or excessive formatting.
Example 1: This example has 3,284 duplicate formatting items.
Example 2: This example has 11,837 excessive formatting items.
These excessive cell styles, mixed with the many other elements in this workbook (such as sheet protection, defined names, and VLOOKUP formulas), cause the loading process to exceed the 30-second time-out duration that is set by Excel Online.
To have Excel to clear excess or duplicate formatting, use the Inquire add-in. To load the Inquire add-in for Excel, follow these steps:
Select File > Options > Add-ins.
On the Manage menu at the bottom of the page, select COM Add-ins, and then select Go.
After the COM Add-ins window opens, make sure that the check box next to Inquire is selected, and then click OK.
You should now see the Inquire add-in loaded in the workbook ribbon. Open the Inquire tab, and then select Clean Excess Cell Formatting.
In addition to cleaning up excess or duplicate formatting, consider tidying up your workbook by using the following resources:
Third-party information disclaimer
The third-party products that this article discusses are manufactured by companies that are independent of Microsoft. Microsoft makes no warranty, implied or otherwise, about the performance or reliability of these products.
Microsoft Excel is undoubtedly a very powerful and useful program. It is so powerful that it is possibly highly vulnerable to have issues.
The most common issue is Excel file slow to respond, this is very irritating and faced by the users randomly. Well, there are many reasons responsible for Excel running slow or crashes, hangs, freezing Excel.
Some of the common reasons are old graphics hardware, storing plenty of data in single Excel files, making use of the volatile formulas, updating Excel files from earlier to a later version, or not updating the office program with the latest updates, etc.
These are some of the common issues that make Excel slow. So, if you are struggling with Excel running slow or Excel file slow to respond then read the article completely. Today in this article I am going to describe how to fix Excel files slow to respond and tips to avoid Slow Excel Spreadsheets in the future.
So let’s get started…
How To Fix Excel Slow To Respond Issue?
Well, there is no exact reason responsible for behind slow responding Excel file. So here I described the possible working solutions that help you troubleshoot Excel file slow response issues.
1: Update the Graphics Card Drivers
2: Start Excel in Safe Mode
3: Disable Hardware Acceleration
4: Scan for Virus Infection
5: Change Default Printer (Possibly a Solution)
6: Use Quick Formulas Techniques
7: Avoid Using Complete Row/Column As Reference (A:A)
8: Keep Spreadsheet Small
9. Processing Excel from Network or Web App
10. Enable Superfetch
1: Update The Graphics Card Drivers
This is one of the common reasons that cause the Excel workbook to run slow. So, if you are having any graphics card on your system then try to update the drivers.
To update the drivers visit the manufacturer website or Microsoft official website and check for the latest updates and try installing them.
Moreover, you can also make use of the Free Driver Booster which helps you to update the drivers automatically.
2: Start Excel In Safe Mode
If the above solution won’t help you to fix Excel file slow respond then try to start Excel in Safe Mode.
Follow the steps to do so:
- First close Excel completely > press Windows + R > and in the Run dialog box type Excel.exe/safe > hit Enter.
- If Excel opens with the above steps, then it might happen any plug-in or other software is installed that interferes with the software. Then try to disable the plug-in and uninstall the software related to the programs. But if you still experience problems in starting Excel in safe mode, then try the next solutions
- Choose File > Options > Add-ins.
- Select Excel Add-ins in the Manage drop-down menu > choose Go…
- If in case any items are checked, then uncheck them and choose OK. This disables the Add-ins causing the issue.
- Next close Excel and launch normally.
Hope the Excel file launches normally but if not then tries disabling the “COM Add-ins”, “Actions”, and “XML Expansion Packs”. Doing this will help you to open Excel files normally.
3: Disable Hardware Acceleration
If your Excel file is still running slow then try disabling the hardware acceleration for Excel to fix the issue.
Follow the steps given:
- Open Excel program and click on the options.
- Then go to the Advanced tab.
- And under the Display section > uncheck the box for ‘Disable hardware graphics acceleration’.
- Click OK.
And in this way you are able to fix Excel slow to respond issue.
4: Scan For Virus Infection
In many cases the virus or malware infection causes the Excel file to run slow so, it is worth scanning your system with a good antivirus tool.
Perform a complete scan with the good antivirus program and check whether the issue is resolved.
Don’t Miss – Top 5 Methods to Repair & Recover Virus Corrupted Excel Files
5: Change Default Printer (Possibly a Solution)
Well, this is a working solution that worked for many users to resolve slow-running Excel files. The printer driver corruption causes the issue for many so, try to change the default printer and update the printer driver with the latest update.
You can also try to uninstall and reinstall the printer driver or contact the printer manufacturer for doing so.
After that check, if the issue is fixed or not.
So, these are some of the best fixes that help you to troubleshoot Excel slow to respond issue. Now here check out the tips to avoid common mistakes that make Excel slow to crawl and speed up Excel file easily.
6: Use Quick Formulas Techniques
Excel offers its users with lots of formula or formula combos to do the same things. To identify and use the fastest ones.
Have a look over some of the examples:
Instead of using ISERROR and IF combo just make use of the IFERROR.
- Use the IFERROR in place of IF & ISERROR combo.
- Instead of using IF(A1>0,A1,0) just use the MAX(A1,0). As it is found that MAX option approx 40% much faster than the IF option.
- Just like the same instead of using the VLOOKUP function you can use the INDEX/MATCH combo.
- Use the INDEX/MATCH combo, instead of VLOOKUP.
- You can use double negatives for converting up FALSE and TRUE to 0 and 1. Quick improvement can easily be noticed in large data sets.
7: Avoid Using Complete Row/Column As Reference (A:A)
The only reason behind stopping you from using the entire Row/Column As References in the formulas.
We all think that excel columns and rows have few cells with data but Excel doesn’t think in this way.
When the entire row/column is referenced. Excel checks it manually one by one which consumes more time in calculations.
8: Keep Spreadsheet Small
An oversized Excel file is highly responsible for slow Excel Spreadsheets performance because a large Excel spreadsheet consumes a large space.
You need to have enough storage space on the system so that you can easily get rid of any issues efficiently and smoothly.
Following are some ways to reduce Excel file size:
- Save Excel File in the .xlsb file format
- Remove Unused Cells
- Eliminate the Cell Formatting
- Remove Unwanted Formulas
- Compress the Images
9: Processing Excel From Network or Web App
If you are directly trying to edit Excel spreadsheets in the Web App such as SharePoint then this may lead to slow Excel spreadsheet.
Instead of editing this file directly from the network, downloads the file to your PC and then edit it. After complete editing, upload the file back and then check it out immediately.
10: Enable Superfetch From Registry
In some of the cases, excel gets opens normally and you can load the Excel workbook files without any problem. But in some cases, it is found that the Excel file gets stuck when double click the excel icon.
This problem mainly arises when Superfetch features is turned off. This feature is mainly based on the cached data present over the RAM. So, the file can be easily fetched when necessary
Here are the steps that you need to follow:
- Press the ‘Win + R’ key from your keyboard and then in the open Run window type ‘Regedit. This will open the Windows registry
- Go to the: ‘HKEY_LOCAL_MACHINE/SYSTEM/CurrentControlSet/Control/Session Manager/MemoryManagement/PrefetchParameters’.
- Search for the “EnableSuperfetch” at the right windows and then change its value to 1.
- If the option of EnableSuperfetch is not present, then make a right-click over the right windows and then choose the ‘New> DWORD Value’. Assign the value to 1.
Tips to Avoid Common Mistakes That Makes Excel Slow
1. Place Everything in a Single Workbook
This is a trick that prevents Excel from slowing down. Working across workbooks makes Excel slow commonly when a lot of references are involved, so follow the order in which you place data and formulas to avoid slowing down Excel.
- When you are placing everything in a single sheet then remember to start from top left and proceed right and downwards as the dependence of the cells increases.
- Name the Excel sheets alphabetically (earlier the sheet with only data, and later the sheets with formulas)
- And after executing both the options, you can use the references from other workbooks.
2. Sort Data
Another mistake that causes the Excel spreadsheet to slow down – is the lack of sorting. Well if you are utilizing the formulas that have the ability to search across a range or multiple ranges, sort the data.
Start with the column used mostly in lookup followed by the others one less utilized. If a Vlookup locates a match in the first few rows then this will work much faster, rather than having to go through 30000 rows.
So, it is worth sorting the data in Excel and optimizes the Excel performance.
3. Avoid Using Volatile Functions (Functions re-calculated at every step)
If you are using an excess of volatile formula like RAND() or the RANDBETWEEN() functions, then this what slows down your Excel file.
When you change a single cell value in a spreadsheet, then all the cells containing these formulas recalculate themselves.
Alike this Conditional formatting also has the same impact. And this is the reason why your Excel spreadsheet with thousands of these functions suddenly becomes slow.
Although, this doesn’t mean you should stop making use of these formulas but try to use them generously across thousands of cells.
4. Remove Unwanted Formulas
This might sound useless but is responsible for Excel running slow. Leaving the thousands of unused formulas like Sumifs, Countifs, and VLookups around long can have served their purpose.
So, try to convert them to static values and avoid giving your Excel to run slow on you.
5. Optimize Your Workbook
Here check out certain tips that you need to perform regularly to avoid Excel from slowing down.
- Try keeping the entire thing in the same workbook
- Delete entire invalid references in the name manager
- Remove any invalid links and formulas
- Delete anything that is required. Hit Ctrl + END on each spreadsheet to verify the actually used range of your sheets. And if your used range includes empty cells then delete any rows or columns that are not needed.
Automatic Solution: MS Excel Repair Tool
It is recommended to make use of the MS Excel Repair Tool. It is the best tool to repair all sorts of issues, corruption, errors in Excel workbooks. This allows to easily restore all corrupt Excel files including the charts, worksheet properties cell comments, and other important data.
This is a unique tool to repair multiple Excel files at one repair cycle and recovers the entire data in a preferred location. It is easy to use and compatible with both Windows as well as Mac operating systems.
Steps to Utilize MS Excel Repair Tool:
So this is all about Excel slow to respond issue.
Excel tends to slow down or start crashing after some time so, it is very important to be careful while using Excel and try your best to avoid some mistakes that make the Excel slow down to crawl.
Here I have listed the fixes to resolve Excel file slow to respond issue and the tips to prevent Excel file from running slow.
Hope after following the fixes and tips you are able to speed up Excel file.
Apart from that Excel is a very crucial file so it is very important to handle it properly and backup your important data to avoid data loss situations.
In case you have any additional workarounds that proved successful or questions concerning the ones presented, do tell us in the comments or you can visit the Repair MS Excel blog section.
Learn Ways to Fix Excel File Slow To Respond Issue with Bonus Tips
Are you struggling with the Excel file slow to respond issue, then follow the ways given to fix it and avoid getting it slower...
Repair MS Excel Blogs
Margret Arthur is an entrepreneur & content marketing expert. She writes tech blogs and expertise on MS Office, Excel, and other tech subjects. Her distinctive art of presenting tech information in easy to understand language is very impressive. When not writing, she loves unplanned travels.
You will also be interested:
- Ebay sewing basket
- Photo of roses free download
- Jesus design tattoo
- Bernina artista
- Enterprise oregon elevation
- Kate summerville
- Dmv test pa
- Kvd fishing rods
- Japan eyelid tape
Have you ever had to deal with a slow Excel file?
I’m sure you’ve encountered this situation before. You may have come across Excel workbooks that take a few minutes to load, and you can’t work out why.
Or, there are files where if you change any formulas or add something new then the file locks up because it’s “Calculating”.
Sometimes, Excel workbooks can take a seriously long time for anything to happen.
Fortunately there are some easy ways to quickly fix those problems.
In this article we’ll show you how to fix up any slow Excel workbooks so you can get back to your work as quickly as possible.
We’ll show you tactics including how to optimise your formulas, streamline your formatting and avoid any external data sources.
Change formula calculation to manual
The biggest reason for slow Excel files are formulas that take too long to calculate. So the first tip you can use is to ‘press pause’ on any calculations!
Did you know you can pause the automatic calculation of formulas in Microsoft Excel? This stops formulas being recalculated after every edit you make.
When it’s set to Manual, formulas won’t re-calculate unless you edit an individual cell directly. This means your file won’t hang for a long time before you can continue working.
To turn on manual formula calculation, in the Excel Ribbon go to Formulas > Calculation > Calculation Options > Manual.
Get rid of unused cells by reducing the Used Range
In old Excel files, some additional rows & columns might have been used a long time ago but the data has now been deleted.
When this happens, Excel still “stores” those cells in its memory and if there are a lot of unused cells, it can slow down your Excel workbook. This range of cells is called the “Used Range” and it’s what Excel keeps in its memory.
To check on the Used Range, press Ctrl + End and the cursor will jump to the end of the workbook. If this cell is far beyond where your data is, then you’ll need to delete some rows & columns.
Want other tips to reduce the file size of your Microsoft Excel workbook? Try the Ultimate Guide to Reducing File Size in Excel.
Remove any links to other Excel files
Does your file have any links to other Excel workbooks?
It takes more processing and computational power if there are formulas or other links to other files, because Excel has to jump out of the current file and get information from somewhere else.
If you’re bringing in lots of data from another location, then consider moving all of your data and calculations in the one workbook.
Check on your Excel formulas
Calculating formulas is the most time-consuming part of any large Excel workbook, so it’s important for you to seriously look at what kind of formulas are being used.
Here are some common tactics you can apply to your formulas to fix your slow Excel file:
Don’t refer to entire columns or rows when referring to a range of cells
For example, instead of using a formula like =SUMIF(B:B, “Apples”, A:A), limit the cells to the exact data that you need – so change it to =SUMIF(B3:B25, “Apples”, A3:A25).
Use Paste Values to remove formulas
Do you have any cells that are based on formulas but won’t change in the future? If so, you can copy the final value back into the cell and remove the formula – and Excel won’t recalculate these cells in future. Simply select the cells you want to remove the formulas from, Copy the cells then Paste Values (Alt-E-S-V).
Avoid using volatile functions
Volatile functions in Excel recalculate on every single change, so they can take up a lot of processing power. Consider saving these cells as values if you have a lot of these. Here’s the list of volatile Excel functions:
Reduce the use of array formulas in Excel
For the more advanced Excel users, there might be some array formulas that perform complex calculations. These use a lot of computer processing time if you’re working on large data sets. If you can remove these formulas (or Copy / Paste Values) then you will be able to speed up your Excel file.
Try using different formulas
If you’re using some complicated formulas, consider using some alternatives that are more straightforward. One common example is to remove any VLOOKUP functions and replace them with INDEX & MATCH functions instead.
Check out more great Excel Efficiency articles about functions & formulas here.
Remove or simplify your formatting
Excel stores all formatting styles in memory, so you should reduce the amount of formatting you use.
Here are some ways that you can speed up your slow Excel file by fixing up your formatting:
Remove excessive Excel formatting
If you’re adding borders, text coloring or cell shading for an entire row or column, it will slow down your Excel workbook if you do it too often. If you want to clear any formatting, use the Clear > Clear Formats option in the Home menu of the Excel Ribbon, or use the shortcut Alt-H-E-F.
Alternatively, have a look at this Clean Excess Cell Formatting add-in from Microsoft that removes formatting from cells beyond the last nonblank cell.
Learn how to format cells in Excel quickly with this Excel Shortcut Roundup.
Remove unnecessary Conditional Formatting
Conditional formatting in Excel is powerful and helpful – but if your slow Excel file has been used for a long time, some rules might not be needed any more. Too much conditional formatting can cause the file size to increase and slow down the workbook.
Go to Conditional Formatting in the Home menu of the Excel Ribbon. Then, select Manage Rules if you want to inspect the current rules set up, or Clear Rules > Clear Rules from Entire Sheet to get rid of all rules on the current sheet.
In Microsoft Excel, shapes can be useful, but only in certain circumstances. Just remember that Microsoft Excel is a spreadsheet application first and foremost, and shapes aren’t what it was built for. You should delete them if they’re not needed.
Delete Styles from the Excel Ribbon
If you have imported data or sheets from other files or sources, you have most likely brought in some new formatting styles. These styles are added to the default styles and are stored in Excel’s memory.
Too many styles will slow down your Excel workbook significantly, so delete the ones you don’t need.
And if all else fails…
…try, try again!
If you’ve tried a few of these options and your Excel file is still sluggish, then consider starting fresh and importing your data into a new, clean Excel workbook.
When you copy your data into a new file, it’s a good idea to paste the data as values (Paste Special > Values) so you don’t bring along any excessive formatting or highlighting. But keep in mind that you’ll lose any formulas in your data.
There you have it! There are plenty of options here for you to fix your slow Excel files.
The next time you have a cumbersome Excel workbook that is tearing your hair out, remember that there are some really simple ways to fix it so you can get on with your work.
Let us know in the comments which of these tactics have worked well for you, or if there are any other methods you’ve used!