It analyzes your Excel workbook and if possible, compresses it further. Taking the time to learn best practices in efficient model design will pay off down the road for any model you create and use, whether you’re viewing it in Excel 2013, Microsoft 365 SharePoint Online, on an Office Web Apps Server, or in SharePoint 2013.Ĭonsider also running the Workbook Size Optimizer.
MS EXCEL NOT ENOUGH MEMORY HOW TO
In this article, you’ll learn how to build a tightly constructed model that’s easier to work with and uses less memory. For workbook data models that contain millions of rows, you’ll run into the 10 MB limit pretty quickly. Finally, in Microsoft 365, both SharePoint Online and Excel Web App limit the size of an Excel file to 10 MB. Second, large models use up valuable memory, negatively affecting other applications and reports that share the same system resources. First, large models that contain multitudes of tables and columns are overkill for most analyses, and make for a cumbersome Field List. There's effectively little difference between these versions of Excel.Īlthough you can easily build huge data models in Excel, there are several reasons not to. However, the same data modeling and Power Pivot features introduced in Excel 2013 also apply to Excel 2016. Carlton Collins, CPA, ( is a technology consultant, a conference presenter, and a JofA contributing editor.ĭo you have technology questions for this column? Or, after reading an answer, do you have a better solution? Send them to We regret being unable to individually answer all submitted questions.Note: This article describes data models in Excel 2013. 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. Therefore, you should also consider upgrading your edition or version of Excel to the latest version.īe aw are 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. Secondly, this memory reference problem applies only to older editions of Excel, as this problem was resolved with Office 2016/365 version 1708. 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.
The fact that you have encountered this memory error also tells me things about your computer system. 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: 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. 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.
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.