7 Ways to Fix a Slow Spreadsheet

Is your spreadsheet running slow or constantly freezing and crashing on you? There are many ways you can make it quicker and more efficient. Below, I’ll cover seven different ways you can fix some of the more common reasons your file may not running well.

1. Turn off calculations for the workbook

The first thing you can do is to turn off calculations. If you don’t need your data to constantly be updating, then the easiest way to ease the load of your spreadsheet is to make sure those formulas don’t keep calculating. To do this, you can go to the Formulas tab and under the Calculation group, select Calculation Options and choose Manual:

You can always turn the formulas back on and use F9 to do a force calculation. But if you don’t want to re-calculate everything, a quick trick you can do is to use Find and Replace and replace the equals sign (“=”) with equals. It seems redundant but doing this will refresh your formulas and recalculate the range you selected. This can be an easier alternative to enabling calculations for everything and then having to wait for every calculation to update.

The danger with turning off calculations is that you could end up looking at data that hasn’t been updated and potentially incorrect values as a result of that. This is why you’ll always want to be careful when turning off calculations for an entire workbook.

2. Turn off calculations for individual worksheets

If you don’t want to turn off everything, you can turn off the calculations for specific worksheets. Although there isn’t a way from the calculation options to specify which sheets to turn off, you can do this through a macro.

To do this, go into visual basic (ALT+F11) and go into the ThisWorkbook object:

We’ll want to turn off calculations when the workbook is first opened so you don’t have to remember to do it later. The initial subprocedure looks as follows:

To turn off a calculation, you just need a single line of code:

Worksheets(“Sheet1”).EnableCalculation = False

Then entire subprocedure look as follows:

Where Sheet1 is the name of the sheet that you want to turn calculations off for. And if you want to turn them back on, you change the value from False to True.

This isn’t the easiest option, especially if you aren’t familiar with macros or don’t want to worry about coding anything. But with VBA you can turn off the calculations and then use a button to turn them back on.

3. Separate the data into multiple tabs

If you have a data set that includes multiple years worth of data, you may want to consider breaking it up. Have one tab for the current year, one for the previous year, and so on.

Using the INDIRECT function, you can refer to different worksheets in your file. By naming a header the same name as a tab (e.g. ‘2020’), you can make your formulas dynamic and pointing to a different worksheet as opposed to one very large tab. Looking up 10,000 rows in one tab versus 50,000 rows in a massive collection of all your data can significantly improve the time it takes for your calculations to run.

4. Separate data into multiple files

A more drastic move than breaking up data into different tabs is moving them onto completely different files. If you’re carrying lots of old data into one big spreadsheet, consider archiving some of it and saving it in another file. Unless you really need access to the old data all the time, it might make a lot of sense to break up your files and to keep your current version as lean as possible. You could even have your data on one file and a separate file for your reporting while using PowerQuery to create a snapshot of your data and simply refreshing it as you need it.

5. Use COUNTA and INDIRECT to limit the scope of your calculations

The INDIRECT function can also help you to ensure your formulas don’t include too many rows or columns. If you select an entire column to run your calculations on rather than just the first couple hundred rows that actually have data in them is another way you can slow down your spreadsheet.

To do this, you can use the COUNTA function on a range to determine how many cells have values in them. The formula is a simple one that you can run on an entire column:

=COUNTA(“A:A”)

Then, using the INDIRECT function, you can write a formula that does something like this:

=SUM(INDIRECT(“A1:A”&B1))

B1 in the example above contains the COUNTA formula and will give you the total number of rows that are used. Rather than doing this:

=SUM(A:A)

and selecting everything in column A to sum up, you can shrink your range down using a combination of the INDIRECT and the COUNTA function. This can be a time-saver when you’re dealing with complex calculations, especially if there are thousands of rows of data.

6. Minimize formulas and utilize pivot tables where possible

Another way to cut down on resources is to make use of pivot tables. Pivot Tables are more efficient than formulas and using them can help make your spreadsheet run smoother. The one drawback is that they’re not as flexible as formulas are. Refreshing the data also takes seconds and you don’t have to worry about turning calculations off.

7. Make sure your worksheets aren’t too big

Large files can take long to open and they freeze up a lot. A common problem I see is that worksheets are sometimes thousands of rows long even though there’s nothing there. This can happen if you download a large data set into a sheet and clear it out later. And although those rows may no longer be occupied with data, they’re still technically taking up space. This can add several MB onto a file and make it a lot more difficult to run.

How can you spot this issue? An easy way is to cycle through the tabs and click CTRL+END. You’ll be taken to the last cell in your sheet. If that takes you far beyond your last row or column of data, then that tells you your sheet is using up more data than it needs to. What you can do is delete all the cells in between the last cell (using CTRL+END) and the last cell that actually contains information in it. Then, save the file. You should see your file come down in size. Do this for each worksheet that has this issue. Even though the cells appear to be empty, they can be making your file unnecessarily large.

If you liked this post on 7 ways to fix a slow spreadsheet please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.