Excel’s ability to execute complicated computations makes it a fairly efficient piece of software. Complicated computations, on the other hand, may take a long time depending on your system. Your workbook’s size and the way formulae are employed are usually to blame if calculations take a long time. In this article, we will learn how to make Excel calculate faster.

**Table of Contents**hide

## Why Do You Need to Make Excel Calculate Faster?

Despite its versatility, Microsoft Excel is nonetheless vulnerable to a variety of issues. When you click on a spreadsheet, it may take a few seconds to load. Especially if you’re opening and closing spreadsheets frequently, even a 30-second wait might greatly affect your workflow. If Excel is running slow, it can also affect your submission deadlines. For this reason, a fast-functioning Excel may ensure that you achieve your deadlines while also providing you with an uninterrupted workflow.

In this article, we’ll cover eight easy and practical strategies to speed up Excel’s calculation process. Excel’s daily tasks may be made more efficient with the help of these tips.

## 1. Using Table and Named Ranges to Make Excel Calculate Faster

In Excel, making a Table and Named Ranges can save us a lot of time. In the following data set, we have some data on sold items from an electronic store. First, we are going to use a **Table** and then we will use the **Named Ranges** to calculate the monthly sales of the store.

### 1.1 Creating Table in Excel

You can save a few extra seconds by using an Excel table. Creating a table in Excel is very easy. To create a table, you can use the following steps.

__Steps:__

- First, select your data and press
**CTRL+T**. - Make sure to check the box of
**My table has headers**. - Now press
**OK**.

Congratulations! You have successfully made a table in Excel.

Using tables in Excel gives us a lot of options to customize. Some of the advantages of using tables are given below.

- You can add numerous amounts of customizations and formatting in Table. And it is very easy to format the tables. Just check or uncheck the boxes in the
**Table Design**tab to format your tables.

- If you scroll down while using your table, your table headers remain fixed on the top. It is very useful for data visualization for long tables.

- You can add filters very easily to your data.
- In Excel, tables will automatically expand when you add a new row or column. They expand with the respective formatting too. For example, if you’re using
**PivotTable**and add new data to the existing dataset, the PivotTable will not be updated automatically. Even the**Refresh**option will not work properly! But, converting the dataset into an Excel table will update the PivotTable with newly added data dynamically! - Tables can drag the formula automatically.

These are only some of the advantages of using tables in Excel. It is always a good idea to convert your data into tables before using them.

### 1.2 Using Named Ranges in Excel Calculation

**Named Ranges** in Excel can give us a lot of flexibility while dealing with large data sets. We can use **Named Ranges **by using the following steps.

__Steps:__

- To make
**Named Ranges**for all of your data at once, select all of your data and click**Create from Selection**from the**Formulas**tab.

- After that, check the box of
**Top Row**from the**Create Names from Selection**dialogue box.

- Now, all of your data has named ranges. The name of the range of each column is the name of the respective headers.
- Now to calculate the
*Sales*, we can use the following formula in cell**F5**.

`=Qty*Unit_Price`

Here, cell **F5** refers to the cell of **Sales**.

- Now, you will be able to get all of your sales data at once.

Thus, you can use the **Named Ranges **for doing numerous calculations instead of typing the cell range repeatedly.

## 2. Avoiding Volatile Functions in Excel to Make Calculation Faster

Volatile functions are such functions, which change their value if we insert anything on any cell of our worksheet. Some of the volatile functions are:

Let’s see an example. In the following data set, we have some volatile functions. Let’s see how they change their values.

**Steps:**

- First, use the following formula in cell
**C5**to generate a random number.

`=RAND()`

Here, cell **C5** represents the cell for storing **Value 1**.

- Now drag the
**Fill Handle**up to cell**E5**to generate the rest of the random numbers.

- After that, let’s type “random” in any cell you want. Type it and press
**ENTER**.

At this stage, you will be able to see that the random numbers have changed after you entered something in a different cell.

All these five functions change their values in this manner, which creates an unnecessary computational load on Excel.

**How to Keep the Output of Volatile Functions Fixed:**

For this reason, try to avoid these volatile functions to calculate faster in Excel. However, using such volatile functions might be essential for you in some cases. In such a situation, you may do the following task:

- First, copy the values from cell
**C5**to cell**C9**.

- After that, select cell
**F5**. Then click on**Paste**from the**Home**Tab. Afterward, from the**Paste**drop-down select**Values (V)**.

- Now, we have copied and pasted as values. Let’s type random at any cell and press
**ENTER**.

You will be able to see that although the values from columns **C**, **D**, and **E** have changed, the values from the **Counter **column haven’t changed.

*Note: **Here, 44741 is the serial number representing the date and time by default. You can easily change the format by pressing CTRL + 1.*

In this way, we can counter the issues of volatile functions.

**Read More:** How to Improve Excel Performance with Large Files

## 3. Using Conditional Formatting with Discretion to Make Excel Calculate Faster

