Article 43 – Optimize, Troubleshoot and Improve Performance in Excel (non VBA)

Below points will help you in following to optimize your excel sheet

1. Reduce time taken to calculate / recalculate
2. Reduce time taken to open / close workbook
3. Reduce file size
   

Below points may not be valid in many circumstances so you will have to see the suitability of a recommendation but most of the time, these will be valid. Also, in this article we would not concentrate on “WHY” but “WHAT”.

 At the outset, I would like to thank Charles Williams of Decision Models from whom I have learnt most of the below tips. You may like to visit following links where Charles Williams have been sharing his wisdom-

https://msdn.microsoft.com/en-us/library/ff726673(v=office.14).aspx

https://msdn.microsoft.com/en-us/library/ff700515(v=office.14).aspx

https://msdn.microsoft.com/en-us/library/ff700514(v=office.14).aspx

http://www.decisionmodels.com/

https://fastexcel.wordpress.com/

1. Forward and Backward Referencing – Avoid referring to cells on right and down. Always refer to cells on left and above. Hence, A1=A2+1 should be avoided as A1 refers to a cell on its right. Whereas A4=A3+A2 is a good practice as it refers to cells on left side.

2. Circular References – Avoid circular references with iterations. Rather avoid any circular reference as a good practice.

3. Linking Workbooks – If possible, avoid links between workbooks. A good practice is to have few large workbooks rather than having many small workbooks.
   1. If linking can not be avoided, use those functions in linked workbooks which work on closed workbooks.
   2. If linking can not be avoided and there are functions which can not work on closed workbooks, open all your linked workbooks first and then open the workbook which have links to these open workbooks.

4. Linking Worksheets – Message here is also same. If possible, avoid linking between worksheets.  A good practice is to have few large worksheets rather than having many small worksheets. Better to keep all data in one sheet, if possible.
   

5. Minimize the Used Range – Sometimes, your used area may become larger than required. You must have noticed that all of a sudden, the file size has become very big whereas data is limited. You need to clear off that portion of used range which is not needed. You may take following actions –
   1. Go to last cell in column A containing data. Select next row > CTRL+SHIFT+Down Arrow to select till last row of the sheet > Home tab > Clear > Clear All
   2. Go to last cell in first row containing data. Select next column > CTRL+SHIFT+Right Arrow to select till last column of the sheet > Home tab > Clear > Clear All
   3. Another alternative may be to just copy the used part of your Excel sheet into another worksheet and delete the parent worksheet.

6. Using Links – Minimize the links to other data sources. You should clear off the links which you don’t use. To find all the links, you should use Find Link Add-in by Bill Manville which is free of cost.

http://www.manville.org.uk/software/findlink.htm

7. System Maintenance – Type “%Temp%” without quotes in Windows Start > Search and clear off all files and folders periodically (2 to 3 times in a week if not daily). Also do SCANDISK and DEFRAGMENTATION periodically (at least weekly).

8. File extension – Use .xlsx rather than .xls. Calculation engine in .xlsx is supposed to be faster and file size of .xlsx is less than .xls.

9. Use .xlsb – If formatting etc. is not a consideration, then you can look into using .xlsb rather than .xlsx to reduce file size further and make open and close faster. If you are using very large and complex spreadsheets, then .xlsb format is a way to go. As per Microsoft, .xlsb format is optimized for performance in ways that aren’t possible with .xlsx format.

10. Minimize Range in the function – Rather than giving full column reference like A:A in your functions, you should minimize the range to be used like A1:A100

11. Use Structured References – Sometimes, people give large ranges in their function even though such large range is not needed currently. They do this because, as per their assessment, more data might come in future. For example, though currently data is till 1000 rows, but they use the range A1:A100000 as more data might come in the future. In these kind of cases, you may use TABLE which supports Structured References and expands / contracts as per the data addition / deletion. Structured References also give the advantage of inserting / deleting formulas automatically when more rows / columns are added.

12. Minimize Named Ranges – There is a trend to use more and more named ranges. This slows down your Excel if you lot of them. Also, I find debugging and locating the problem area very difficult when named ranges are used. Use them only if must. Following tool is recommended by Microsoft to review named ranges (this tool uncovers even hidden named ranges which are not visible in Name Manager) and take appropriate action –

http://www.jkp-ads.com/officemarketplacenm-en.asp

13. Minimize Volatile Functions – Use them if must. Following are volatile functions in Excel
    1. RAND
    2. NOW
    3. TODAY
    4. OFFSET
    5. CELL
    6. INFO
    7. INDIRECT

