### 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
instead of only pressing*Ctrl + Shift + Enter*. Inexperienced Excel users might not understand this, and it will cause an error most of the time.*Enter*

### 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
calculation mode from the*Manual***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
and*Name*.*Age*

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

`=B5&"-"&C5`

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

### 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
the table as*Paste*from*paste as Values***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,and*B*.*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
for our calculation purposes.*B5:C9*

### 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**.

**Notes:**You can also follow the tips below to make your worksheet faster.

- Use Microsoft Office 365 or a 64-bit version of Excel because it can calculate a large data set more quickly.
- Try to disable them when you don’t need them. This will help you to calculate fast enough.
- While doing calculations, try to make all computer processors available for your Excel. This will increase the speed of your calculation.

**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**