Excel limits

[Svack]

Hi all,
Anyone know what the realistic limits are excel for numbers of columns and rows?

Got someone at work complaining about slow performance on a pretty powerful PC (4th gen i7, 16GB of ram and 250GB SSD) when trying to make changes to an excel spreadsheet, further investigation shows its quite a few complex formulas, over 500,000 rows and 400 columns, they keep telling me "It should work, the limit is 64000 columns and 1 million rows", I'm no database expert but I am really thinking they should be using something a bit better suited to large databases.

So in summary, whats the practical limit on excel when you should really be considering a proper database solution?

 

[izzop]

Hi,

I think you've all ready hit the nail on the head, that depending on what they're actually trying to do with regards to the formulas they might possibly be better off with a database.

Yes, the limitation of Excel, version dependent, is 1,048,576 rows by 16,384 columns for 2013. However, once the user introduces various formulas performance of the spreadsheet will suffer, even though the row & column limit isn't reduced.

This is quite clear here where Microsoft say "Limited by available memory".

I'm assuming that in the spreadsheet there's more than one worksheet, which the formulas are referencing?

It's really down to the complexity of the formulas that's giving them the performance issue imo. There is a possibility that the formuals they've written, all they return the results they want/expect could be better written.

In the past all to often I've come across Excel being used as a database, which it can, however there comes a time when a database is more suitable, as that's what they're designed for.

Regards & Good Luck!

 

[james.miller]

The problem with big, complex spread sheets is the amount of calculation required to recalculate all those formulas on the fly. You'll usually hit a throughput limit well before you run out of memory, and you'll run out of memory long before you use every cell on a million row sheet. Izzop is bang on the money with the post above

 

[izzop]

Reading the reply from James, made me think that something the user could consider to try and improve the performance is to breakdown the forumlas being run in to logical steps and possibly introduce some VBA to run the forumlas so they're not all run at once.

Just a thought.

 

[fretted]

Some formulas are CPU intensive like countif(). If a cell does not need to be recalculated then just remove the formula and keep the value

 

[OspreyO]

...whats the practical limit on excel when you should really be considering a proper database solution?

Usually about a year or so before someone asks that question.

Something like MS SQL is heavily multi~threaded internally. There is a vast difference in performance, between that and Access/Excel and VBA. MS SQL will scrape every bit of performance out of modern hardware, that Excel/VBA can't.

But its not that simple. You need the skillset and the infrastructure to move to SQL. Moving complex Excel/Access/VBA projects to SQL can be complicated. The company might not have the skills or resources to do it. (or be willing)

Even when you do. Quite often its make more sense to do new work in SQL only. Keep the old\existing stuff in the legacy system.