In many cases, INDIRECT can be replaced with INDEX. Like                                                        =OFFSET($A$1,0,0,COUNTA($A:$A),1) can be replaced with $A$1:INDEX($A:$A,COUNTA($A:$A)). Similarly, very popular ROW(INDIRECT("1:"&LEN(A1))) can be replaced with ROW(A1:INDEX(A:A,LEN(A1))).

14. Minimize Dynamic Ranges – Do not use them unless absolutely must.

15. Avoid Array Formulas / SUMPRODUCT – Avoid them unless no way out. Array formulas / SUMPRODUCT which reference both row and column forces the calculation of a rectangular area making is all the more slower.
    1. Comma syntax in SUMPRODUCT is faster than * syntax.
    2. Try using SUMIFS, COUNTIFS, AVERAGEIFS in place of Array formulas / SUMPRODUCT. (Excel 2016 has MAXIFS and MINIFS also)

16. Avoid Data Tables –  Avoid them unless no way out.

17. Database Functions – Database functions like DSUM, DAVERAGE, DCOUNT etc. are generally faster. But if you want to use Database functions, you will need to put conditions in different cells. This may not be possible all the time and rather is not a good design consideration.

18. Rows / Columns – Use many rows, less column design for your spreadsheet.

19. Complex Formulas – It is better to have many rows and columns rather than having complex formulas.
    1. Reduce the number of references in your formulas.
    2. Move the repeated calculations to one or more helper cells, and then reference the helper cells from the original formulas.
    3. Use additional rows and columns to calculate and store intermediate results once so that you can reuse them in other formulas.

20. Data Validations – Minimize them. And of course, Data Validation on full columns / rows should not be used.

21. Conditional Formatting – Minimize them. Also don’t apply Conditional formatting on entire column / row. Minimize the range used in a Conditional formatting.

22. 64 Bit Excel – If possible, use 64 bit Excel as 32 bit Excel can use only 2 GB of RAM. 64 bit Excel doesn’t have this limitation.

23. Higher Configuration Machine – The better the CPU / hardware, the fast the calculations will be.

24. Add-ins – Active Add-ins may slow down opening of your worksheets and can cause some other problems in performance. Less frequently used Add-ins may be removed and may be needed when needed.
    1. Keep Updating your Add-ins Periodically – Never postpone the update of your Add-ins. Newer versions are, most of the time but not always, are better than previous versions.
    2. Determine Culprit Add-in – Sometimes, add-ins can cause problems. To determine, if add-in is a problem start Excel in safe mode and see if problem has gone away. If yes, then start Excel normally and disable add-ins one by one and start Excel again every time you disable an add-in to determine the culprit add-in. (To start Excel in Safe Mode, type “Excel /safe” without quotes in Windows>Run and Enter OR hold CTRL key and click Excel icon. Don’t release CTRL button till you get the prompt asking whether you want to start Excel in Safe mode)

25. Unnecessary objects – Sometimes, your worksheet may contains shapes / objects which are hidden and are not needed. Generally, when you copy data from webpages, many objects may get copied which are not needed and they may not be visible to you. To find them > Home tab > Find and Select > Selection Pane which will display all objects. Unnecessary ones can be removed through Selection Pane.

If you need to all delete all of them, simply do F5 or CTRL+G > Special > Objects to find all of them and Delete. (You can also use shortcut ALT+E+G+S+B)

26. Summarization – Use pivot tables rather than formulas.

27. Manual Calculation Mode – If cell inputs are taking time because of recalculations, you can keep your Excel sheet in Manual calculation mode > Formulas tab > Calculation options > Manual (After you job is done, you should revert back to Automatic by clicking Automatic under Calculation options). To calculate while in Manual mode, you can press SHIFT+F9 to recalculate the worksheet and only F9 for entire workbook.
    1. If you save Excel, the entire workbook gets recalculated. If you are frequently saving it, it will become headache. To disable this > File > Options > Formulas > Check Manual and uncheck Recalculate workbook before saving
    2. Excel community says that if you require Manual Calculation Mode, you should redesign your solution. If possible, do it.

28. Stop unnecessary animations (Windows 7) – Control panel > Ease of access center > Use the computer without a display > Uncheck Turn off all unnecessary animations

29. Repeated Use of Static References – If you have a formula which is copied down to thousands of rows and which uses the same references, it is better to copy that part of the formula in a single cell somewhere and refer to this cell in the formula. For example, If the formula =IF(MONTH(TODAY())=4,A2+B2,C2+D2) is copied down, then MONTH(TODAY()) is calculated again and again in the all the formulas copied down. It is better to put following = MONTH(TODAY()) in a cell say Z1 and then use the following formula everywhere =IF($Z$1=4,A2+B2,C2+D2).

