Have you lot 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 lot can't work out why.

Or, in that location 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 like shooting fish in a barrel ways to quickly prepare those bug.

In this article we'll show you how to fix upwardly whatever slow Excel workbooks so you can get back to your work equally speedily as possible.

We'll show you lot tactics including how to optimise your formulas, streamline your formatting and avert any external data sources.

Alter formula calculation to manual

The biggest reason for boring Excel files are formulas that accept besides long to calculate. And so the first tip you lot can apply is to 'printing pause' on whatsoever calculations!

Did you know you lot can intermission the automatic calculation of formulas in Microsoft Excel? This stops formulas being recalculated after every edit y'all brand.

When it's prepare to Manual, formulas won't re-summate 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 transmission formula adding, in the Excel Ribbon get to Formulas > Calculation > Calculation Options > Manual.

How to Fix a Slow Excel File | ExcelEfficiency.com

Get rid of unused cells by reducing the Used Range

In old Excel files, some boosted rows & columns might accept been used a long time ago simply the information 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 chosen the "Used Range" and it's what Excel keeps in its memory.

To check on the Used Range, printing Ctrl + Stop and the cursor will spring to the stop of the workbook. If this cell is far beyond where your information is, then you'll need to delete some rows & columns.

Want other tips to reduce the file size of your Microsoft Excel workbook? Effort 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 spring out of the current file and go information from somewhere else.

If you're bringing in lots of data from another location, so consider moving all of your data and calculations in the ane workbook.

Cheque on your Excel formulas

Calculating formulas is the almost time-consuming role of any large Excel workbook, and so information technology'due south of import for you to seriously await 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 lot demand – and so change it to =SUMIF(B3:B25, "Apples", A3:A25).

Employ Paste Values to remove formulas

Do y'all have any cells that are based on formulas only won't change in the future? If so, you can copy the final value dorsum into the cell and remove the formula – and Excel won't recalculate these cells in futurity. But select the cells y'all want to remove the formulas from, Copy the cells and then Paste Values (Alt-Eastward-S-V).

Avoid using volatile functions

Volatile functions in Excel recalculate on every unmarried change, so they tin have upward a lot of processing power. Consider saving these cells as values if you have a lot of these. Here's the listing of volatile Excel functions:

  • Now()
  • TODAY()
  • RANDBETWEEN()
  • OFFSET()
  • INDIRECT()
  • INFO()
  • Cell()
  • SUMIF()

Reduce the utilise of array formulas in Excel

For the more advanced Excel users, in that location might be some array formulas that perform complex calculations. These utilize a lot of computer processing time if y'all're working on large information sets. If you tin can remove these formulas (or Copy / Paste Values) and so you will be able to speed up your Excel file.

Attempt using dissimilar formulas

If you're using some complicated formulas, consider using some alternatives that are more than straightforward. One common example is to remove any VLOOKUP functions and replace them with Index & MATCH functions instead.

Check out more swell Excel Efficiency articles about functions & formulas hither.

Remove or simplify your formatting

Excel stores all formatting styles in retention, so you should reduce the amount of formatting you lot utilise.

Here are some ways that you can speed up your slow Excel file by fixing upwardly 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 downwards your Excel workbook if you do it too often. If y'all want to clear whatever formatting, use the Clear > Clear Formats option in the Home menu of the Excel Ribbon, or apply the shortcut Alt-H-East-F.

Alternatively, have a expect at this Clean Backlog Cell Formatting add together-in from Microsoft that removes formatting from cells beyond the last nonblank cell.

Larn 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 whatever more. Too much conditional formatting tin can cause the file size to increase and slow down the workbook.

Get to Provisional Formatting in the Dwelling menu of the Excel Ribbon. Then, select Manage Rules if you lot want to audit the current rules fix, or Articulate Rules > Articulate Rules from Unabridged Sheet to get rid of all rules on the electric current canvass.

How to Fix a Slow Excel File | ExcelEfficiency.com

Remove Shapes

In Microsoft Excel, shapes can be useful, only simply in certain circumstances. Just recollect that Microsoft Excel is a spreadsheet application get-go and foremost, and shapes aren't what it was built for. Y'all should delete them if they're not needed.

Delete Styles from the Excel Ribbon

If yous have imported data or sheets from other files or sources, y'all have virtually likely brought in some new formatting styles. These styles are added to the default styles and are stored in Excel'southward memory.

Too many styles volition slow down your Excel workbook significantly, then delete the ones you don't need.

How to Fix a Slow Excel File | ExcelEfficiency.com

And if all else fails…

…try, effort once again!

If you've tried a few of these options and your Excel file is still sluggish, and then consider starting fresh and importing your information into a new, clean Excel workbook.

When y'all copy your data into a new file, it'due south a good idea to paste the information as values (Paste Special > Values) so you don't bring along any excessive formatting or highlighting. Only keep in mind that you'll lose any formulas in your data.

Determination

There yous have information technology! There are plenty of options here for you to fix your ho-hum Excel files.

The next fourth dimension you have a cumbersome Excel workbook that is tearing your hair out, retrieve that in that location are some really unproblematic ways to fix information technology so you can go on with your work.

Allow u.s. know in the comments which of these tactics accept worked well for you, or if in that location are whatsoever other methods y'all've used!