Resolve out of memory errors

Any system or software has its limit of processing data. Like in system or a server, RAM is responsible for processing memory. More the RAM, more data can be processed at a time. Likewise in Excel, it has its own limit on number of rows and columns allowed per sheet. 1,048,576 rows and 16,384 columns are those limits. Calculation and processing power of Excel depends on the bit system. 32 bit Excel has processing power of around 2 GB per instance. However, 64 bit Excel can stretch its processing upto 8 terabytes. This practically makes it independent of Excel’s processing limit.

In case there are a large number of records on which you want to perform some operations, there are chances you will get Out of Memory error.

To work on the large data size on Excel (using SheetKraft addin), first ensure that your Excel is 64 bit. Second, make it run on top few rows mode. Then reduce the file size from Sheetkraft Global settings. This will make your run much faster and less prone to Out of Memory error. Top few rows mode in the sheetkraft addin will reduce the number of rows of each data array displayed in excel to 10 entries. However, when you run the book, it processes entire original data in the back end calculation. After a book is run on the Top few rows mode, Reduce file size option will clear all the unused cells memory thus reducing the file size. (Above mentioned changes in Excel, will not solve your server side (Sheetkraftweb) errors. If the error occurs on the server side, there might be some other reason for it)

Given below is an example on how to make the above mentioned changes:

Example: There are 43 records present in a sheet as shown in the following figure -

Fig 1.1

In order to reduce the number of records, click on Global under Settings under SheetKraft tab as shown in the following figure:

Fig 1.2

The Settings window will be displayed, check the Show only the top few rows of each result checkbox from the Active Workbook Settings block:

Fig 1.3

Click on Run Book option under SheetKraft tab:

Fig 1.4

The records are now reduced to top 11 records as shown in the following figure:

Fig 1.5

Reducing file size:

The following figure highlights the file size:

Fig 1.6

To reduce the size of the file, click on Reduce File Size button on the Settings window:

Fig 1.7

Save the sheet. The file size is reduced as highlighted in the following figure:

Fig 1.8

The Out of Memory error can also occur due to inefficient setup of calculation logic. You should look into following implementations to solve it (These cases can also hit on the server side, that is, on “Sheetkraftweb”):

• Avoid using multiple SUMIF functions(could be used with a filldown). This can be changed to SK pivot/lookup functions.
• Avoid using OR conditions in Lookup function. Instead, split it into different Lookups, then stack the results and remove duplicates from it.
• You can refer to the Remarks column in Formulas Table in SheetKraft addin for other possible reasons.