Question About Workbook Speed

[MSC Bobs]

Greetings Everyone,

I've been working on a pretty big dashboard and received a lot of help from here, so first of all I want to say thanks.

My question today choosing between two options, the goal of which is to optimize speed and use fewer resources.

My dashboard has a lot of date look ups that are rooted in array formulas. Several times throughout the dashboard I'm referring to the same date, so I'm wondering if I can look it up just once, park it in a stationary cell and refer to that look up, or if I should use the INDEX formula every time I need any date.

Would it even make a difference? Thanks!

 

[Chihiro]

It will depend on your data/calculation structure.

Best way to optimize speed and resource is to use only the data required.

I usually use Master Workbook/Database where raw data is stored (without any calculations). Use Advanced Filter or SQL query to extract only the portion needed for the dashboard to reduce foot print. Or where only summary data is required, use Access to do most of data operation and Excel only to present final report.

Also, what I often find is that company executives don't much care for interactive dashboard and just want static snapshot that captures key metrics (Operations/Team Leads are different matter).

 

[MSC Bobs]

Ok, I think that makes sense. Basically what you're saying is the complexity of a formula and the number of complex formulas in a workbook doesn't really matter.

The only thing that matters is keeping the number of records to a minimum, is that right?

Eventually, it's in my plans to have a database and to have queries doing as much of the work as possible but right now Excel is my database and there has to be intermediate calculations to some extent.

 

[Chihiro]

That's the largest impactor.

Since most reporting type formula looks at range of cells. Minimizing range will cut down on resource need.

Also, how data is set up will matter. If data is in table format (i.e. database format), there is very little need for array formula (CSE) and speed up the process.

Also, having array dependent on other array calculation can slow down things.

Volatile function should only be used when required and only at last stage of calculation. For an example, if you had Now() in a single cell only, but if there are multiple subsequent formula that references it, then every time Now() is recalculated, all dependent formula will recalculate as well.

 

[MSC Bobs]

I have one TODAY function and there are some things downstream from that. I also use INDIRECT and OFFSET, but only for chart ranges, with nothing downstream from those. I try to use INDEX instead of OFFSET when I can, too.

I think my data are in database format. They are all flat file tables, which I use Excel's Table feature. Is this what you mean, or do you mean something else?

My preferred method of counting is to use IF nested in SUM, and set the value if true to 1. I have that basic counting formula all throughout. Is there a faster way? I prefer that method because most people don't know anything about arrays and so it tends to cut down on the tampering.

 

[Chihiro]

I think my data are in database format. They are all flat file tables, which I use Excel's Table feature. Is this what you mean, or do you mean something else?

That's what I meant. I often receive data with date header in row, other headers in column when it comes to me from HR, Payroll etc. Held couple of lunch and learn sessions to rectify that issue.

My preferred method of counting is to use IF nested in SUM, and set the value if true to 1. ...

I'd go with COUNTIF/COUNTIFS. For most reports, I protect the sheet to avoid end users tampering with formulas or publish in PDF format.

 

[MSC Bobs]

Ok, I appreciate all the input. Thanks.

I think my next project is to learn up some VBA to try and work around the volatile functions.

 

[chirayu]

The way I do it is:
1) Avoid Pivots like the plague - they increase filesize. use only when no other option
2) Avoid huge datasets in the dashboard - instead use tools (separate excel files) that calculate your data beforehand & then paste the calculated data into the dashboard, that way your can reference it through vlookups etc but you don't have huge data in your dashboard
3) Reduce formulas - the more formulas you have the longer it takes to calculate the workbook whenever you change a selection e.g. dropdown list etc. So I sometimes have macros that add my formulas in, calculate everything & then value paste the range