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