Excel Lagging After Data Input

Do you experience Excel time lag when entering data? Is your Excel very slow when entering data? This article explores the problem of lagging after inputting data in Excel 2016.

Please note, an earlier version of Excel, like Excel 2010 might not suffer from lags after data input. We noticed this problem occurs in later versions of Excel, like 2016. i.e., we ran an Excel file with Office 2010, and there was no lagging issue; however, the same file experienced a lagging problem when we opened it with Excel 2016. Please note, the provided solutions in this article are tested for Excel 2016.

Solutions to Improve Excel Lagging

Lags after data input in Excel spreadsheet reduces the performance significantly. After inputting data in a cell, Excel might freeze for a moment. We listed some of the solutions which might improve your Excel performance.

Solution 1 – Turn Off Automatic Calculations

On the Formulas tab, select Calculation Options and choose Manual. By turning off the automatic calculation, Excel won’t freeze or lag after data input because no calculation is executed. The image below displays how to select Manual as your calculation option.

However, the disadvantage of this solution is that formulas are not automatically calculated. Still, you can execute the calculation of formulas manually by pressing F9 or clicking Calculate Now in the Formulas tab, as shown in the image below.

The Formulas tab has two buttons for the formula calculation, Calculate Now button, and Calculate Sheet. You might wonder which one is the right one to click. The Calculate Now button (or press F9) recalculates all open worksheets. And the Calculate Sheet button, as it’s evident from its name, calculates only the active sheet. If you have formulas on other sheets linked to the active sheet, it’s safer to click on the Calculate Now button.

Solution 2 – Unhide Cells & Remove Repeated Conditional Formattings

Hidden Cells: Surprisingly, hidden rows or columns make the calculation slow in Excel 2016 (and maybe other versions of Excel). Unhiding rows will increase the performance and prevent lagging. Filtering records has also the same performance effect because it hides rows.

If you are required to hide some rows or columns or filter the data, please proceed to Solution 4. i.e., you can break your sheet to multiple sheets, and the hidden rows can be separate from the data entry sheet.

Conditional Formattings Duplicates: Also, a workbook might have conditional formatting duplicates which decrease the performance. Copy-pasting cells produce duplicates of conditional formatting rules. Therefore, your Excel file might get hundreds of copies of the same rule. 

You can write a macro to detect duplicates in conditional formatting rules or prevent them. If you are not familiar with macro/VBA, please check out our Excel Automation services and contact us for a free quote.

Solution 3 – Upgrade CPU Cores

Please make sure Excel is using all CPU cores to calculate formulas. Please upgrade the number of CPU cores on your server or computer. If your computer is using two cores, you can upgrade to at least four  cores of CPU and check if any performance improvement is observed. The clip below displays how to do so.

If you’re running your Excel on a server, your IT team would be able to upgrade the CPU without a hassle.

Solution 4 – Break Sheets with Many Formulas into Multiple

If a sheet contains many formulas and hidden rows or columns, you can break it into multiple sheets. Our Excel experts do not recommend using a sheet for many purposes. You can create a second sheet and move some of the data or hidden rows from the sheet to the second one. i.e., by transferring some of the formulas and hidden rows/columns in a sheet, its performance is considerably improved.

Solution 5 – Avoid Volatile Functions

Every time Excel recalculates, cells that contain volatile functions get recalculated. Our Excel experts recommend avoiding volatile functions if possible, because of their effects on the Excel performance and calculation time. A user-defined function, which includes the “Application.Volatile” line is a volatile function. Some of the Excel formulas are also volatile such as indirect(), offset(), cell(), and rand().

Other Solutions

You can try the following solutions as well, but we have not tested them thoroughly, and we are not sure whether they help the Excel performance. Our Excel consultants found these solutions during our research.

Default Printer 

Change the default printer to Microsoft XPS. Excel 2016 has continuous communication with the default printer. A lengthy communication might reduce the performance of Excel. Changing the default printer to Microsoft XPS probably achieves the fastest communication. Open the control panel and set the default printer as explained in the clip below.

Automatic Actions 

Disable automatic actions such as auto hyperlinking links, as explained in the clip below.

Restoring Down Excel Window

Restore down the Excel window. Surprisingly, the performance of Excel is increased in lower window size probably because less of the screen is displayed; therefore, the screen updating takes less time.

Hardware Acceleration

Disable Hardware Acceleration in Display Settings of Excel.

If you are using Office 2013 and later versions, you can rely on hardware acceleration to run Office UI, and the content more efficient. But using hardware acceleration sometimes may lead to a lag in Excel files after inputting data. In such situations, you can disable hardware acceleration to prevent lagging. Here is how to disable hardware acceleration in Excel: 

• Open your Excel spreadsheet.
• Choose the File tab at the top-left.
• Choose Options.
• Go to the Advanced section.
• Find the Display section and uncheck Disable hardware graphics acceleration.
• Click OK to save changes.

Add-in Problem

A particular add-in that might not work properly with some of Excel versions. Try disabling add-ins and observe any improvement in the performance, as explained in the clip below.

Flash Fill

Flash Fill might also cause a performance issue. You can disable it.

One way to automate repetitive and time-consuming tasks in Excel is to use the Flash Fill feature. Flash Fill can analyze your inputting data and detect patterns to fill data automatically. But it may cause performance lagging so, here is how to disable Flash Fill in Excel,

• Open your Excel spreadsheet.
• Go to File and then choose Options.
• Choose the Advanced section.
• In Editing options, uncheck Automatically Flash Fill.
• Click OK to save changes.