Slow Excel Files? Tips to Speed Up Large Excel Workbooks

Q: Why is my Excel workbook running so slowly?

What is the fastest way to speed up a slow Excel file?Switch to manual calculation mode under Formulas then Calculation Options so Excel only recalculates when you press F9. This single change has the biggest immediate impact on workbooks with many formulas because it stops Excel from recalculating everything every time you make any edit.

Q: What are volatile functions in Excel and why do they slow things down?

What file format makes Excel workbooks faster?Saving your workbook in binary format with the .xlsb extension instead of the standard .xlsx format reduces file size and improves performance especially for larger files. Go to File then Save As and choose Excel Binary Workbook from the format options.

Q: What file format makes Excel workbooks faster?

How does conditional formatting slow down Excel?Applying conditional formatting rules to entire columns or rows forces Excel to evaluate every cell in that range constantly. Limiting conditional formatting to only the specific range of cells that actually need it and reducing the total number of rules significantly reduces the processing load.

Follow these 12 tips to speed up your Excel workbook. If you've ever worked with a large Excel workbook, you’ve probably experienced the frustration of it slowing down or becoming unresponsive. Excel is a powerful tool, but as you add more data, complex formulas, or multiple sheets, it can start to drag. Fortunately, there are several strategies you can use to speed up your Excel files and improve performance. In this article, we’ll go over some practical tips to help you optimize your Excel workbooks and ensure they run efficiently.

1. Disable Unnecessary Add-ins

Excel’s add-ins can be useful, but if you have too many installed or some are outdated, they can slow down your workbook’s performance.

How to disable add-ins:

Go to File > Options > Add-ins.
At the bottom of the window, next to "Manage," select Excel Add-ins and click Go.
Uncheck any add-ins you don't need, then click OK.

You can also manage other add-ins such as COM Add-ins from the same window. Disable the ones you don’t actively use, and your workbook should become faster.

2. Use Excel’s Manual Calculation Mode

When Excel recalculates formulas every time you make a change, it can slow down the process, especially with complex or large formulas. Switching to manual calculation mode can help improve performance.

How to switch to manual calculation:

Go to Formulas > Calculation Options > Manual.

In manual mode, Excel will only recalculate when you explicitly tell it to, such as by pressing F9 or using the Calculate Now option. Just be mindful that you’ll need to manually refresh your calculations when necessary.

3. Avoid Volatile Functions

Volatile functions, like NOW(), TODAY(), RAND(), and INDIRECT(), can slow down your workbook significantly. These functions recalculate every time any change is made, even if it doesn’t affect the result of the function.

Tip: Replace volatile functions with alternatives or use them sparingly. For example, instead of using NOW(), you can manually update the date or time as needed.

4. Optimize Your Formulas

Formulas that involve large ranges, array formulas, or complex operations can drastically impact performance. Here are some tips to optimize formulas:

Limit the range: Instead of applying a formula to an entire column (e.g., A:A), apply it only to the necessary range (e.g., A1:A1000).
Use helper columns: Break down complex formulas into simpler ones spread across multiple columns. This can reduce the processing load.
Replace array formulas with regular formulas: Array formulas can be powerful but are resource-intensive. If possible, convert them into simpler non-array formulas.

5. Reduce the Use of Conditional Formatting

While conditional formatting is a useful feature for highlighting specific cells based on rules, applying too many formatting rules or complex rules can slow down your workbook.

How to optimize conditional formatting:

Use fewer conditional formatting rules.
Limit the range of cells that conditional formatting is applied to.
Avoid using conditional formatting on entire columns or rows; target only the necessary range.

To manage and clean up conditional formatting:

Go to Home > Conditional Formatting > Clear Rules to remove unnecessary formatting.

6. Minimize the Use of Images and Objects

Large images or embedded objects, such as charts, shapes, and text boxes, can slow down your workbook. If you're using multiple images or complex objects, try to minimize their size or consider storing them outside the workbook.

Tip: Compress images before inserting them into Excel or use external links to access large files rather than embedding them directly.

