What causes Excel to use excessive CPU time and generate an 'Excel not responding' msg w/each entry?

Copper Contributor

I have a LOOKUP table with ~ 1500 x 10 elements.  It contains a list of foods and all the nutritional factors for each.  The next worksheet is used to track food consumption by entering the food name.  This triggers the LOOKUP pulling in the nutrition info.  A serving size factor then adjusts the serving size nutritional factors based on the portion size to show what was consumed.  I track this daily so each day has a list of 10 - 20 items consumed.  These values are accumulated for daily, weekly and quarterly-average totals.  After a quarter the number of elements / LOOKUPS /calculations will top 15,000.  After that Excel starts to slow significantly.  When I go to add another food LOOKUP, I get the cycling circle and when I look at Task Manager it shows Excel as 'not responding'.  it start taking several seconds to accept and calculate the last entry and when I get to a 2nd calendar quarter it can take minutes.  I've broken the workbooks to individual quarters but have the same problem.

 

Am I just expecting too much from Excel's ability to do the LOOKUPs and do all the calculations?

 

Annotation 2019-07-19 134237.jpg

7 Replies

@RichL579

 

Hi,

 

There are some tips that you can follow to speed up the lookup functions calculations and prevent the workbook from crashing.

One of them is to use the 64-bit version of Excel, especially, if you have a huge number of lookups in the workbook.

Could you provide a sample of your workbook and formulas to see how we can help?

Another possibility is that your lookups might be using volatile functions, such as OFFSET and INDIRECT. Avoid them, if possible.

@Haytham Amairah 

Attached are the 2 sprdshts I am currently using.  One holds the nutrition data that the LOOKUP checks when I add a new entry to the nutrition log.  The other is the nutrition log that accumulates the info.  

Sorry for the naming confusion. I'm still working on these and have renamed them from food consumption to nutrition. The ones listed above are the files that I am trying to 'fix'. No luck yet. I don't see any errors but am probably not looking at the right stuff.

@RichL579

 

Hi,

 

The workbooks are works on my machine without any crash or slowness!

I think the reason is that I have the 64-bit version of Excel!

 

On the other hand, if you have the latest version of Office 365, consider using VLOOKUP instead of LOOKUP because VLOOKUP got a huge boost in its performance as I think that the old LOOKUP doesn't take advantage from the latest update!

Please check out this link.

 

Hope that helps

OK, thanks for the feedback.  I'll move to the 64 bit version.

@RichL579 

First, get rid of the numerous conditional formatting rules.

Second, get rid of the unnecessary rows with formulas.

 

Separate data input from calculation and reporting.

Do not put data input on more than one sheet (e.g. one sheet for each month/quarter/year).

Use Excel tables.

Use Pivot to do the reporting

Use Power Query to the the calculation and/or reporting.