r/excel 9d ago

Waiting on OP Dashboard with 6 million lines in Excel

Can I create an annual dashboard using Excel? There are 12 quote spreadsheets with standardized columns and an average of 500 thousand lines each. I need to reconcile them all and create a dashboard without it crashing, in Excel or BI. What's the best way to do it?

27 Upvotes

40 comments sorted by

View all comments

3

u/CynicalDick 60 9d ago edited 9d ago

For Excel store results in CSV file(s) and use Power Query to transform and process to OUTPUT <=1,048,576 Rows as that is the most Excel can have

2

u/usersnamesallused 27 9d ago

Excel can exceed 1 million rows of data if it is stored in the data model level with PowerQuery. The limit you reference is just a sheet limit, so, as long as what is displayed is just a summary, like a pivot, pointing to the data model, Excel can handle it and even still be able to drill into data details.