Do your Excel spreadsheets move slower than a rush hour traffic jam? It’s time for a serious tune-up, as such behavior can affect your productivity and life. Just like there are shortcuts and strategies to navigate a busy city, there are clever tricks to optimize your Excel spreadsheets for smoother performance. In this post, I will show you my go-to practices to fix common Excel woes and dramatically speed up those loading times.

8 Close other apps

Free up resources for Excel

Let’s go over some of the basics first. Closing other applications is a simple yet surprisingly effective first step. After all, every software running on your computer consumes resources (CPU and RAM). By closing unnecessary ones, you give Excel more of these resources to work with, which leads to faster calculations and smoother operations.

While closing other apps is generally helpful, be mindful of closing essential programs or any apps that might not be saving your work automatically.

7 Don’t open too many workbooks

Focus on one thing at a time

It is always important to be mindful of how many workbooks you have open simultaneously. If you have an entry-level Windows or Mac machine, opening too many workbooks can lead to performance issues. Excel becomes slow to respond, with noticeable delays when switching between workbooks, entering data, or performing calculations.

You should regularly save and close workbooks you're not actively using. This frees up resources and simplifies Excel's workload. You can also consider combining related workbooks into a single workbook with multiple sheets.

6 Disable macros

When you don’t require them

Macros can provide a powerful Excel method to automate tasks. However, they can also significantly impact performance, especially when there are too many of them in a single workbook or if they are poorly written. Even worse, macros can be used to spread malware or compromise data. If you're not actively using macros in a particular workbook, it's best to disable them.

The same logic applies to third-party Excel plugins, which you can turn off when not in use.

5 Slow down with external links

It can cause delays

External links in Excel can be a major source of performance drag. When your workbook relies on external links, it needs to access those external files to update data and formulas. This can cause delays. You have several options to minimize external links.

You can embed data directly into your workbook, break links, consolidate workbooks, and even use Power Query (more on that later). You can navigate to Data > Queries & Connections to break links.

4 Optimize workbook performance

Remove unnecessary hidden information

Your complex Excel workbook may contain cells with hidden information that’s not relevant. They lack any meaningful data, but still occupy space, which can affect your workbook performance in no time. Thankfully, there is a built-in way to tackle it.

Open an Excel workbook and head to the Review > Check Performance menu. Select Optimize all and you are good to go.

The option isn’t available on Excel for Mac. Also, you shouldn’t use this function when one of the sheets has Pixel Art. The software can’t differentiate between a Pixel Art cell and an irrelevant black cell and it may remove the art entirely.

3 Use efficient functions

A performance booster

Choosing the right function for the task is crucial for optimizing Excel performance. It’s important to understand the strengths of different functions so that you can make informed decisions.

Suppose you have a large dataset of sales transactions, and you want to calculate the total sales for a specific product category in a particular month. Now, you can use the SUMPRODUCT function to get the job done, but it can be a drag. For example, the formula below evaluates each row in the entire columns A, B, and C, even if most rows don't meet the criteria.

=SUMPRODUCT((A:A="Product Category")*(MONTH(B:B)=1)*(C:C))

Instead, you can use SUMIFS formula, which is specifically designed for summing values based on multiple criteria.

=SUMIFS(C:C, A:A, "Product Category", MONTH(B:B), 1)

This function looks for rows that meet the specified criteria.

2 Be precise with ranges

Make formulas easier to read

You need to be precise with your ranges in a formula. It increases accuracy, reduces the risk of errors, and makes a significant difference in your overall Excel experience. Let's say you have a formula to calculate the average sales in column B. Instead of using =AVERAGE(B:B), use =AVERAGE(B1:B100) (if you know your sales data only goes up to row 100).

You should also consider converting your data into tables. Tables will automatically adjust formulas to include new rows and columns.

1 Consider using Power Query

For efficient data cleaning and transformation

Power Query is one of the Excel hacks I wish I knew earlier. It’s a built-in data transformation and cleaning tool. When you work with large or complex datasets, Power Query can come in handy for an efficient workflow.

It offers a wide range of advanced features such as filtering, sorting, grouping, pivoting, merging, and appending data, all without writing complex formulas. Power Query boosts performance, enhances data connectivity, and simplifies data cleaning. Check out our dedicated guide to learn the top reasons to use it for data analysis.

Say hello to lightning-fast analysis

Tackling your unruly Excel spreadsheets doesn’t have to be rocket science. By implementing these tricks, you can transform your slow, clunky workbooks into lean, mean, data-crunching machines. If you often deal with large datasets in Excel, check out my dedicated guide to handle them like a pro.