{"id":2653,"date":"2021-06-12T12:19:18","date_gmt":"2021-06-12T12:19:18","guid":{"rendered":"https:\/\/marstraining.in\/?p=2653"},"modified":"2021-06-15T13:40:56","modified_gmt":"2021-06-15T13:40:56","slug":"how-to-speed-up-excel","status":"publish","type":"post","link":"https:\/\/marstraining.in\/?p=2653","title":{"rendered":"How To Speed Up Excel"},"content":{"rendered":"

How To Speed Up Excel
\n<\/u><\/strong><\/h2>\n

\u2018How To Speed Up Excel\u2019<\/strong>\u00a0There is one thing in the life of every Excel user that always frustrates them and that is the slow performance of Excel calculation.<\/p>\n

It is not a big issue when you have less data but it becomes difficult if we add more data or calculations to the workbook.<\/p>\n

There are many methods and tricks on\u00a0how to speed up Excel<\/strong>\u00a0which we can easily use to improve the performance of Excel spreadsheets. In this article I will teach you\u2019ll few tips and tricks to make Excel calculate faster and increase its speed.<\/p>\n

Methods to Speed Up Excel Worksheet Performance and Calculation.<\/u><\/strong><\/p>\n

Following are the few tips to make Excel spreadsheet run faster\u00a0(How to speed up Excel)<\/strong><\/p>\n

1. Minimum use of Dynamic\/Volatile Formulas<\/strong><\/p>\n

\u00a0<\/strong>Volatile functions such as RANDBETWEEN, CELL, INFO, NOW, TODAY, INDIRECT, RAND, OFFSET, etc. always recalculate if there is even a minor change in the Excel workbook.<\/p>\n

For example, if you use the NOW function in a cell, every time you make a change in the worksheet, the formula would be automatically recalculated and the cell value will get updated. This may take too much time and even additional processing speed.<\/p>\n

So the best way out is to avoid such volatile functions as it may affect the speed of the Excel spreadsheet and slow down its performance.<\/p>\n

2. Conditional Formatting for unwanted Cells<\/strong><\/p>\n

While applying conditional formatting normally we select the entire column\/entire row or the entire sheet, which is not right. The user should only select the range to which he wants to apply the formatting. It may result in a slow excel spreadsheet if applied multiple times or applied on \u00a0large data sets.<\/p>\n

Therefore, another important way out on\u00a0how to speed up Excel<\/strong>\u00a0is to use conditional formatting carefully.<\/p>\n

Piece of advice \u2013 Don\u2019t use conditional formatting on a large data set.<\/p>\n

3. Avoid unwanted ranges in Formulas\/Functions<\/strong><\/p>\n

\u00a0<\/strong>While using any Formulas or Functions in excel one should avoid giving unwanted ranges. Here unwanted ranges mean in the terms of extra cells\/row\/columns. This makes internal calculation slow and sometimes it may hang your file.<\/p>\n

So, if possible, try to avoid unwanted ranges in formulas or functions. This can surely solve your query on how to speed up Excel<\/strong>.<\/p>\n

4. Avoid Array Formulas<\/strong><\/p>\n

Another reason which many of us don\u2019t know is that the use of array formulas may also slow down the speed of our Excel calculation. Array Formulas takes more time to throw result because of cell reference dependency.<\/p>\n

So, the best way out is to avoid array formulas if possible.<\/p>\n

5. Use Named Ranges<\/strong><\/p>\n

We should try to use Named Ranges to refer to any data set in Excel. It makes the user’s job easy and internal access time is also less.<\/p>\n

While creating any dashboards, it\u2019s always better to convert your data into Excel ranges. It is always a good idea to convert your data into an Excel Table. This will help to\u00a0speed up your Excel<\/strong>\u00a0spreadsheet.<\/p>\n

An Example of the named range is:<\/p>\n

=COUNTIF(MYDATA,\u201dEXCEL\u201d), here MYDATA is named range and we are finding the occurrence of Excel within MYDATA range.<\/p>\n

6. Keep source data in one place<\/strong><\/p>\n

It is always better to keep all your source data in one place so that while working with formulas and functions you don\u2019t need to go to another worksheet or workbook to get the data or value. This will consistently help you to\u00a0speed up excel<\/strong>\u00a0calculation and boost the\u00a0speed of your excel\u00a0<\/strong>sheet.<\/p>\n

7. Convert Formulas to Values<\/strong><\/p>\n

Using more formulas affects Excel workbook performance and large file size too. It is highly recommended to convert formulas into values that are the static value.<\/p>\n

8. Avoid Using the unwanted Reference in Formulas<\/strong><\/p>\n

One should avoid using the entire row\/column or sheets as a reference in formulas. You may think that the row\/column only has a few cells with data but Excel doesn\u2019t think that way.<\/p>\n

When you give a reference as an entire row\/column in any Formula, Excel checks the entire cells of the given row\/column\/sheet anyways and takes more time for calculations.<\/p>\n

9. Better Formulas Techniques<\/strong><\/p>\n

Excel has a strong set of Formulas and it provides different types of Formulas to do the same job. It is always better to use the better one to\u00a0increase the speed of Excel.<\/strong><\/p>\n

For Example,<\/p>\n