Performance issue using a combination of dropdowns & lookups

[Excel_Johannes]

Hello forum community,

I am seeking advice since an excel tool that I built has become super slow. Performance increasing measures that I found on the forum (listed below) haven't helped so far.

I have added a few sheets to our company price list (Excel file with just under 3 MB), on which you can put together a shopping cart via dropdown. The dropdowns are sourced from the tables with the products. All relevant information are pulled via Vlookups and IndexMatch. You only have to enter the required products, volumes and discounts, the rest is calculated automatically (file size incl. tool is 3.5 MB now). The problem now is that Excel performance has completely collapsed and the tool cannot be rolled out in this way.

The topic of Excel performance is new to me, but I have already read up a bit and made the following adjustments:

- Deleted almost all conditional formatting
- Deleted unused sheets
- Simplified formulas where possible
- Deleted superfluous formatting
- Named cells/ranges reduced
- Cache repeatedly deleted
- Removed external workbook links
- Removed purely informative columns
- Run excel in save mode (all add-ins disabled)

All these measures have achieved little improvement (file size reduced to 2.5 MB).

I'm in the process of redoing the tool to check when the performance starts to drop. My impression is that it is a gradual process. In the fresh file there are now 24 dropdowns, 1 Xlookup, 8 Vlookups, 1 Countif, 1 simple IndexMatch formula and a complex IndexMatch formula with 9 nested IndexMatches.

Even with this minimal setup, I notice slight performance losses. Especially when I click on a dropdown, it gets a bit slower (in the final version it was especially slow when clicking on a dropdown cell without opening the dropdown).

