Excel Processing Speed

Hi all,

I have a 7.1MB Excel file which purely uses excel formulae and graphs (no macros) and the processing speed of the file is horrendous and it doesn't seem to finish processing(ever). I would like to know what are the possible issues that may be causing this?

My file mainly uses index, match, offset, small, large functions. The only reason I can think of that may be causing this issue is that my index match function usually refers to entire columns i.e R:R rather than R1:R100. However, I'm guessing Excel would stop after it finds the first correct value rather than going through the entire column.

I've also posted this question at the following forum:

http://www.mrexcel.com/forum/excel-q...ml#post4234300

my index match function usually refers to entire columns i.e R:R rather than R1:R100.

That's probably it. If you are using index, match, offset, small, large functions in a combination, there's a chance that these are array functions (committed with Ctrl-Shift-Enter rather than just Enter and confirmed by the presence of curly brackets). If they are, that's definitely a problem and you should use Dynamic Named Ranges rather than referring to entire columns.

You should also try pressing Ctrl-End on each/every sheet. If it goes way beyond the "real" data, the sheet is bloated. You need to select all the rows below the real data and delete them using the Delete icon on the toolbar. Similarly with columns to the right of your real data.

If you use Conditional Formatting, it's worth checking if the ranges have been split. This can happen if you delete and add rows. It may be worth making a note of the CF conditions and the ranges they SHOULD apply to and deleting ALL Conditional Formatting and re-instating it. The easy test is just to delete it all and see if it's any faster. If it is, it proves the point.

Incidentally, OFFSET is a volatile function so that can cause excessive re-calculation if used extensively. It might be better to restructure any formulae using it ... maybe use INDEX instead.

Anyway, just a few ideas to start you off.


Regards, TMS

TMS is right. It's frankly astonishing how misunderstood is the use of entire column references within Excel formulas.

To clarify: array formulas calculate over all cells passed to them, whether those cells are technically beyond the last-used cells in those ranges or not.

With some functions, e.g. COUNTIF(S)/SUMIF(S), you can get away with referencing entire columns with no detriment to performance, though not with functions which operate over arrays, such as AGGREGATE, SUMPRODUCT, and any construction requiring CSE.

Hence, if, for example, you only have data up to row 1000, and you use an array formula which is referencing entire columns, that means that just one instance of such an array formula is being forced to calculate 1,047,576 more cells than are actually necessary.

If you have just ten such formulas, that's already more than 10 million unnecessary calculations, etc., etc.

Regards

Thanks for the Rep. And to you also XOR LX

It's frankly astonishing how misunderstood is the use of entire column references within Excel formulas.

Usually done for convenience, ease, lack of awareness ... or just pure laziness As XOR LX has said, some functions are optimised and can cope with full column references but, once you get into home grown complex formulae, it can be deadly.

The other common mistake is to "prepare" empty rows with formulae such as

Formula:

Please Login or Register  to view this content.

I've seen workbooks wit tens of thousands of formula like that and, by removing them, it can reduce the size of the workbook by an order of magnitude.

Thanks Kyle: were there any replies to the cross post?

Use the Excel Table feature where possible to reduce the ranges with formulas and conditional formatting.
http://chandoo.org/wp/2009/09/10/data-tables/
http://www.jkp-ads.com/Articles/Excel2007Tables.asp

As Jacc says, convert your data to Structured Tables and then you can modify your formulae to refer to columns in the Table.

Structured Tables will not only extend the ranges automatically, they will propagate formula, formatting, Conditional Formatting and Data Validation in new rows.

All those formulae to produce "blank" cells/entries all take time to process/calculate.

Regards, TMS