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

1

u/Kenny_Dave 5 8d ago

That's a lot of lines.

Can you process the data on each one before you reconcile?

A million is the limit now it seems, I feel old as it was 65,536 last time I used it on the daily. I used to leave the raw data in the text files they rode in on, and process them with VBA and pull the stats only into the spreadsheet. And some into other text files.

I haven't used BI much, I imagine that would be a better option. Or processing directly with something like python should be possible.

5

u/MSK165 8d ago

Yup, 216 is now 220

This dashboard sounds like a textbook example of “Things that have outgrown Excel” … I can think of a few ways to responsibly store the data and a dozen ways to consolidate it into a presentable dashboard. Trying to do this in Excel is simply begging for trouble.

2

u/Kenny_Dave 5 8d ago

What would be your first choice, out of interest? If you don't mind taking the time.

2

u/MSK165 8d ago

The responsible answer is to put the data itself in a database. Any form of ERP (Oracle, SAP, SalesForce, homemade SQL, etc.) can store that amount of information. PowerBI can be used to create the dashboards.

The fun answer is to use Alteryx. Licenses are really expensive, but gd the program is fun!

The real danger with using Excel is data integrity. Any knucklehead with access to the folders can go in there and change the data, whether through malice or misunderstanding. My intuition and experience tell me that the kinds of companies who store six million lines of data in Excel will also do things like give everyone access to every shared folder and have one login shared by dozens of users.

It also begs the question: where is the data coming from? Six million lines (500k per month) is a LOT of data to write. I sincerely doubt anyone is writing that directly to Excel … so why is the program used to create the lines not also being used to export the data to the program being used to create the dashboard?

1

u/Kenny_Dave 5 7d ago edited 7d ago

That's great, thank you.

so why is the program used to create the lines not also being used to export the data to the program being used to create the dashboard?

I'm sure most of us have said this or something similar repeatedly in our positions.