The final version has the following scope (spread over 2 sheets that do not communicate with each other, i.e. about half of the information below per sheet. I hesitate to provide excerpts from the actual workbook since it's sensitive data):

- 48 dropdowns for product selection, to which all the following lookups refer
- 48 Xlookups
- 624 Vlookups
- 64 dropdowns for an important option to which some columns refer
- 192 simple IndexMatches
- 48 very complex IndexMatches (some products have tiered pricing, i.e. the more volume, the (gradually) higher the tier, the lower the price. The formula must determine the correct tier with the number of tiers varying between products)
- 960 simple plus-minus-times-divided-cell reference formulas
- 600 If formulas
- 48 CountIf
- Most formulas are inside an if-error wrapper

In addition, there are 3 output sheets with 2 tables each, which are linked to the two sheets in order to extract the output clearly. There are also about 10 checks with if-then relationships and 8 conditional formatting.

Sometimes this final version works fine, input takes a second, but that's ok with me. Choosing a few products from the dropdown can however render the tool unusable (even scrolling is hardly possible).

I would be grateful for any advice on what could be causing the slowdown or what I could do to improve performance.

Many thanks,
Johannes

I use Office 365 on a Lenovo ThinkPad X1 Carbon G11 (Intel Core i7-1355U / 1,2 GHz, Deca-Core, 16GB LPDDR5 - 6000MHZ, Windows 11 64bit version)

 

[Georgiboy]

Do your XLOOKUPS, VLOOKUPS & INDEX/MATCH formulae refer to entire columns i.e. 'E:E' or to specific ranges like 'E2:E1200'?

 

[Excel_Johannes]

Do your XLOOKUPS, VLOOKUPS & INDEX/MATCH formulae refer to entire columns i.e. 'E:E' or to specific ranges like 'E2:E1200'?

Thanks for you answer. They refer to entire columns. I read however, that this is not an issue anymore (see attached screenshots). I might have misunderstood what these screenshots say though. My understanding is that Microsoft fixed the performance issue with referencing entire columns. How do you interpret it?

 

[Excel_Johannes]

Thanks for you answer. They refer to entire columns. I read however, that this is not an issue anymore (see attached screenshots). I might have misunderstood what these screenshots say though. My understanding is that Microsoft fixed the performance issue with referencing entire columns. How do you interpret it?

Do your XLOOKUPS, VLOOKUPS & INDEX/MATCH formulae refer to entire columns i.e. 'E:E' or to specific ranges like 'E2:E1200'?

But I agree, I'm gonna rewrite the formulas to refer to specific ranges instead of entire columns just to be sure. I don't expect it to solve the whole problem, but I'm positive it can contribute. Many thanks.

 

[Georgiboy]

For me, I prefer to house my data in table objects and then create formula based on the table references. This way, the formulae refer to smaller blocks of data and yet, when you add data to the table object, it expands - as do the formula created that refer to the table objects. It could be more the INDEX/MATCH formula that see an impact of reducing the range in the formula, in my experience - a smaller range in a formula is always better.

 

[Excel_Johannes]

For me, I prefer to house my data in table objects and then create formula based on the table references. This way, the formulae refer to smaller blocks of data and yet, when you add data to the table object, it expands - as do the formula created that refer to the table objects. It could be more the INDEX/MATCH formula that see an impact of reducing the range in the formula, in my experience - a smaller range in a formula is always better.

It will take me a few days to rebuild it with the adjusted lookup/IndexMatch formulars, but I'll report back if it increased performance.

From my scope description above, do you feel like my plans are too ambitious for excel in general or would you expect an excel with that amount of formulars to work fast and smoothly?

I read that when they tested vlookup vs xlookup calc speed, they used 1 million lookups. I feel like I'm far away from that many lookups.

 

[Joe4]

The real crux of the problem is that you are probably using the wrong tool for the job. When I hear lots of sheets and formulas, and especially lots of VLOOKUP and INDEX/MATCH functions, that tells me what you are really probably dealing with is a RELATIONAL DATABASE.

While you can "brute force" Excel to work as a Relational Database, it can be inefficient, clunky, and cumbersome and performance can suffer (as you are experiencing), because this is not what Excel was really designed for. It would be far better to use a Relational Database Program like Microsoft Access or SQL. They were built for this sort of thing, and handle it much more gracefully and perform much better. Of course, there is a learning curve to those programs if you are not familiar with them.

If you are stuck using Excel, you may want to look at incorporating Power Query, which allows you to better complete database operations in Excel. There is even a forum here where you can ask questions specific to that: Power Tools

 

[Excel_Johannes]

The real crux of the problem is that you are probably using the wrong tool for the job. When I hear lots of sheets and formulas, and especially lots of VLOOKUP and INDEX/MATCH functions, that tells me what you are really probably dealing with is a RELATIONAL DATABASE.

While you can "brute force" Excel to work as a Relational Database, it can be inefficient, clunky, and cumbersome and performance can suffer (as you are experiencing), because this is not what Excel was really designed for. It would be far better to use a Relational Database Program like Microsoft Access or SQL. They were built for this sort of thing, and handle it much more gracefully and perform much better. Of course, there is a learning curve to those programs if you are not familiar with them.

If you are stuck using Excel, you may want to look at incorporating Power Query, which allows you to better complete database operations in Excel. There is even a forum here where you can ask questions specific to that: Power Tools

Agreed. The long term plan is to build a proper tool in SAP BTP. We want to gather some information and feedback from sales managers with our excel tool first though. I have until Friday to finish this excel tool with at least acceptable usability and performance. I feel like I'm missing something here, since this excel tool is not too complicated as far as I understand it.

 

[Excel_Johannes]

So, as far as I can tell, apart from what was discussed above, the biggest performance improvement came from reducing the amount of formulars that need inputs from other formulars. Excel had to calculate many interdependent cells which slowed down things a lot. Now it's much better. Thanks for all the advice!

 

[Akuini]

So, as far as I can tell, apart from what was discussed above, the biggest performance improvement came from reducing the amount of formulars that need inputs from other formulars. Excel had to calculate many interdependent cells which slowed down things a lot. Now it's much better. Thanks for all the advice!

Just an idea:
if you have certain data ranges created by formulas and the values are no longer going to be altered, you can simply delete the formulas and retain the values.