07-19-2019 02:25 PM
07-19-2019 02:25 PM
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?
07-20-2019 06:06 AM
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?
07-20-2019 08:14 AM
07-20-2019 12:05 PM
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.
07-20-2019 12:08 PM
07-20-2019 01:04 PM - edited 07-20-2019 01:05 PM
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
07-20-2019 01:45 PM
OK, thanks for the feedback. I'll move to the 64 bit version.
07-20-2019 04:00 PM
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.