Performance issues

Question

I have recently came across the potential of use of defined names in complex formulas. It adds a lot of clarity, but I am afraid I went a bit far with it.

I was hoping it would give increased performance as some spreadsheets were starting to get really slow. Apparently, it may even hinder the performance further.

So, I have a few questions about performance in certain cases.

A. I used it for some conditional formatting. I had a few complex formatting formulas that evaluated all the rows in a structured table. So here are three scenarios, which performs best:

1. having the whole formula on the conditional formatting tab
2. having the formula as a defined name and then on the conditional formatting tab
3. having the formula in a hidden column and link the conditional format to that true/false result per row

B. I used it also to shorten repeated criteria on a formula. what is best:

1. having a defined name with the repeated criteria
2. using LET function
3. repeat the criteria

C. I used to repeat criteria (like ranges or condition) across several formulas. what is best:

1. having the defined name
2. repeat the formulas in all other formulas

D. This one is derivative of my sudden discovery of defined names. As I was working on structured tables, when I called [@someCln] I needed to add TableName[@SomeCln], when I inserted the formula in the defined name. To workaround this I created a series of defined names with Table[@Cln1] etc, and to the column ranges, so that I did not have to type them... now this seems totally stupid doesn't it?

In any case, if someone had the patience to read until now and has the knowledge of the performance impact, I would really appreciate some guidance... and I know it depends on the type of formulas...

Answer 1

Hi nsgma365,

Thanks for  your updates, VBA code in the link may help measure calculating time in workbook, you may check the link to get the code and more information: Measuring calculation time.

Best Regards,

Clark

Answer 2

Thank you, Cui.

Clear. I had read the links. The thing is that I need dynamic ranges/names/formulas, which makes really tempting to use the names. Besides, I have in the past used the hidden columns and cells, and eventually I run into performance problems... perhaps for other reasons.

Is there a simple way to compare performances without having to fill 100's of rows and "have a feeling for it"?

Thanks, N

Answer 3

Hi nsgma365,

Please feel free to post back if there are anything we can help.

Best Regards,

Clark

Answer 4

Hi nsgma365,

I go through the post carefully, Microsoft give some Tips for optimizing performance obstructions **** in Excel. based on this, you may reduce use of Defined name in your workbook that may increase Excel performance, as you can see screenshot from the link:

So as for repeat criteria and formulas used in Conditional format, it is better not to put them in defined name, it is recommended to put these formulas to a spare cell and refer to these cells instead.

And it is recommend to use structured table references, as this will increase performance. So as for scenarios you shared with us, I think it is better to use less Defined names, to shorten formulas, you may put criteria that use often in spare cells and refer to the cell in other formulas.

Best Regards,

Clark

Answer 5

To workaround this I created a series of defined names with Table[@Cln1] etc, and to the column ranges, so that I did not have to type them... now this seems totally stupid doesn't it?

A clear no. If your solution works, it is a correct solution.

The bottleneck in Excel that is often overlooked are all these eye-catchers that your can create using a conditional formatting. Each object in a cell must be checked and drawn, so if you have tons of checks and a colorful screen at the end ... it needs time to build all this.

For testing purposes:
Make a copy of the file

Remove all conditional formatings

Remove all colors, borders, etc. etc.

Now check the calculation speed to see how big the proportion of eye-catchers is.

Then check out this (long) article how to improve the calculation speed.

https://docs.microsoft.com/en-us/previous-versions/office/developer/office-2010/ff700515(v=office.14)

Maybe you have to go new ways to process your data.

Andreas.