How to Make Excel Run Faster with Lots of Data (11 Ways)

Excel is an essential tool for everyday life. We use Excel to analyze  lots of data for our calculation purpose. Sometimes, Excel may not run fast while analyzing those lots of data. So, user needs to find solution about how to make Excel run faster. In this article, we will show you how to make Excel run faster with lots of data.

Download Practice Workbook

You can download the free Excel workbook from here and practice on your own.

11 Handy Ways to Make Excel Run Faster with Lots of Data

While analyzing business summaries or calculating income tax or home loans in Excel, it has to go through lots of data. This can sometimes make the performance of Excel slower. In this article, you will see 11 effective approaches about how to make Excel run faster with lots of data. Inserting helper columns, applying manual calculations, avoiding conditional formatting, using excel tables are some of the ways among them.

1. Trying to Avoid Array Formulas to Make Excel Run Faster with Lots of Data

Array formulas can kill your time while doing calculations. It takes time to get results from array formulas because they go through all the referenced data in the assigned cells and analyze it.

• You must always remember to use them by pressing Ctrl + Shift + Enter instead of only pressing Enter. So, inexperienced Excel users might not understand this, and it will cause an error most of the time.

2. Applying Manual Calculation Mode to Make Excel Run Faster with Lots of Data

Whether there are a lot of complex formulas that contain volatile formulas or not,  it will make Excel slower. You can avoid this problem by applying the Manual calculation mode.

• This process helps you take control of recalculations in Excel. As it will not change the result of a formula, when you change the source data. Which results in a slower speed of the Excel.
• You can find Manual calculation mode from the Formulas tab in the ribbon.
• From the Calculation Options on the right side and choose Manual.

Read More: How to Make Excel Open Faster (16 Possible Ways)

3. Skipping Volatile Formulas to Make Excel Run Faster with Lots of Data

Volatile functions in Excel are those kinds of functions that have values that alter every time a cell is calculated. Even if none of the arguments related to the function change, the value may still change. Every time Excel recalculates, these functions also do so.

• Some examples of these functions are: the NOW function, the RANDBETWEEN function, the TODAY function, the OFFSET function and the INDIRECT function.
• These types of functions cause the Excel worksheet to run slower. As these functions recalculate every time, this takes extra processing speed. So, it’s better to avoid these functions.

4. Inserting Helper Columns to Make Excel Run Faster with Lots of Data

Helper columns help a user to reduce the length of the formula that user is writing. It also helps with the time efficiency in reports with big calculations. It helps Excel work more efficiently just because it has fewer columns to look through to meet certain conditions.

• You can see below two columns which define a person’s Name and Age.

• Here, in cell D4, we will create a helper column with following formula.

• Thus, in column D, you will be able to accumulate the data of columns A and B with the aid of the helper column.

Read More: How to Make Excel Calculate Faster (8 Handy Tips)

5. Trying to Keep All Assigned Data in One Sheet

Sometimes in Excel, you have to use more than one worksheet for working purposes. It will take much more time if you have to go through the sheets repeatedly in search of data.

• To avoid this problem, you can simply reference all your essential data or values from another worksheet by simply referencing them in one cell.

6. Applying Conditional Formatting with Caution to Make Excel Run Faster with Lots of Data

The use of Conditional Formatting in Excel can assist in emphasizing trends and patterns in your data. Conditional Formatting compares one set of data with others.

• Conditional Formatting takes a lot of time for calculation.
• For a larger data set, it is better to avoid Conditional Formatting. When you use Conditional Formatting in a lot of cells,it will cause the worksheet to become very slow.
• You can find Conditional Formatting in the Home tab of the ribbon.

7. Avoiding Unnecessary Pivot Tables

You can use the Pivot Table in Excel to summarize a large data set quickly. But if you are calculating a large data set using a pivot table only once, then you can simply keep the value of the table by turning it into static values. Thus, your workbook will be faster by deleting the Pivot Table.

8. Using Static Values for Unnecessary Formulas to Make Excel Run Faster with Lots of Data

When you use a lot of formulas in your worksheet, it makes Excel slower. While working, some formulas may not change the value of your result. So, it would be better to change those formulas into static values. Let us see an example of how you can do this conversion.

• First, take a cell or table with any formula in it.

• Then, select the entire table and press Copy.

• After that, select any cell and Paste the table as paste as Values from Paste Options.

• Then, you will see that the formulas will turn into static values.

Read More: How to Make Excel Faster on Windows 10 (19 Effective Ways)

9. Refraining from Using Entire Row/Column as Reference in Excel to Make Excel Run Faster

Sometimes, in Excel users reference an entire row or column in their formula. But this practice causes Excel files to run slower as Excel will check through the entire row, taking up more calculation time.

• Below you can see that, while inserting the SUM function, we give reference of two entire columns, B and C .

• This causes Excel to run slower on a large worksheet. So, we will try to give reference to a row or column till the data remains.
• Here, we will only take B5:C9 for our calculation purposes.

10. Using Excel Tables and Named Ranges to Make Excel Run Faster with Lots of Data

Excel tables and named ranges make it easier for you to manage data and make calculations faster. The user is able to use a lot of formats with these features.

• In tabular format, the user’s formula becomes more understandable. It helps users make calculations faster.
• To turn your values into tabular format, select a cell within your data.
• Then go to the Home tab of the ribbon and select Format as Table.

• Named ranges allows users to name a cell or range of cells. This process makes it easier to refer to those cells in formulas.
• To create, edit or delete any name go to Formulas tab in the ribbon and from there go to Defined Names command.
  

11. Applying Faster Formula Techniques

If you use the proper formula combinations in your Excel worksheet, then it will take less calculation time. Some formulas or functions can perform faster than others. Some of the examples are:

• Use the MAX function instead of the IF function.
• Use the SUMIFS function instead of the SUM function in an array formula.
• Use the combination of the INDEX and MATCH functions instead of the VLOOKUP function.

Read More: How to Speed up Excel Calculating 4 Processors (16 Effective Ways)

Conclusion

That’s the end of this article. I hope you find this article helpful. After reading this article, you will be able to make Excel run faster using any of the methods. Please share any further queries or recommendations with us in the comments section below.

Related Articles

• How to Improve Excel Performance with Large Files (15 Effective Ways)
• How to Stop Calculating 8 Threads in Excel (14 Effective Ways)
• How to Open Large Excel Files Without Crashing (10 Effective Ways)
• How to Stop Calculating Threads in Excel (4 Handy Methods)
• How to Make VBA Code Run Faster (15 Suitable Ways)