7. Remove Unnecessary Formatting

Excess formatting can increase file size and decrease performance. This includes hidden formatting, extra styles, or formatting applied to blank cells.

How to clean up formatting:

Select a range of cells or an entire sheet.
Go to Home > Clear > Clear Formats to remove unnecessary formatting.
You can also use the Find and Select tool (on the Home tab) to search for specific formatting.

8. Use Power Query for Large Data Sets

If you’re working with large data sets, using Power Query to import, clean, and transform the data can be a much more efficient approach than manipulating it directly in the workbook. Power Query allows you to perform data processing tasks without overloading your workbook with complex formulas.

How to use Power Query:

Go to Data > Get & Transform Data to load data into Power Query.
Perform the necessary transformations in Power Query before loading the data into your workbook.

9. Keep Your Workbook Size in Check

Sometimes, workbooks become bloated with excess data or unused sheets. Removing unused sheets or data that you no longer need can reduce file size and improve performance.

How to check for excessive file size:

Save the workbook as a new file (to eliminate hidden data).
Use File > Info to check the file’s size and see if it's unusually large.

10. Use 64-bit Excel

If you're working with large data sets and have access to the 64-bit version of Excel, consider upgrading. The 64-bit version can handle more memory and is better suited for large files.

How to check which version of Excel you’re using:

Go to File > Account > About Excel. The version number will indicate whether you’re using the 32-bit or 64-bit version.

11. Save Files in Binary Format (.xlsb)

Saving your workbook in binary format (.xlsb) instead of the default .xlsx format can reduce the file size and improve performance, especially for larger workbooks.

How to save in binary format:

Go to File > Save As.
Choose Excel Binary Workbook (*.xlsb) from the file format options.

12. Keep Excel Updated

Make sure you're using the latest version of Excel, as newer versions often come with performance improvements, bug fixes, and optimization features.

How to update Excel:

Go to File > Account > Update Options > Update Now.

Large Excel workbooks can easily become slow and unresponsive, but by following these tips, you can improve performance and ensure smoother operation. From optimizing formulas to reducing unnecessary formatting, there are plenty of steps you can take to make Excel work faster and more efficiently. If you’re dealing with very large data sets, don’t forget to explore Power Query or consider upgrading to the 64-bit version of Excel.

Implementing just a few of these techniques can make a huge difference, allowing you to work more productively without the frustration of a lagging spreadsheet.

Learn Excel with me here.

Frequently Asked Questions

Why is my Excel workbook running so slowly? The most common causes are too many complex formulas recalculating automatically, volatile functions like NOW and TODAY that recalculate on every change, excessive conditional formatting applied to entire columns, large embedded images or objects, and bloated file sizes from unused sheets and hidden formatting.

What is the fastest way to speed up a slow Excel file? Switch to manual calculation mode under Formulas then Calculation Options so Excel only recalculates when you press F9. This single change has the biggest immediate impact on workbooks with many formulas because it stops Excel from recalculating everything every time you make any edit.

What are volatile functions in Excel and why do they slow things down? Volatile functions like NOW, TODAY, RAND, and INDIRECT recalculate every single time any change is made anywhere in the workbook even if the change has nothing to do with those functions. In large workbooks this constant recalculation adds up significantly. Replace them with static values where possible or use them sparingly.

What file format makes Excel workbooks faster? Saving your workbook in binary format with the .xlsb extension instead of the standard .xlsx format reduces file size and improves performance especially for larger files. Go to File then Save As and choose Excel Binary Workbook from the format options.

How does conditional formatting slow down Excel? Applying conditional formatting rules to entire columns or rows forces Excel to evaluate every cell in that range constantly. Limiting conditional formatting to only the specific range of cells that actually need it and reducing the total number of rules significantly reduces the processing load.

When should you use Power Query instead of formulas for large data sets? When you are working with large volumes of data that need to be imported, cleaned, or transformed, Power Query handles those operations outside the main workbook calculation engine. This avoids overloading your workbook with complex formulas and keeps the file performing smoothly regardless of data volume.