Excel is an essential tool for everyday life. We use Excel to analyze lots of data for our calculation purposes. Sometimes, Excel may not run fast while analyzing a lot of data. So, the user needs to find a 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.
How to Make Excel Run Faster with Lots of Data: 11 Handy Ways
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 to make Excel run faster with lots of data. Inserting helper columns, applying manual calculations, avoiding conditional formatting, and 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 Ctrl + Shift + Enter instead of only pressing Enter. So, inexperienced 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 Manual calculation mode from the Formulas tab in the ribbon.
- From the Calculation Options on the right side and choose Manual.
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.
Read More: How to Improve Excel Performance with Large Files
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 the 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 that define a person’s Name and Age.
- Here, in cell D4, we will create a helper column with the following formula.
=B5&"-"&C5- Thus, in column D, you will be able to accumulate the data from columns A and B with the aid of the helper column.
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 Paste the table as paste as Values from Paste Options.
- Then, you will see that the formulas will turn into static values.
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 to two entire columns, B and 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 B5:C9 for our calculation purposes.
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 allow 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 the Formulas tab in the ribbon, and from there go to the Defined Names command.
Read More: How to Make Excel Calculate Faster
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.
- 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 processors of your computer 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.
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.
