3 ms·

> Putting more than 1M rows of data into Excel is not possible, and once you get into the low 100K's, it becomes almost unbearable. I dispute this. Yes, the no

> Putting more than 1M rows of data into Excel is not possible, and once you get into the low 100K's, it becomes almost unbearable. I dispute this. Yes, the normal spreadsheet view of excel will buckle under 1M rows, but excel has another feature called "Power Pivot" that is backed by an embedded database and scales into the high millions at least. I've personally used excel on a dataset of 18M rows and PowerPivot handled it just fine. [0] https://support.office.com/client/Data-Model-specification-and-limits-19aa79f8-e6e8-45a8-9be2-b58778fd68ef https://support.office.com/client/Data-Model-specification-a... [1] https://support.office.com/client/power-pivot-powerful-data-analysis-and-data-modeling-in-excel-a9c2c6e2-cc49-4976-a7d7-40896795d045 https://support.office.com/client/power-pivot-powerful-data-...

texasbigdata 6y ago
Big fan of this, but powerpivot sits on vertipaq (I believe) which is an in memory columnar DB or sorts (apologies if that’s incorrect). So at this point you’re getting awfully close to direct querying (another msft feature) which while analogous resembles more traditional db/client if you squint hard enough. But yes, big fan of vertipaq which I believe also powers PowerBI.
- craig_asp 6y ago
  Just to clarify.. Yes, vertipaq is the tech behind power pivot, power bi and sql server analysis services (in tabular mode) and the same column-oriented storage is also used in sql server. Excel generates queries against the data stored in a vertipaq model. You cannot write normal excel formulas on top of it and you have to use DAX (a unique to msft language, which is the replacement for MDX) instead, which is pretty much a no-go for anyone but well-trained power users.
aarondia 6y ago
There's also a workaround in Google Sheets where you can store your data in BigQuery and use the spreadsheet to interact with it.
stilisstuk 6y ago
Yes pp can handle data. But vba can not. And most spreadsheets contain vba for reporting and magic interfaces for managers. Slow and unmaintainable. Any day: rmarkdown and csv

texasbigdata 6y ago

Big fan of this, but powerpivot sits on vertipaq (I believe) which is an in memory columnar DB or sorts (apologies if that’s incorrect). So at this point you’re getting awfully close to direct querying (another msft feature) which while analogous resembles more traditional db/client if you squint hard enough. But yes, big fan of vertipaq which I believe also powers PowerBI.

craig_asp 6y ago
Just to clarify.. Yes, vertipaq is the tech behind power pivot, power bi and sql server analysis services (in tabular mode) and the same column-oriented storage is also used in sql server. Excel generates queries against the data stored in a vertipaq model. You cannot write normal excel formulas on top of it and you have to use DAX (a unique to msft language, which is the replacement for MDX) instead, which is pretty much a no-go for anyone but well-trained power users.

aarondia 6y ago

There's also a workaround in Google Sheets where you can store your data in BigQuery and use the spreadsheet to interact with it.

stilisstuk 6y ago

Yes pp can handle data. But vba can not. And most spreadsheets contain vba for reporting and magic interfaces for managers. Slow and unmaintainable. Any day: rmarkdown and csv