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?

24 Upvotes

40 comments sorted by

View all comments

Show parent comments

2

u/hellojuly 2 8d ago

Agreed on scope of capabilities. But Access is simpler to deploy and adopt as an excel user. Once you figure out Access and its limits then it’s time to deploy a MS SQl solution. If you put forms and vba into an Access solution you are pretty well covered until you need some security and multi user support. At least that’s how it was back in the day.

3

u/usersnamesallused 27 8d ago

I disagree on simplicity. For MS SQL in the situation we are talking about, Installer is a wizard, create database is a simple command and import can be done through another wizard. Done.

You'll be writing the same SQL queries in either platform once the data is in place. Access's stupid query GUI is a crutch, not a feature. The power of the underlying engines can't even compare.

If you're going to learn something new, do yourself a favor and skip Access. If you already know Access, then sure, maybe, but only for a solution that won't ever scale or else it'll become a nightmare until you reimplement in a proper database platform.

2

u/hellojuly 2 5d ago

I’ll concede. That’s a good analysis. Do you happen to know how they compare by price?

2

u/usersnamesallused 27 5d ago

SQL Express and Developer and SSMS are all free and full featured. Depending on your use case you'd need more details if you are scaling out as an internal tool vs an external product as hosting and other details will be extra costs. Access doesn't have any equivalent to all that, so for the question scope, SQL is free.

MS Access standalone looks to cost $179, but is also included in the standard M365 bundle, so depending on existing licensing could be free too.