Forum Discussion
What causes Excel to use excessive CPU time and generate an 'Excel not responding' msg w/each entry?
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?
7 Replies
- Another possibility is that your lookups might be using volatile functions, such as OFFSET and INDIRECT. Avoid them, if possible.
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?
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.
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.
