How to Make Excel Calculate Faster (8 Handy Tips)

Get FREE Advanced Excel Exercises with Solutions!

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.


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.

Using Excel Table and Named Ranges 


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.

Using Excel Table and Named Ranges 

Congratulations! You have successfully made a table in Excel.

Using Excel Table and Named Ranges 

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.

Using Excel Table and Named Ranges 

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

Using Excel Table and Named Ranges 

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

Using Excel Table and Named Ranges 

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

Using Excel Table and Named Ranges 

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

Using Excel Table and Named Ranges 

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

Using Excel Table and Named Ranges 

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

Read More: How to Make Excel Run Faster with Lots of Data


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.

make excel calculate faster Avoiding Volatile Functions

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.

Avoiding Volatile Functions

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

make excel calculate faster Avoiding Volatile Functions

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

Avoiding Volatile Functions

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

Avoiding Volatile Functions

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

make excel calculate faster Avoiding Volatile Functions

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.

Avoiding Volatile Functions

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

make excel calculateAvoiding Volatile Functions

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

make excel calculate faster Avoiding Volatile Functions

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.

make excel calculate faster Avoiding Volatile Functions

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.

Using Conditional Formatting with Discretion

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

make excel calculate faster Using Conditional Formatting with Discretion

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.

make excel calculate faster Trying to Avoid Large Ranges During Calculation

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.

Trying to Avoid Large Ranges During Calculation

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.

Trying to Avoid Large Ranges During Calculation

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

make excel calculate faster Trying to Avoid Large Ranges During Calculation

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.

Trying to Avoid Large Ranges During Calculation

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

maTrying to Avoid Large Ranges During Calculation


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.

make excel calculate faster Using Multi-Threaded Calculation

  • Now, go to the Advanced section.

Using Multi-Threaded Calculation

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

makeUsing Multi-Threaded Calculation

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!


Related Articles


<< Go Back to How to Speed Up Excel | Excel Files | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Zahid Hasan
Zahid Hasan

Zahid Hassan, BSc, Industrial and Production Engineering, Bangladesh University of Engineering and Technology, has worked with Exceldmy for 1.5 years. He has written 95+ articles for Exceldemy. He has worked as an Excel & VBA Content Developer. He also worked as a VBA Developer for the Template team. Currently, he is working as a Junior Software Developer for the Excel Add-in project. He is interested in Software Development, Python, VBA, VB.NET, and Data Science, expanding his expertise in... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo