How to Improve Excel Performance with Large Files (15 Effective Ways)

Method 1 – Avoid Using Volatile Functions

Volatile functions can be defined as a function in which Excel will recalculate every time there is a change in values.

In large files, if there are volatile functions, it will eventually reduces Excel’s performance.

There are 8 volatile functions.

  1. NOW()
  2. TODAY()
  3. RAND()
  4. RANDBETWEEN()
  5. INDIRECT()
  6. OFFSET()
  7. CELL()
  8. INFO()

If you use these functions several times in your large Excel file, it will slow down your Excel file and eventually decrease your Excel performance.


Method 2 – Switch to Manual Calculation

 

Steps

  • Go to the Formulas tab in the ribbon.
  • From the Calculation group, select the Calculation Options drop-down option.
  • Select Manual from the drop-down option.

Improve Excel Performance with large files

 

  • When you need to update the formulae, return to the Formulas tab in the ribbon.
  • From the Calculation group, select Calculate Now option.

Improve Excel Performance with large files


Method 3 – Avoid Using Entire Row or Column References

Some users have a habit of including an entire row or column in references. This will reduce Excel’s performance in large files. Instead, use cells in references.


Method 4 – Remove Unnecessary Conditional Formatting

Conditional formatting is one of the most used features in Excel but it is similar to volatile functions in that any change in conditional formatting values will cause Excel to recalculate the entire worksheet.


Method 5 – Minimize Using Array Formulas

The array formula takes a lot of data into consideration when calculating its solution.

If you must use an array formula, utilize a helper column to control the array formula properly and reduce the amount of data involved in the calculation.

Read More: How to Make Excel Run Faster with Lots of Data 


Method 6 – Utilize Helper Columns

Helper columns as so called because they help to avoid array formulas in Excel.


Method 7 – Utilizing Excel Tables and Named Ranges

Tables and named ranges are two of the most important features when you want to simplify your data and references.

Setting a name for any range of cells allows the user to type that name to utilize the range in the formula and makes the formulas more comprehensive. These two features are useful when you work in a large Excel file and it can actually improve Excel performance.

Read More: How to Make Excel Calculate Faster


Method 8 – Use Formulas That Work Fast

  • Use the IFERROR function instead of the combination of IF and ISERROR. IFERROR is not available in Excel 2003 or earlier.
  • Use the combination of INDEX and MATCH functions instead of the VLOOKUP function. The VLOOKUP function basically decreases Excel performance in large files.
  • To convert True and False into 1’s and 0’s, we need to use –(double negative).

In small Excel files there will not be a significant performance increase, but in large files, you will see some noticeable improvement in your Excel performance.


Method 9 – Disable Excel Add-ins

Excel add-ins are really helpful for some advanced calculations. But when you work on a large Excel file, they can decrease Excel’s performance.

Steps

  • Go to the File tab in the ribbon.
  • Select the More command.
  • Select Options.

  • The Excel Options dialog box will appear.
  • Select the Add-ins option.

Improve Excel Performance with large files

  • Select the Go option.

  • From the Add-ins available section, uncheck all the Add-ins.
  • Click on OK.

Read More: How to Open Large Excel Files Without Crashing


Method 10 – Avoid Creating Links Between Workbooks

When you apply links between workbooks, Excel can become slow. Try to use simple call references that work in a closed workbook instead. By doing this, you can avoid recalculating all the workbooks.

If you need to include links between workbooks, you need to keep the workbooks open. Otherwise, it will create a similar problem in the future.

Read More: How to Make Excel Open Faster


Method 11 – Minimize Links Between Worksheets

As in the previous solution, minimizing links between worksheets can improve performance. If you create links between worksheets, it will cause Excel to recalculate every time you change a value in any one of the worksheets.


Method 12 – Convert Unwanted Formulas to Values

Sometimes in a large Excel file, there will be formulas that are no longer in use or not that important. Convert the formulas into static values to reduce the number of calculations and improve performance.


Method 13 – Disable Hardware Graphics Acceleration

Another possible reason Excel slows down when using large files is the hardware graphics acceleration.

Steps

  • Go to the File tab in the ribbon.
  • Select the More command.
  • Select Options.

  • The Excel Options dialog box will appear.
  • Select the Advanced option.
  • Scroll down to find the Display section.
  • Uncheck the Disable Hardware Graphics Acceleration.
  • Click on OK.

