How much is a lot of data for Excel?

Question

excel

What are the metrics for evaluating Excel files for "this is already a lot for Excel."
Is there any official documentation where it is written like "if 100500 cells are filled in Excel, this is already a system limitation" or in MB
The bottom line is that there is a tradition that has developed over the years to solve certain problems in Excel.
Some files have turned into "fancy software" from a bunch of relationships, data, graphs and VBA.
In some cases, they "slow down" a lot, which is not surprising.

However, it is not clear how to calculate from what the problem is.
from formulas/relationships or for a large amount of data.

In general, there are probably even three questions:
1. exact metrics and how to measure that "this task is no longer for Excel."
2. how to evaluate the impact of certain entities (formulas, graphics) on the overall performance of the file.
3. where is the boundary between Excel and DB/Python/R

Answer 1

The bottom line is that there is a tradition that has developed over the years to solve certain problems in Excel.

Excel has changed over the years. Once upon a time there was a limit of 65 thousand lines. Once upon a time there was a limit of 1-5 megabytes per file.
If we take just data, then Excel copes quite well with large volumes, it rather rests on the RAM on a particular computer.
But if you start to design, then I saw how a rather small file, but with the design in each cell, excel is forced to freeze.
Too many links to external files is bad. Excel is still not a database, and updating from different files will slow down.
If you have a lot of functionality, it might make sense to think about some kind of application, maybe with a base, but you can do it in half.
Moreover, many libraries can work directly with Excel.

Answer 2

I usually look at the instructions
1.
https://support.office.com/ru-ru/article/%D1%82%D0...
2. google excel profiler
3. Apparently you already have.

Answer 3

in practice, I came across this - there was an unload from two databases. I had to do a fuzzy text search and combine by key column into one table. I used fuzzy lookup. Excel compared 90 thousand rows from one table with 300 rows from another. It crashed on large volumes .it took 40 minutes on core i7, taking into account that excel loads each core only by 25%. I repeated the results many times and glued them into the final table. now I already know what pover pivot is and would solve this problem correctly and faster. but then there were no options. The deadlines were running out. On a python, this could be ideally implemented, but the person who undertook to solve the problem failed all the deadlines and did nothing. cuda cores from the video card are connected by people (but things didn’t go beyond the prototype).in general, working with olap cubes and pover pivot + pover query + pover bi- I can say that I never reached the limits. YouTube works wonders in terms of learning. look at the channels of stanislav salostey and nikolay pavlov. there is more than enough information. and if further dig, then for example I use python and node red.
and as said above, you need to be careful with conditional formatting. on this issue, here is an excellent solution https://www.youtube.com/watch?v=sc9xyMl1uu8&list=P...