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

**Table of Contents**hide

## 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
instead of only pressing*Ctrl + Shift + Enter*. So, inexperienced*Enter***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
calculation mode from the*Manual***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
and*Name*.*Age*

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

`=B5&"-"&C5`

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

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

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

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

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

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