Improve Excel Performance with large files


Method 14 – Modifying Advanced System Settings

In Windows 10, there is some unnecessary animations that can cause problems in Excel large files and eventually decreases Excel’s performance. To turn these settings off, you need to follow the following steps carefully.

Steps

  • Right-click on my computer icon and select Properties.
  • A System dialog box will pop up.
  • Select the Advanced System Settings option.

  • The System Properties dialog box will appear.
  • Select the Advanced option at the top.
  • In the performance section, select Settings.

  • In the Performance Options dialog box, select the Custom option.
  • Uncheck all the options except the Smooth edge of screen fonts.
  • All of these options are unnecessary and can slow down large Excel files and gradually decrease Excel performance.
  • Click on OK.

Improve Excel Performance with large files

Read More: How to Make Excel Faster on Windows 10


Method 15 – Split Workbook into Several Workbooks

Divide large Excel files into several files to improve performance with large files. However, this method can accidentally delete data and has other disadvantages related to maintaining numerous files.

 


Related Articles


<< Go Back to Excel Files | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Durjoy Paul
Durjoy Paul

Durjoy Kumar, with a BSc in Naval Architecture & Engineering from Bangladesh University of Engineering and Technology, is a dedicated contributor to the ExcelDemy project. His substantial contributions include crafting numerous articles and demonstrating expertise in Excel and VBA. Durjoy adeptly automates Excel challenges using VBA macros, offering valuable solutions for user interface challenges. Apart from creating Excel tutorials, he is interested in Data Analysis with MS Excel, SPSS, C, C++, C#, JavaScript, Python Web Scraping, Data Entry... Read Full Bio

2 Comments
  1. Excel 2024 is total crap ! Can NOT handle even relative small datasets and choking on it a lot!? Why MS can’t ad hardware acceleration support ? Excel is performance datasheet application only in MS heads ! Useles garbage crap ! I have a DDR4 fast performance 32 GB RAM, 2x Raid0 NVMe EVO 960 in size 1 TB on i7-7700K and CUDA 5.0 capable GPU. Now for performance comparison sake i have run the same excel spreadsheet on 24 core workstation and guess what, its the same non performance behaviorand, all i use is some conditional formating and some relative simple excel formulas like percentage and dividing ! Excel dont even scale on multicores properly ! Its the same 1 core cpu thinkung like 30 years ago ! When do we get true parallel procesing from MS ? Even on 1000×1000 matrix number data set i can show u how fast Excel can choke or even crash ! WTF do MS do with so much IQ power at his hands ??? How to feed advertisment and all sorts od useless crap into OS !
    And now what ? It was a bad idea to use Ecel in the 1st place coz now i have to test other software to find a permanent non choking performance solution ! God damn !!!

    • Hello Steve,

      It’s clear you’re facing performance bottlenecks with Excel, and your frustration is understandable given your powerful hardware. Excel, while widely used, doesn’t fully utilize multi-core CPUs or GPUs for many tasks. Here are some steps and alternatives you could try:

      To optimize Excel usage you can disable volatile functions like NOW() and RAND() recalculate constantly, slowing Excel down.
      Overuse Conditional Formatting can heavily impact performance, especially on large datasets.
      Set Excel to manual calculations when working on large datasets.
      Excel doesn’t effectively leverage GPUs or advanced multi-threading. However, you can check File > Options > Advanced > Enable hardware graphics acceleration (though this often doesn’t make significant differences in complex datasets).

      Try to enable multithreading by going to File > Options > Advanced > Formulas > Enable multi-threaded calculation. While Excel doesn’t scale well across many cores, this can still help somewhat with certain tasks.

      Explore Alternatives:
      Power BI: It’s optimized for large datasets and leverages more powerful backend engines for data processing.
      Python (Pandas/NumPy): If you handle large matrix-like data, Python libraries can offer much faster performance and can use multiple cores efficiently.
      R or SQL-based tools: These are better at handling larger datasets, parallel processing, and complex operations.
      Consider Excel Online: While Excel desktop has limitations, Excel Online, backed by Microsoft’s cloud, sometimes offers better performance in dealing with larger datasets since it uses cloud resources.

      Despite its strengths, Excel’s legacy architecture doesn’t yet match the potential of modern hardware. Exploring these steps or alternative tools might improve your workflow and reduce frustration.

      Regards
      ExcelDemy

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo