One common example is to remove any VLOOKUP functions and replace them with INDEX & MATCH functions instead.Ĭheck out more great Excel Efficiency articles about functions & formulas here. If you’re using some complicated formulas, consider using some alternatives that are more straightforward. If you can remove these formulas (or Copy / Paste Values) then you will be able to speed up your Excel file. These use a lot of computer processing time if you’re working on large data sets. Reduce the use of array formulas in Excelįor the more advanced Excel users, there might be some array formulas that perform complex calculations. Here’s the list of volatile Excel functions: Consider saving these cells as values if you have a lot of these. Volatile functions in Excel recalculate on every single change, so they can take up a lot of processing power. Simply select the cells you want to remove the formulas from, Copy the cells then Paste Values (Alt-E-S-V). Use Paste Values to remove formulasĭo you have any cells that are based on formulas but won’t change in the future? If so, you can copy the final value back into the cell and remove the formula – and Excel won’t recalculate these cells in future. Here are some common tactics you can apply to your formulas to fix your slow Excel file: Don’t refer to entire columns or rows when referring to a range of cellsįor example, instead of using a formula like =SUMIF(B:B, “Apples”, A:A), limit the cells to the exact data that you need – so change it to =SUMIF(B3:B25, “Apples”, A3:A25). Check on your Excel formulasĬalculating formulas is the most time-consuming part of any large Excel workbook, so it’s important for you to seriously look at what kind of formulas are being used. If you’re bringing in lots of data from another location, then consider moving all of your data and calculations in the one workbook. It takes more processing and computational power if there are formulas or other links to other files, because Excel has to jump out of the current file and get information from somewhere else. Remove any links to other Excel filesĭoes your file have any links to other Excel workbooks? Want other tips to reduce the file size of your Microsoft Excel workbook? Try the Ultimate Guide to Reducing File Size in Excel. If this cell is far beyond where your data is, then you’ll need to delete some rows & columns. To check on the Used Range, press Ctrl + End and the cursor will jump to the end of the workbook. This range of cells is called the “Used Range” and it’s what Excel keeps in its memory. When this happens, Excel still “stores” those cells in its memory and if there are a lot of unused cells, it can slow down your Excel workbook. In old Excel files, some additional rows & columns might have been used a long time ago but the data has now been deleted. Get rid of unused cells by reducing the Used Range
0 kommentar(er)
