• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 192
  • Last Modified:

Which factors result in the most "sluggishness" in Excel?

Hello,

I'm trying to determine sort of a "sluggishness scale" regarding which factors have the greatest effect on slowing down basic functions in an Excel spreadsheet.

For example, I have a spreadsheet with data entries in rows 1:21000.  In that spreadsheet, doing even the most basic functions is exceedingly slow.  As a result, I'm trying to determine which bells and whistles I can eliminate to get the greatest improvement in processing speed.

Some of the bells and whistles include:

Color formatting of both fonts and fill
Outlining (using Group & Ungroup)
Hyperlinks
Formulas
Indents
Conditional formatting
Inserted symbols

There are others but those are the ones that come to mind.  There's nothing exotic but this particular spreadsheet is so slow that I would call it dysfunctional.  Therefore it definitely needs therapy -- I'm just trying to determine which therapeutic approach might be most effective!

Thanks

PS  Obviously, a slow computer can result in the same problem.  That's not the issue here though as this particular computer has plenty of oomph plus if I Copy/PasteValue the entire contents into a new spreadsheet, it zips along just fine.  :)
0
Steve_Brady
Asked:
Steve_Brady
  • 4
  • 4
  • 2
  • +1
3 Solutions
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Hello Steve,

there are lots of factors. Most prominent slower-downers: anything that is volatile, i.e. will cause the WHOLE spreadsheet to recalculate when ANY cell changes, including formulas like Now(), Today(), Offset(), Indirect(), and others, and conditional formatting.

Charles Williams has written an excellent article here http://msdn.microsoft.com/en-us/library/aa730921(v=office.12).aspx 

It explains the nuts and bolts much better than any post here could.

Enjoy the read.

cheers, teylyn

0
 
Swapnil NirmalCommented:
The most vulnerable are:

vlookup, countif, lookup, etc where range lookup is involved
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
@n_swapnil, not quite sure what you mean with "vulnerable". Vulnerable to what? Slowing the spreadsheet down? If so, I disagree.

Countif() is one of the fastest formulas out there. Lookup() and Vlookup() should be built to work only on the populated cells in a range, then they are also very fast. Even =LOOKUP(C1,A:A,B:B) is very fast.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
Swapnil NirmalCommented:
dear teylyn,

well as far as speed is concerned, if countif is used on a huge data with big range it does take time(i have faced it). the same is for lookup if huge data
0
 
wolfcamelCommented:
I have also seen excel go slow simply when it has had data in a cell right down the bottom/right of the spreadsheet.
Back when computers were a bit slower i often used to encounter this, copy/paste into a new spreadsheet would speed things up a lot.

Also - have you considered a database perhaps?
0
 
Steve_BradyAuthor Commented:
Thanks for the responses.

>>teylyn:
"...will cause the WHOLE spreadsheet to recalculate when ANY cell changes..."


Switching Calculation Options (Formulas tab) to Manual has already made a big difference!

Also, thanks for the link.  Although I have thus far only scanned through it briefly, I can tell that it is a great article and I am looking forward to going through it carefully.

The responses here have given rise to some additional questions which I will post in a separate thread.

Thanks again.
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Hello Steve,

switching to manual calculation can help, but be very aware of it when you use it.

Another good resource for learning about formula efficiency (if you have time on your hands to watch it)  is this series of videos on YouTube.

In this series, several formulas and their application with different parameters is evaluated and speed-tested towards the end. You will see that, Sumifs() for example leads well before Sumproduct and array formulas.

So, if you want to speed up your spreadsheet, first, try to replace array formulas and functions that act like array formulas (Sumproduct) with Sumifs() and its siblings, and if that's not possible, use helper columns instead of doing all calculations in one cell. Next work on using just the populated cells in a range, for example by using dynamic range names.

cheers,
0
 
Steve_BradyAuthor Commented:
>>teylyn:
Another good resource for learning about formula efficiency (if you have time on your hands to watch it)  is this series of videos on YouTube.


Thanks teylyn.  Did you intend to include a link for "this series of videos" with the above comment?  If not, can you elaborate?
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Here it is. Sorry. Must have been distracted by a funny email. =)

http://www.youtube.com/watch?v=O2ulQwMmlmY
0
 
Steve_BradyAuthor Commented:
LOL  Touché!

Thanks!
0
 
Steve_BradyAuthor Commented:
Thanks
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 4
  • 4
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now