Accept you ever had to deal with a irksome Excel file?

I'm sure you've encountered this state of affairs before. You may have come across Excel workbooks that take a few minutes to load, and you can't piece of work out why.

Or, in that location are files where if you lot change whatsoever formulas or add something new then the file locks up because it's "Computing".

Sometimes, Excel workbooks can take a seriously long time for anything to happen.

Fortunately at that place are some like shooting fish in a barrel ways to quickly fix those problems.

In this commodity we'll evidence you how to fix upwardly whatever tedious Excel workbooks so you can get back to your work every bit apace as possible.

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

Modify formula adding to manual

The biggest reason for wearisome Excel files are formulas that have besides long to summate. So the showtime tip y'all tin can apply is to 'printing pause' on any calculations!

Did yous know you lot can interruption the automated calculation of formulas in Microsoft Excel? This stops formulas being recalculated after every edit you make.

When it'south set to Manual, formulas won't re-calculate unless you edit an individual jail cell straight. This means your file won't hang for a long time earlier yous tin continue working.

To turn on manual formula calculation, in the Excel Ribbon become to Formulas > Calculation > Calculation Options > Transmission.

How to Fix a Slow Excel File | ExcelEfficiency.com

Become rid of unused cells by reducing the Used Range

In old Excel files, some additional rows & columns might have been used a long time ago but the data has now been deleted.

When this happens, Excel nonetheless "stores" those cells in its memory and if there are a lot of unused cells, it tin can deadening down your Excel workbook. This range of cells is called the "Used Range" and information technology'due south what Excel keeps in its retention.

To check on the Used Range, press Ctrl + Finish and the cursor volition jump to the finish of the workbook. If this prison 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? Try the Ultimate Guide to Reducing File Size in Excel.

Remove whatever links to other Excel files

Does your file have whatsoever 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 bound out of the current file and get information from somewhere else.

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

Check on your Excel formulas

Computing formulas is the nearly time-consuming part of whatever large Excel workbook, so it'due south important for you lot to seriously await at what kind of formulas are existence used.

Hither are some common tactics you can apply to your formulas to prepare your slow Excel file:

Don't refer to entire columns or rows when referring to a range of cells

For instance, instead of using a formula like =SUMIF(B:B, "Apples", A:A), limit the cells to the exact data that you lot need – then change it to =SUMIF(B3:B25, "Apples", A3:A25).

Use Paste Values to remove formulas

Do yous have any cells that are based on formulas but won't change in the time to come? If so, you can copy the last value back into the cell and remove the formula – and Excel won't recalculate these cells in hereafter. Merely select the cells you desire to remove the formulas from, Copy the cells then Paste Values (Alt-Eastward-South-V).

Avoid using volatile functions

Volatile functions in Excel recalculate on every single change, so they tin can take upward a lot of processing power. Consider saving these cells equally values if you have a lot of these. Here'southward the list of volatile Excel functions:

  • NOW()
  • TODAY()
  • RANDBETWEEN()
  • OFFSET()
  • INDIRECT()
  • INFO()
  • CELL()
  • SUMIF()

Reduce the apply of array formulas in Excel

For the more than advanced Excel users, there might be some assortment formulas that perform circuitous calculations. These employ a lot of computer processing fourth dimension if you're working on large information sets. If you tin remove these formulas (or Copy / Paste Values) then you will be able to speed up your Excel file.

Effort using different formulas

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

Cheque out more dandy Excel Efficiency manufactures most functions & formulas hither.

Remove or simplify your formatting

Excel stores all formatting styles in memory, and so you should reduce the corporeality of formatting you use.

Hither are some means that yous 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 down your Excel workbook if you exercise it besides often. If yous want to clear whatsoever formatting, use the Clear > Clear Formats pick in the Home menu of the Excel Ribbon, or use the shortcut Alt-H-E-F.

Alternatively, have a look at this Clean Backlog Cell Formatting add-in from Microsoft that removes formatting from cells across the concluding nonblank jail 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 – only if your slow Excel file has been used for a long time, some rules might not be needed any more. As well much conditional formatting can crusade the file size to increase and slow down the workbook.

Go to Conditional Formatting in the Habitation carte of the Excel Ribbon. Then, select Manage Rules if you lot want to audit the electric current rules gear up up, or Clear Rules > Articulate Rules from Entire Sheet to get rid of all rules on the current sail.

How to Fix a Slow Excel File | ExcelEfficiency.com

Remove Shapes

In Microsoft Excel, shapes can be useful, but only in certain circumstances. Just remember that Microsoft Excel is a spreadsheet application first and foremost, and shapes aren't what it was built for. You should delete them if they're non needed.

Delete Styles from the Excel Ribbon

If you accept imported data or sheets from other files or sources, you have most likely brought in some new formatting styles. These styles are added to the default styles and are stored in Excel's retention.

As well many styles will slow downward your Excel workbook significantly, so delete the ones you don't need.

How to Fix a Slow Excel File | ExcelEfficiency.com

And if all else fails…

…endeavour, endeavour again!

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

When you re-create your data into a new file, it'southward a good idea to paste the information equally values (Paste Special > Values) so y'all don't bring along whatsoever excessive formatting or highlighting. Merely keep in mind that you'll lose any formulas in your information.

Conclusion

In that location yous accept it! In that location are plenty of options here for you to set up your tedious Excel files.

The adjacent fourth dimension you accept a cumbersome Excel workbook that is tearing your hair out, remember that in that location are some really simple ways to fix it and so you tin can get on with your work.

Let usa know in the comments which of these tactics have worked well for yous, or if there are any other methods yous've used!