Forum Discussion
How work with a DATA of more de 70.000 rows?
I'm working on an Excel from a csv file with over 70,000 lines. But when I use the formulas of x.lookup, Sum.ifs or unique... Excel doesn't show all the details because Excel has more than 10,000 ...
There isn't a limitation like this. Both SUMIFS and SUMPRODUCT work for 100000 rows and for any other number of rows. It's the same for other functions such as VLOOKUP. Can you attach your .xlsx without sensitive data which doesn't return the expected result for more than 70000 rows?
If you have more than 1 million rows you can apply POWER QUERY or POWERBI because the Excel sheet has 1048576 rows. Of course you can use POWER QUERY for fewer rows as well.
For SUMIF, MINIF, MAXIF, AVERAGEIF... you can as well consider a Pivot Table.
- OliverScheurich,
So my excel is weird, because i got this message:
"This column contains over 10,000 unique items. Only the first 10,000 unique items are displayed."I guess this message when you use filtering, not with formula
Not all items are displayed in the AutoFilter/PivotTable list (microsoft.com)
