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

Method 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 analyze all the referenced data in the assigned cells.

• Use them by pressing Ctrl + Shift + Enter instead of only pressing Enter. Inexperienced Excel users might not understand this, and it will cause an error most of the time.

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

Whether there are many complex formulas that contain volatile formulas or not, Excel will be slower. You can avoid this problem by using 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 Excel speed.
• You can find the Manual calculation mode from the Formulas tab in the ribbon.
• From the Calculation Options on the right side choose Manual.

Method 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. These functions also do so every time Excel recalculates.

• 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.

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

Helper columns help a user reduce the length of the formula they are writing. They also help with time efficiency in big calculations and reports. Helper columns help Excel work more efficiently because it has fewer columns to look through to meet certain conditions.

• Two columns define a person’s Name and Age.

• In cell D4, we will create a helper column with the following formula.

• In column D, you can accumulate the data from columns A and B with the aid of the helper column.

Method 5 – Trying to Keep All Assigned Data in One Sheet

Sometimes, you have to use more than one worksheet in Excel for working purposes. It will take much more time if you 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 in one cell.

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

Using Conditional Formatting in Excel can help you emphasize trends and patterns in your data. Conditional Formatting compares one set of data with others.

• Conditional Formatting takes a lot of time for calculation.
• It is better to avoid conditional formatting for a larger data set. When you use Conditional Formatting in a lot of cells, the worksheet will become very slow.
• Find Conditional Formatting in the Home tab of the ribbon.

Method 7 – Avoiding Unnecessary Pivot Tables

You can use the Pivot Table in Excel to summarize an extensive data set quickly. But if you calculate a large data set using a pivot table only once, you can keep the table’s value by turning it into static values. Your workbook will be faster by deleting the Pivot Table.

Method 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. It would be better to change those formulas into static values. See an example of how you can do this conversion.

• Take a cell or table with any formula in it.

• Select the entire table and press Copy.

• Select any cell and Paste the table as paste as Values from Paste Options.

• See that the formulas will turn into static values.

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

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

• While inserting the SUM function, give reference to two entire columns, B and C.

• This causes Excel to run slower on a large worksheet. We will try to refer to a row or column until the data remains.
• We will only take B5:C9 for our calculation purposes.

Method 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 can use a lot of formats with these features.

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

• Named ranges allow users to name a cell or range of cells, making it easier to refer to them in formulas.
• To create, edit, or delete a name, go to the Formulas tab in the ribbon and then to the Defined Names command.
  

Method 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.

Download Practice Workbook

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

Related Articles

• How to Make Excel Open Faster
• How to Speed up Excel Calculating 4 Processors
• How to Open Large Excel Files Without Crashing
  
• How to Stop Calculating Threads in Excel
• How to Stop Calculating 8 Threads in Excel
• How to Make VBA Code Run Faster

<< Go Back to Excel Files | Learn Excel