Finding the formula(s) that are causing Excel to lag

[auballard]

I have a fairly large file with multiple formulas. As I enter data or make modifications, there's is a frequent lagging that takes place. It's more aggravating than anything. I would like to create a macro that will report the timings of the formulas to determine which one is creating this issue. There were some codes published about 10 years ago but they did not work. The closest one was from Here which yielded nothing but 0's.

I have tried the most common suggestions, removing volatile formulas, Column references, etc... but I would rather learn what is causing this.

Does anyone have another way to determine which formula(s) are making my file lag?

Excel for Mac, V 16.54

 

[Joe4]

How many sheets do you have?
How much data do you have on each sheet?
How many formulas are there on each sheet?
Exactly what kind of formulas are you using?

I had a co-worker run into a problem like this some years ago.
She had about 10 sheets of data, each with about 20-30 columns, and thousands of rows of data.
She had a bunch of VLOOKUP formulas, looking up values from other sheets.

What we discovered is that Excel actually tracks formula dependencies. That is how it knows exactly which formulas to update when a value is changed.
However, it can only track something like 1 million dependencies.
Once you exceed that number, anytime you change any data, IT WILL RECALCULATE EVERY FORMULA IN YOUR ENTIRE WORKBOOK!
As you might imagine, that absolutely kills performance, rendering the workbook practically unusable.

This is often a clue that you are not using the right program for the task at hand. In this instance, what she actually had was a relational database model (where you have lots of inter-related data - the number of LOOKUP or INDEX/MATCH formulas is usually a dead giveaway that you have a relational database model).
While you may be able to use Excel as a relational database model on a small data set, this is not what it was designed for (and as a result is usually quite cumbersome and clunky).

If this is the situation you have, what you should really look into doing is using the right tool for the project, that is a relational database program, something like Microsoft Access or SQL.
Or at least try to take advantage of some of the new feature offered in Excel, like Power Pivot, which allows you to use relational database-type logic and code in Excel.

 

[auballard]

How many sheets do you have?
How much data do you have on each sheet?
How many formulas are there on each sheet?
Exactly what kind of formulas are you using?

I had a co-worker run into a problem like this some years ago.
She had about 10 sheets of data, each with about 20-30 columns, and thousands of rows of data.
She had a bunch of VLOOKUP formulas, looking up values from other sheets.

What we discovered is that Excel actually tracks formula dependencies. That is how it knows exactly which formulas to update when a value is changed.
However, it can only track something like 1 million dependencies.
Once you exceed that number, anytime you change any data, IT WILL RECALCULATE EVERY FORMULA IN YOUR ENTIRE WORKBOOK!
As you might imagine, that absolutely kills performance, rendering the workbook practically unusable.

This is often a clue that you are not using the right program for the task at hand. In this instance, what she actually had was a relational database model (where you have lots of inter-related data - the number of LOOKUP or INDEX/MATCH formulas is usually a dead giveaway that you have a relational database model).
While you may be able to use Excel as a relational database model on a small data set, this is not what it was designed for (and as a result is usually quite cumbersome and clunky).

If this is the situation you have, what you should really look into doing is using the right tool for the project, that is a relational database program, something like Microsoft Access or SQL.
Or at least try to take advantage of some of the new feature offered in Excel, like Power Pivot, which allows you to use relational database-type logic and code in Excel.

I have 8 Worksheets, 55k rows are the most I have in 1. Most formulas are countifs, vlookups, and Pivot Tables. No connections to outside files.

I actually do not want to solve the problem per se, but rather learn how to create a macro or some other tool that will determine the bottleneck causing the lag.

Thank you!

 

[Joe4]

I do not know exactly how to determine where the lag is, but I can tell you that COUNTIFS and VLOOKUP formula are rather labor-intensive, especially if you use entire column references in them instead of defined ranges, i.e. using something like "A:A" instead of "A1:A55000".

My thinking it isn't any one thing causing the lag, but the combination of factors you have working (lots of data and labor intensive formulas).

BTW, what you are describing is almost definitely a relational database!