What to do when Excel is too slow?

[roasthawg]

What do people do when Excel isn't getting it done? Keep getting "resources not available" errors... mysql or something like that seems to lack the computational power of Excel with all the sumifs and similar valuable functions. Any input appreciated... thanks!

[Sawyer]

What to do when Excel is too slow?

I wait. Hehe.

Use i7 computer.

[mathdotcom]

Invest financially and mentally in a command based program. Which one I'd suggest depends on your needs. Any will have those Excel functions and will handle larger datasets given your computer's specs.

[Emily_Haines]

What do people do when Excel isn't getting it done? Keep getting "resources not available" errors... mysql or something like that seems to lack the computational power of Excel with all the sumifs and similar valuable functions. Any input appreciated... thanks!

If you have data utilizing lots of formulas this will drastically slow everything down.

[mathdotcom]

Excel is even exceptionally slow as a basic spreadsheet program. Takes forever to open.

In terms of analyzing data of any substantial size, I feel like my computer is suffering a horrible horrible death.

[thom321]

Depending on how your data is structured and how large your data set is, some simple VBA macros could make a big difference. If you have a lot of formulas that repeat throughout the entire set of data, you could have whatever formulas you are using in the top row, then use VBA to apply the formula in the top row to the entire data set but instead of having the formulas "live" on each row (which could bring Excel to a halt especially with functions that use the entire data set like sumifs, countifs) the formulas will be replaced with values in every row except the top row where you keep the formula the way you want it.

[MonkeyF0cker]

Learn a programming language and/or learn/interface with a statistical language like R, Matlab, etc.

Excel is pretty much worthless beyond fundamental exercises.

[tukkk]

as thom said, vba macros could make a big difference

and if it is possible you should break your dataset into smaller pieces

[kilmerAsHolliday]

I went xl->xl/vba->visualStudio->xl/vba->php/mysql
I recommend skipping the middle bit if at all possible.

[MonkeyF0cker]

What do people do when Excel isn't getting it done? Keep getting "resources not available" errors... mysql or something like that seems to lack the computational power of Excel with all the sumifs and similar valuable functions. Any input appreciated... thanks!

By the way, there are ways to sum in SQL (and quite a bit faster than Excel)...

SELECT SUM(CASE WHEN Column2 = 'Some Value' THEN Column1 ELSE 0 END) as sum FROM table_name

That will select and sum all records in Column1 if Column2 meets a criterion of "Some Value".

[MonkeyF0cker]

I went xl->xl/vba->visualStudio->xl/vba->php/mysql
I recommend skipping the middle bit if at all possible.

Unless multithreading (and utilizing multi-core processors to capacity) isn't important to someone, I wouldn't skip or disregard the VS languages at all.

It's one of the many reasons that Excel lacks any speed.

[kilmerAsHolliday]

Unless multithreading (and utilizing multi-core processors to capacity) isn't important to someone, I wouldn't skip or disregard the VS languages at all.

It's one of the many reasons that Excel lacks any speed.

pretty easy to curl ur own scripts. voila!

[MonkeyF0cker]

pretty easy to curl ur own scripts. voila!

Yeah. cURL has come a long way, but it still has (and always will have) some serious limitations. A language with native multithreading makes many, many tasks much simpler and efficient.

[Pokerjoe]

Open your worksheet.

Press CTRL END.

If you end up at the bottom of the whole sheet, and especially if this is the case on numerous sheets, that's the problem. I've faced it before a few times, I think generally from Copying and Pasting entire columns. When the entire gazillion available cells in a sheet have to be checked every time you do anything, the system bogs down.

Copy the cells you actually need to work into a clean sheet, delete the old sheet, and you should be fine. For example, if the cells you're actively using are, say, ending at JY106, but CTRL END brings you to whatever, row 100,000+, etc, then just completely copy from A1 to JY106 into a new sheet, then delete the old sheet.

If your data is filling up entire sheets, then yes, you have to change to something other than Excel.

[Lookingtostart]

Have you tried Libre Office? You can change settings relating to RAM usage as well as being more efficient IMO.

[roasthawg]

Depending on how your data is structured and how large your data set is, some simple VBA macros could make a big difference. If you have a lot of formulas that repeat throughout the entire set of data, you could have whatever formulas you are using in the top row, then use VBA to apply the formula in the top row to the entire data set but instead of having the formulas "live" on each row (which could bring Excel to a halt especially with functions that use the entire data set like sumifs, countifs) the formulas will be replaced with values in every row except the top row where you keep the formula the way you want it.

So basically automate paste specials?

[roasthawg]

Invest financially and mentally in a command based program. Which one I'd suggest depends on your needs. Any will have those Excel functions and will handle larger datasets given your computer's specs.

Can you give me examples of a program like this?

[roasthawg]

Learn a programming language and/or learn/interface with a statistical language like R, Matlab, etc.

Excel is pretty much worthless beyond fundamental exercises.

Already have a working knowledge of R and PHP... neither have the computational convenience of excel and it's formulas such as sumifs to my knowledge.

[roasthawg]

By the way, there are ways to sum in SQL (and quite a bit faster than Excel)...

SELECT SUM(CASE WHEN Column2 = 'Some Value' THEN Column1 ELSE 0 END) as sum FROM table_name

That will select and sum all records in Column1 if Column2 meets a criterion of "Some Value".

This might be the way to go for me... I'll play with this and see how it works for me. Thanks!

[roasthawg]

I went xl->xl/vba->visualStudio->xl/vba->php/mysql
I recommend skipping the middle bit if at all possible.

Good news for me as I know php and a bit of mysql. Easy for me to put information into mysql via php... much tougher to analyze and manipulate it.

[thom321]

"So basically automate paste specials?"

Functionally yes, but I wouldn't actually use paste special in the code.
The code doing the "copy" and "paste" could look something like:

'Copy range with formula. Paste in destination range
rngCopyRange.Copy rngDestRange
'Recalculate sheet to update formula values assuming Excel calculation was set to manual at the beginning of the procedure
Calculate
'Suggest including DoEvents to avoid Excel getting stuck or hanging when looping through rows
DoEvents
'replace with values
rngDestRange.Value = rngDestRange.Value

[Justin7]

Learn a programming language and/or learn/interface with a statistical language like R, Matlab, etc.

Excel is pretty much worthless beyond fundamental exercises.

One big advantage of Excel is its universality. It's obviously not as fast or flexible as other languages. But, it is much more portable. If you are working with other people, translating your work to (or starting a project in) Excel makes collaborating much easier.

[MonkeyF0cker]

One big advantage of Excel is its universality. It's obviously not as fast or flexible as other languages. But, it is much more portable. If you are working with other people, translating your work to (or starting a project in) Excel makes collaborating much easier.

I don't see the point if Excel can't handle whatever you're doing. If whoever you're working with can't "translate" your work easily, that's just another reason why collaborating is completely inefficient. It's not hard to zip up a database and source, encrypt it, and ship it off to someone if "translating" isn't an issue.