How To Process Large Excel Files With 1MM Lines

[mdsurf]

Find myself in a situation where I need to run equations on a huge excel with over 1 million lines of data on it. Problem is, anytime I try to do anything, even clicking on a cell somewhere excel usually crashes.

Wondering if there's a better way to do this or maybe a service out there that can help run files this large?

Thanks!

 

[kennypete]

Excel is limited to just over 1m rows (ref Excel specifications and limits)

Depending on what you are doing, most databases like Access or Sqlite do not have such limits on their tables and may work for crunching the data.

Maybe post to the General Discussion & Other Applications forum for ideas? (including the sorts of "equations" you need to run as I expect the answers will depend on those too)

 

[Alex Blakenburg]

Also how complicated are the formulas and do you need to work with the detail records or can you return the data in a summarised or pivotted format.
If the formulas are not that complex and / or more lookup based then perhaps you can do what you need in Power Query with the addtional option of using the data model.