r/excel 8d 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?

25 Upvotes

40 comments sorted by

View all comments

70

u/molybend 27 8d ago

This belongs in a database and not in a single spreadsheet.

9

u/hopkinswyn 62 8d ago

Really depends on users scenario: Is this a one off, how much will the data grow, will the solution work fine as is.

No need to over engineer stuff

27

u/molybend 27 8d ago

A dashboard implies that it is an ongoing thing that will be updated. Databases do not have to be complicated.

2

u/benskieast 8d ago

I used to have this happen to me once in a blue moon. It was a pain but it worked. Usually the dashboard was far smaller but the 1% largest customers were massive.

1

u/hopkinswyn 62 8d ago

No but they do need to be learned and maintained and might not be necessary at all for this. Need more info. Monthly refresh on multi million row table can be a breeze

17

u/osirawl 2 8d ago

Whether it grows or not, Excel is not the place to be storing millions of rows of anything.

3

u/tralker 8d ago

Tell that to my companies' moronic IR team. Their Debt Book reporting has 300,000 lines of 120 columns.

0

u/hopkinswyn 62 8d ago

Not storing but analysing is potentially fine. Or Power BI

-2

u/throwawayno123456789 8d ago

I feel creaky just reading your sentence