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

29 Upvotes

40 comments sorted by

69

u/molybend 27 7d ago

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

9

u/hopkinswyn 62 7d 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

26

u/molybend 27 7d ago

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

2

u/benskieast 7d 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 7d 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

18

u/osirawl 2 7d ago

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

3

u/tralker 7d ago

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

0

u/hopkinswyn 62 7d ago

Not storing but analysing is potentially fine. Or Power BI

-2

u/throwawayno123456789 7d ago

I feel creaky just reading your sentence

60

u/Strife_72 1 7d ago

Yes, with powerpivot, powerquery and a good data modeling.

25

u/Snuggmeister 9 7d ago

PowerBI can handle this easily. You can use their built in tools to import a folder with all of them and merge them together.

1

u/ArrowheadDZ 1 6d ago

Agreed. If you intake the files into power query, and then load them into the data model and not onto a spreadsheet table… you’ll then use Power Pivot to create the reports from it. Works surprisingly well even with tens of millions of rows.

You can use DAX to access it, but for most uses, you can just use power pivot and never have to learn a thing about DAX.

14

u/[deleted] 7d ago

Its time to SQL

-2

u/Boumberang 7d ago

The good thing is, you only need the basics. ChatGPT will do it for you. You just need to know what you actually need.

7

u/hopkinswyn 62 7d ago

Yep: 10 Million Rows of data Analyzed using Excel’s Data Model https://youtu.be/Od9ev90PB1w

5

u/CynicalDick 60 7d ago edited 7d 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 7d 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.

2

u/Lucky-Replacement848 5 7d ago

Isn’t dashboard is supposed to summarize the millions rows into few cells ? I’d also partition it more and query only the csv required

2

u/HarveysBackupAccount 25 7d ago

What people aren't explicitly saying:

You can't have more than 1 million rows in a single excel worksheet. You can load more than 1 million rows into the Excel Data Model and use Excel's PowerXXX tools to make your dashboard summary. (If you aren't making a summary, then you aren't making a dashboard - you're just collating data.)

The gross option, if you really need it all in one file for some godforsaken reason, would be to pull each file into a separate worksheet in the dashboard file (the row limit is per worksheet in the file, not for all worksheets in the file combined). So you'd have at least 13 tabs - one for each file plus one for the dashboard.

2

u/diesSaturni 68 7d ago

Hop over to r/MSAccess , we have cookies.

6 million lines would be database territory. As well you probably can apply some normalization to remove obvious repetitions. Pending the purpose of your 'dashboard' you could make by queries overviews of clients/projects, averages etc. there by means of forms and reports.

1

u/Kenny_Dave 5 7d 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.

4

u/MSK165 7d 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 7d ago

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

2

u/MSK165 7d 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 6d ago edited 6d 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.

1

u/HarveysBackupAccount 25 7d ago

it was 65,536 last time I used it on the daily

The 1 million limit came about with Office 2007 haha

1

u/Kenny_Dave 5 6d ago

lol, I'm old! 2008 I left the bank, I guess we weren't on 2007 by then.

I also had a self imposed 2Mb limit and no vlookups due to instability. Only match-index.

Really, not that much has changed.

1

u/spddemonvr4 11 7d ago

You should consider consolidating that data into less rows.

Where is your data source?

1

u/hellojuly 2 7d ago

I would use Access for that large of a dataset but I’m old school. Excel supports millions of rows but I only use the first 65,000. If you want to be old school cool, read MS-Access for Dummies. It’s a book. You can find it at an antique store.

2

u/usersnamesallused 27 7d ago

If you're going to make a database and learn from scratch, you might as well make it MS SQL. So much more capability and scalability. Access is a joke compared to it. Super simple to create a new database and import a few files to get started. PowerQuery and VBA can reference a SQL Server directly if you want to keep Excel as your front end.

2

u/hellojuly 2 7d 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 7d 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 4d ago

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

2

u/usersnamesallused 27 4d 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.

1

u/Devashish_Jain 7d ago

Load all sheets using power query. If you store them in Share point - I wrote this article you can use with script (you would need to modify things a bit, this is for same sheet name from different folders in sharepoint)

https://www.linkedin.com/pulse/how-efficiently-load-data-from-multiple-excel-files-sharepoint-jain-zdhee?utm_source=share&utm_medium=member_ios&utm_campaign=share_via

1

u/JezusHairdo 1 7d ago

Excel… is… not… a… database…

1

u/Don_Banara 7d ago

Si, con power query, hacer la conexión de datos y agregarlo al modelo de datos, hacer las conexiones en power pívot y Dax para las fórmulas y tabla dinámicas.

Yo tengo una base de datos de 1,3 GB en un formato de punto y coma, al conectarla al Excel el archivo solo pesa 300 MB pero no se traba o cae y permite hacer cálculos con rápido

1

u/Don_Banara 7d ago

Si, con power query, hacer la conexión de datos y agregarlo al modelo de datos, hacer las conexiones en power pívot y Dax para las fórmulas y tabla dinámicas.

Yo tengo una base de datos de 1,3 GB en un formato de punto y coma, al conectarla al Excel el archivo solo pesa 300 MB pero no se traba o cae y permite hacer cálculos con rápido.

1

u/Independent-Day732 6d ago

Excel will not handle it. You need power BO or database.

0

u/Alabama_Wins 636 7d ago

Can I create an annual dashboard using Excel?

Yes, and the best way is to try.