One of the most common file problems faced by users is that of Excel running slowly. Lately, a large number of users have complained about this problem with Excel spreadsheets. When the data in the workbook is less, the impact on the performance of the file is not noticeable, but when you add more data or calculations to it you will notice that the Excel workbook starts to run slowly.
Symptoms of Slow Excel Spreadsheet
- Time to process operations is taking a long time
- Calculation speeds have been reduced.
- Take a long time to open some Excel files
Possible reasons for the Excel spreadsheet to slow down are as below
- Too much use of conditional formatting: file corrupts regularly, slow to open, and slows the process of opening and inserting/deleting rows in a sheet
- Having Picture Links in the sheet slows down changing formats or entering numbers
- Hidden rows/columns in a sheet reduce the speed of calculation
- The presence of hidden name ranges slows down the navigation between cells
- When an Excel sheet is linked to an unavailable printer it is slow on the initial opening up.
If you are frustrated with a slow Excel spreadsheet that contains a large data set, then you are in the right post. In this article, we have discussed the five solutions for each of the reasons mentioned above.
Fix 1 – Remove Conditional Formatting
When you open an Excel spreadsheet with conditional formatting, you notice that it will take a long time to open, and also operations like inserting a row will take a considerable long amount of time. Major lagging issues are seen when the worksheets have unoptimized conditional formatting.
To check if your Excel sheet contains conditional formattings follow the steps below.
1. Open your Excel sheet.
Go to the Home menu tab.
2. Here, in the Editing group click on Find & Select.
In the sub-menu that opens, choose Go To Special.
3. In the Go To Special window, select the option Conditional formats.
Click on OK.
It will take a long time to complete the operation and it will show all the cells in the sheet that have conditional formatting.
4. Again, go to the Home tab and click on Conditional Formatting in the Styles group.
Select the option Manage Rules to see if this problem is due to the set of rules that are been used for formatting.
Now, if the Conditional Formatting Rules Manager takes a long time to open up, it is a clear indication that conditional formatting is the culprit.
Once the Conditional Format Manager opens up, you will notice that there are a lot of entries. When there are many cells with many different conditional formats, Excel keeps checking these cells to see if something needs to change.
You need to either remove these unnecessary conditional formats or build them in a more efficient manner. Below we have mentioned the steps to remove the conditional formats from your Excel sheet.
1. Highlight or select your entire worksheet by taking your mouse cursor to the upper left corner of the cells.
2. Go to the Home tab.
In the Styles group, click on Conditional Formatting.
Here, go to the Clear Rules menu option and choose Clear Rules from Entire Sheet.
At this point, if Excel says it is not responding, you need to wait as it is working and it can take a long time to finish.
Fix 2 – Clear the Picture Links or Objects
When you notice that moving around the spreadsheet has a small pause before it moves, or you see shadows of objects on your sheet, or you notice delays while making changes to the cell color or formatting, it means that there are some picture links or objects that have been inserted into the sheet without your knowledge. While pasting cells, you may have selected the Paste linked picture option due to which a picture link has been inserted.
At times you will be seeing the regular gridlines along with some shadows of the gridlines. To check if your worksheet is having issues due to picture links, then follow the steps below.
1. Open the problematic Excel sheet.
Click on the Home tab in the command ribbon at the top.
2. Locate the Editing group in this tab and click on Find & Select button here.
In the menu that opens, select the option Go To Special….
3. In the Go To Special window, choose the option by selecting the radio button next to Objects.
Then, click on OK.
4. You will see all the objects/picture links in the worksheet get highlighted.
Now, you can just press the Delete key on your keyboard as the objects are already selected.
As soon as these picture links are removed, the Excel spreadsheet becomes faster without any lags.
Fix 3 – Check for Hidden Rows/Columns
While using Excel sheets, we may hide the unused rows and columns so that only the rows or columns that have the required information are visible. In the rest of the space, you will see a grey background without any cells. But, hiding the rows and columns have problems of its own. When there are hidden rows/columns, you will notice a pause while entering numbers in the sheet but there won’t be any issues with respect to the formatting of the cells.
In the image below, you can see the grey areas and it is assumed that it doesn’t have any information/data in them. But, when you try to change some number in a cell there, you see that the excel sheet slows down as the rest of the cells have some formula in them based on the changed cell value. You will notice that it takes a lot of time for this process to complete as these formulas are present in all the cells till the end but they have been hidden.
Now, right-click on the last row /column that is visible and then select the option Unhide in order to unhide the rows/columns. Go all the way to the end (last column of the worksheet) to see if there are any calculations present there.
To go to the last column in a row you can use the Ctrl + Shift + Arrow key (or End key) that will take you to the end of the visible section.
You can check if these columns/rows are necessary else select the ones to be removed and hit the Delete key to remove the calculations from those rows/columns. This will help in speeding up the slow spreadsheet.
So, always take care of these grey areas that are hidden and have calculations in them. If these are not done correctly they can slow down the spreadsheet exponentially.
Fix 4 – Look for Hidden Name Ranges
If you notice that navigating between cells (moving from one cell to the next) has become slow or after highlighting a set of cells it is slow to move over to the next cell that you select, then it means that there are some Named Ranges that are hidden in the Excel sheet.
1. To view the Name Ranges, click on the Formulas tab and select Name Manager in the Defined Names section.
This will list out a few named ranges present in your worksheet.
2. Create a Macro to Unhide the Name Ranges.
Press Alt + F11 to open the Visual Basic for Application Editor.
Right-click on your workbook and select Insert –> Module.
In the new Module, copy and paste the code given below.
Sub UnhideHiddenNameRanges() 'Unhide all names in the currently open Excel file For Each tempName In ActiveWorkbook.Names tempName.Visible = True Next End Sub
Close the VBA editor to go back to your Excel worksheet.
3. Select the View tab at the top.
Click on the dropdown named Macros and then choose View Macros.
In the Macro window, you will see the macro that you have created. Select this macro and choose the option This Workbook in the dropdown next to Macros in.
Then, click on the Run button. This will unhide the Named ranges.
4. Now, go to Formulas –> Name Manager.
You will see the Name Manager with a large number of Name Ranges present in the workbook.
Click on the Scope column to sort them based on their scope.
Then, select all the name ranges having Workbook as scope. Select the first one, hold the Shift key pressed and go to the last one and highlight it.
Click on the Delete button at the top.
You will be asked for a confirmation for this delete operation. Click on OK.
Once the Name Ranges are removed, you will notice the speed up in the Excel spreadsheet while highlighting cells and moving around the sheet.
Fix 5 – Check for Link to Disconnected/Unavailable Printer
Users have noticed that a disconnected or unavailable default printer can be the reason for slowing down your Excel spreadsheet. When there is a default printer that is not available at the time you open your Excel sheet, the first few interactions are slowed down, and then it is better. The reason for this slow down is that Excel is checking whether any changes are to be made to the printer settings. Generally, this happens when you are away from the location where you connected to your default printer.
To resolve this issue, you can change the default printer to PDF Printer or something similar on your computer that is available at your location so that Excel can find it easily.
1. Press Windows + R to open the Run dialog.
Type ms-settings:printers and hit Enter to open the Printers & scanners page in the Settings app.
2. Make sure that the toggle associated with Let Windows manage my default printer is turned off in the Printer Preferences.
Now, select a printer that is available on your system like Microsoft Print to PDF or any other PDF printer present.
On the Printer page, click on the Set as Default button.
When you open the Excel spreadsheet, you will find that Excel is faster as it won’t be spending time checking for the printer settings.
That’s it!!
Thanks for reading.
We hope the five reasons for slowing down your excel sheet along with the solutions mentioned in this article have helped speed up your Excel sheet. Please comment and let us know the method that worked in your case.