30. Replace Unused Formulas with Values – Sometimes after calculation, you don’t need the formulas. For example, every month you get data and calculate your excel sheet. Next month, previous month’s data is not needed for calculation. Values will suffice. Hence, you can simply copy and paste as values last month’s data.

31. Change to IFERROR – Rather than using IF(ISERROR….syntax, use IFERROR.

32. Use Most Likely Condition in IF / AND / OR Statements First – You should structure your IF / AND / OR statements in such a fashion that most likely occurring condition is first condition and least likely occurring condition is the last condition. Other conditions should come in their order of occurrence only.

33. Using Double Unary Operator – To convert from Text to Numbers (also TRUE/FALSE to 1/0), use double unary operator. Hence, use =–A1 rather than A1*1 or A1/1 or A1+0 or A1-0.

34. Keep Excel Version Up-to-date – Keep applying all patches given by Microsoft to keep your Excel up-to-date. This will keep your Excel free from troubles. (Sometimes, it may create some unwanted problem but it is worth to keep applying all patches)

35. Excel not Able to Access Default Printer – Sometimes, slow opening / calculation is caused due to excel not being able to access the default printer. Close all office applications and change your default printer through Start > Devices and Printers to another printer. It may be some physical one or One Note or XPS or PDF or anything else. If this is the problem, then you will need to update your printer driver for default printer.

36. Keep Printer Drivers Up-to-date – Old printer drivers, sometimes, conflict with Excel. Keep them up-to-date by downloading and installing latest versions of Excel. This is all the more important if your OS version has changed. World is moving over to Windows 10 from Windows 7/8, hence this definitely needs to be done if you have switched over to Windows 10.

37. Remove Excessive Formatting – If you format whole columns / rows with colors and borders, it is excessive. Format only that much area which you need in future. Sometimes, copying data from websites / databases may also cause Excessive formatting problem. Microsoft recommends following to clean up excessive formatting

http://xsformatcleaner.codeplex.com/

38. Remove Unused Styles – Styles are generally used to standardize the formats across the workbooks. When cell is copied from one workbook to another, style also gets copied. This can continue to grow causing problem. You can use remove unused styles using following tool recommended by Microsoft

https://sergeig888.wordpress.com/2011/03/21/net4-0-version-of-the-xlstylestool-is-now-available/

39. xlsb – If you use Personal.xlsb, you should open it and do a full recalculation by Pressing F9. And then Save and hide it. You have to do it once only. If you don’t do it, it is calculated every time you open Excel thus delaying opening the Excel.

40. Free Up Your XLSTART folder from Clutter – You should not keep unnecessary files / folders in your XLSTART. Many source of problems are traced to XLSTART folder.

Look into C:\Users\User_Name\AppData\Roaming\Microsoft\Excel (replace user name with your user name, you can directly reach here by copy and pasting following without quotes in Explorer address bar – "%appdata%\Microsoft\Excel\XLSTART\") and see if is contains few files / folders. Move those files to some other location (i.e create a backup of those files / folders and delete all files / folders from here. Personal.xlsb will get moved out. You can restore your Personal.xlsb later on.). Hence, make Excel folder blank.

Now open the file and see if the problem disappears or not.

Note – to find Excel path, you need to find XLSTART path. Excel folder contains XLSTART folder in itself.

1. Open Excel
2. ALT+F11
3. If Immediate Window is not visible, type CTRL+G. Same can be also be accessed through View > Immediate Window
4. Type "? application.StartupPath" without quotes and press enter
5. You will have your path below.
6. Application causing conflict with Excel – Many third party applications also cause the problem like Tuneup Utilities, Abby Finereader, Kaspersky Anti Virus See if you have any of them installed. Close them one by one and see whether it helps.

42. Uncheck Startup Options – File > Options > General > Uncheck both the options given under Start up Options.

43. Uncheck Startup Options – File > Options > General > Uncheck both the options given under Start up Options.

44. Explore Power Query / Power Pivot – Power Query / Power Pivot may help a lot in many situations particularly when you import data from external sources like ERP etc. I would encourage you to learn them and try putting them into use.

45. Giving High Priority to Excel– You can set a high priority to Excel as compared to other applications currently running on your machine so more resources get allocated to Excel. Right click on your task bar > Start task manager > Processes tab > Locate Excel.exe and right click on this > Set priority > Choose High