Microsoft Office: Memory problems in Excel
Q. Why does the attached Excel workbook crash my computer? The workbook opens OK, but soon thereafter Excel freezes up and crashes my computer, returning an Out of Memory error. All of my other Excel workbooks seem to work fine.
A. I reviewed the Excel workbook you sent me, and the problem appears to be that you have multiple VLOOKUP formulas that reference entire columns rather than data ranges. For example, Sheet1’s cell B3 contains the following VLOOKUP formula: =VLOOKUP(A3,$D:$M,2,FALSE) (as shown in the screenshot below), which references the entirety of columns D through M, while the lookup data range you obviously intend to reference is limited to just 44 rows of data.
As a result, your function references 1,048,576 rows times 12 columns of data, or more than 12 million cells instead of the mere 88 cells needed for your formula to work correctly. I understand that some CPAs write their VLOOKUP formulas to intentionally reference large data ranges so additional data can be added to the data set, or they anchor their references with dollar signs so the formula can be easily copied and edited. While these approaches save time that otherwise would be spent creating or modifying VLOOKUP formulas, it has a significant memory cost, as you have encountered. Although your formula works properly, you would use far less computer memory if you were to simplify your formula to reference only the 44 rows of data in columns D and E, as follows:
=VLOOKUP(A3,$D3:$E46,2,FALSE)
The fact that you have encountered this memory error also tells me things about your computer system. First, your computer likely doesn’t have enough RAM by today’s standards, so perhaps you should consider upgrading to a newer computer, or at least consider adding more RAM to your current computer. Secondly, this memory reference problem applies only to older editions of Excel, as this problem was resolved with Office 2016/365 version 1708 16.0.8431.2079 and later editions. Therefore, you should also consider upgrading your edition or version of Excel to the latest version.
Be aware that this memory error is not limited to just the VLOOKUP function, it can also occur in older editions of Excel when using the following six functions: HLOOKUP, LOOKUP, INDIRECT, OFFSET, INDEX, and MATCH. This type of memory issue can also occur when Named Ranges in Excel refer to entire columns of data, or when entire columns are formatted with specific colors, borders, or fonts, even if those columns contain little or no data.
About the author
J. Carlton Collins, CPA, (carlton@asaresearch.com) is a technology consultant, a conference presenter, and a JofA contributing editor.
Submit a question
Do you have technology questions for this column? Or, after reading an answer, do you have a better solution? Send them to jofatech@aicpa.org. We regret being unable to individually answer all submitted questions.
