Excel running slow?

**penfold1992** · 09-05-2012, 09:07 AM

im working on a excel spreadsheet which does have some complex cells but nothing that i wouldnt expect excel to handle to be honest...
i have two pages which include formula and i have maybe around 500 formula in total but most of them arent that large, most are COUNTIFS

is there any reason as to why excel should be running so slow? does cells containing formatting make excel slower? im just looking for any way to speed up the program to prevent it freezing every time i edit a cell!

**jason.b75** · 09-05-2012, 09:24 AM

Try looking at your complex cells and the ranges used, arrays that refer to entire columns can bring things to a halt quickly, as can excessive use of volatile functions.

**penfold1992** · 09-05-2012, 09:56 AM

here is an example of one slow worksheet.

i copied it from one window to another so thats why a lot of the formula have "Macro attempt 2" and such in them.

if someone could take a look and tell me which ones would be that main problem, that would be great!

**Spencer101** · 09-05-2012, 10:05 AM

As mentioned by Jason above, you are referring to entire columns in your formulas rather than limiting them to a specific range. This will slow things down drastically as Excel is checking a million+ rows for each cell containing these formulas each time any data is amended.

Also, as you're using Excel 2010, you could replace your SUMPRODUCT formulas with COUNIFS and SUMIFS.
That too will drastically speed things up.

Happy to help you amend it but would need to see the full file for that.

If not happy posting it here you could PM me for an email address.