In order to improve the user experience, you can apply the **Conditional Formatting** feature. That is why you should take advantage of formatting tools. But when it comes to computations, formatting isn’t necessary at all. Performance can be improved by eliminating conditional formatting from your workbook.

Let’s apply this** **feature. In the following dataset, we have data on some students’ marks on some of their exams. However, some of the students didn’t appear in the exam, which is denoted as **Absent**. We are going to use **Conditional Formatting **to highlight them.

After applying the Conditional Formatting feature if the cell contains the text for the **C5:F13** cells, you’ll get the following output.

You can use more types of conditional formatting rules and formulas based on your needs. But for large data sets, using **Conditional Formatting** will use a large amount of the computational power of Excel to make your data look nice. To reduce the load on Excel for unnecessary work, you should try to avoid using **Conditional Formatting**.

## 4. Trying to Avoid Large Ranges During Calculation in Excel Calculation

When we use a **Large Range **in Excel computation, we must process a huge number of cells. Although it is simpler to choose the entire column or row, this is not good practice certainly. By choosing the entire column, a significant portion of Excel’s computational capacity is wasted. For this reason, avoid choosing an entire column or row as a reference during Excel calculations.

Let’s give you an example. In the following data set, we have the *Quantity* of some Flowers. We are going to sum it up by using **the SUMIF function**. To operate the **SUMIF** function, we need 3 pieces of information. The first one is the *range*, where it is going to search for the criteria. The second is the *criteria*. It is what the function will search for within the range. The third one is **sum_range**. These are the cells the **SUMIF **function will sum if the *criteria *match within the *range*.

__Steps:__

- First, we are going to sum, using the whole column as a reference. We can use the following formula in cell
**F5**.

`=SUMIF(B:B,E5,C:C)`

Here, **B:B** refers to the whole column B, **C:C **represents the whole column C, and **E5** denotes the **criteria**.

Here we see that we are using the whole columns B and C to calculate the sum. This is useless because we only have data in some of the cells of these columns.

- Afterward, drag the
**Fill Handle**or just double-click it to get the rest of the sums.

Now, we are going to learn a more efficient method than the previous one. Here, we are going to use only the cells that we need instead of using the whole column.

__Steps:__

- First, in cell
**F5**, we are going to use the following formula.

`=SUMIF(B5:B14,E5,C5:C14)`

Here we can see that we have chosen a specific range of data from columns **B **and **C**. This greatly reduces the computational effort of Excel.

- After that, to get the rest of the sums, drag the
**Fill Handle**to the end of the data.

## 5. Assigning Static Values to the Unused Formula to Make Calculation Faster in Excel

In Excel, we may have a section of our worksheet where we used a function at an early stage. However, this function is no longer in use. However, it utilizes Excel’s calculating capability.

We can transform this unneeded formula into simple values to improve Excel’s performance. Consequently, we can calculate faster in Excel.

To convert formulas into values in Excel, you can follow the steps described in the second method.

## 6. Using Multi-Threaded Calculation to Make Excel Calculate Faster

Most of you are probably already using this feature. Still, it’s a good idea to double-check. Using *Multi-Threaded Calculation* in Excel can significantly reduce calculation time. This means that your processor’s multiple cores and threads will be allocated to Excel calculations. To enable this feature, follow the following steps.

__Steps:__

- First, go to the
**File**tab from the ribbon and select**Options**.

- Now, go to the
**Advanced**section.

- After that, scroll down until you find
**Formulas**. - Now, check the box of the
**Enable multi-threaded calculation**. Also, make sure to select**Use all processors on this computer**.

Press **OK **and your **multi-threaded calculation **feature is enabled now.

**Read More: **How to Speed up Excel Calculating 4 Processors

## 7. Decreasing the Number of Worksheets to Make Excel Calculate Faster

Excel can calculate faster if all of your data is in the same worksheets. Using multiple worksheets in Excel calculations can slow you down. To avoid this, try not to use multiple worksheets.

## 8. Using 64-Bit Version of Excel for Faster Calculations

If you’re using a newer system, such as **Office 365**, you’re probably already using the 64-bit version of Excel. However, if you are using **Excel 2016 **or before, the default setting is 32 bits. If your computer has a 64-bit operating system, you should use the 64-bit version of Excel. Although the 64-bit version of Excel uses more memory, it can perform calculations much faster than the 32-bit version.

**Read More:** How to Make Excel Faster on Windows 10

## Things to Remember

- To access the
**Table Design**tab in Use of Table, make sure to click on any cell of the table. After that, the**Table Design**tab will be visible. - In the
**Excel**file Avoiding Volatile Functions, you will see some weird data in the**CELL**row. Don’t worry. It shows the address of the cell that you are currently in.

**Download Practice Workbook**

## Conclusion

Finally, we have come to the end of the article. I sincerely hope that this article has given you the answers you were looking for in order to make Excel calculate faster. Please feel free to ask any further questions in the comments section. Happy Learning!