9 ms·
What’s the origin of the phrase “big data doesn’t fit in excel”?
- SunlightEdge 6y agoIt's been common knowledge since... Forever really. I doubt there was a specific person. It just spread across industries due to the known limitations in performance. Excel was well known to not be able to handle large datasets back in 2006 when I started in data analytics field. I'd guess Excels limitations go way back to when it was originally released. Excel is very good btw. A marvel in many ways. It just becomes painful with big data around the 100-300k mark.
- SunlightEdge 6y agoThe million row mark for excel while widely known is never taken seriously by anyone who actually uses excel often. It's performances collapses around the 100-300k mark.
- tyingq 6y agoI would guess it depends on the columns as well. I just opened a million-row, 10 column spreadsheet and added a few formulas like =average(a1:a999999) and it's responsive...no hangs or other issues. "Save as" ran for a 5 seconds or so. On an older i5 laptop with 8GB.
- SunlightEdge 6y agoIt's difficult to comment without seeing your workbook. But broadly if you're developing any kind of useful analytical model you will find the performance fall off a cliff very quickly if using 1 million rows. e.g. waiting around 25 minutes to save the file (or being corrupted), the software being suddenly very slow and weird. And this includes tricks like turning off automatic calculations, hard copy pasting in the results of calculations (rather than keeping all cells as formulas), only running calculations on specific cells etc. There are things you can do to try and manage big data in excel. But it becomes a chore and ultimately a big bloated monster. And why bother with the pain when you can use Python (e.g pandas) or R or SAS. Power excel / an SQL backend (with VBA) is also a common solution. Or was. I mostly work in Python/R these days.
- CryptoBanker 6y agoTry adding a recursive formula or two in there and see what happens
- LegitShady 6y agoMy guess is that even if it is factorable it would be made into its own separate product or attached to another program like power query or their bi offerings.
- tyingq 6y agoHm. Here's the various limits for Excel: https://support.microsoft.com/en-us/office/excel-specifications-and-limits-1672b34d-7043-467e-8e27-269d656771c3 https://support.microsoft.com/en-us/office/excel-specificati... Everything from Excel 2007 and up supports 1,048,576 rows by 16,384 columns, if you use the XLSX format. The older XLS format tops out at 65536 rows and 256 columns. You can, of course, "shard" worksheets ;)
- deleted 6y ago[deleted]
- dopidopHN 6y agoWhat I was getting it at exactly. What prevent a user to split data in thousand of spreadsheet? Given how the corporate world run on excel... someone has done it, somewhere.
- chrismarlow9 6y agoI'm sure it happens all the time from a logical perspective without the user even knowing what a shard is. Example: Too many rows? Okay let's just break it up into monthly sheets and then change this formula to look up the value based on the month. Maybe just use a drop-down for the sheet selection. I think most people could arrive at this answer intuitively who have enough excel experience.
- conductr 6y agoIn practice, excel has all types of issues as you approach a fraction of the available row/column maximums. Your files will be crashing, corrupting, or just impossible to use. If any of your rows or columns have formulas it’s order of magnitude worse at high volumes. That said. Excel has power query/pivot and connects to a large number of external databases, flat files, etc. and it’s best to just never import the entire dataset into excel. That’s my view as a tenured financial analyst working with excel professionally since version 2003.
- AstralStorm 6y ago
- sumnole 6y agoIf Microsoft releases a solution for viewing big data in Excel, does data have to become even bigger to be "big"? They're stuck endlessly chasing a carrot! :)
- kreeben 6y agoMy thesis is MS will never go down this path, not because it isn't an excellent idea but because Excel has become inrefactorable.
- bitwize 6y agoThat's okay, they'll just rewrite it -- largely in JavaScript -- and offer it as a service as part of Microsoft 365. It could then ingest datasets from anywhere in the cloud, even large SQL or MongoDB databases.
- dragonwriter 6y ago> My thesis is MS will never go down this path, not because it isn't an excellent idea but because Excel has become inrefactorable. They’ll just make an optional feature called PowerSomething that is integrated with the Excel UI but mostly a separate tool that interacts with the main Excel system without sharing its limits.
- PeterisP 6y agoYes, sure - the "big data" issues are essentially about data above a certain point where very different techniques for processing it (e.g. cluster computing and algorithms to split and merge partial results of computing that's not trivially parallelizable) start to make sense or even become required. If technology progress grants a huge increase in available memory and computing power and algorithm efficiency, then that boundary moves upwards. There are early "big data" publications on datasets that decidedly shouldn't be treated as "big data" today because now it's entirely appropriate to process them with simple methods within the RAM on a decent server or in some cases even on my laptop.
- rvba 6y agoExcel can connect to relational databases since forever. Prehistoric versions had Microsoft Query and new ones have Power Query, which allow to build SQL. There are also various GUI addons that connect Excel to a database. For example SAP has one in their Business Warehouse (I think they try to change into something else). Also the "new" Power Query removes a lot of limits.
- random5634 6y agoDo a ton of excel work - the 1m row limit is surprisingly low. And sharding across worksheets is miserable in my view
- edrobap 6y agoIt amuses me how the low limit nature of Excel has affected the ecosystem around it. Apache POI, which is a popular library to operate on Excel files, has a weird 4GB limit [1] on uncompressed file size. throw new IllegalArgumentException("Max entry size is bounded [0-4GB], but had " + maxEntrySize); [1] https://svn.apache.org/viewvc/poi/tags/REL_5_0_0/src/ooxml/java/org/apache/poi/openxml4j/util/ZipSecureFile.java?revision=1885418&view=markup https://svn.apache.org/viewvc/poi/tags/REL_5_0_0/src/ooxml/j...
- simias 6y agoWhat makes you think this limitation is related to Excel? 4GiB is a very common limit due to it being the max size you can fit on a 32bit integer. It's the max file size on FAT32 as well for instance.
- edrobap 6y agoThanks for bringing the 32bit point. Turns out that .ZIP specification has the limit [1] . The solution here is ZIP64. Excel does support ZIP64 but the implementation isn't suitable higher sized zip files [2] [1] https://en.wikipedia.org/wiki/ZIP_(file_format) https://en.wikipedia.org/wiki/ZIP_(file_format) [2] https://issues.apache.org/jira/browse/COMPRESS-474 https://issues.apache.org/jira/browse/COMPRESS-474
- amelius 6y agoDoes it matter? Because I'm guessing that sooner rather than later Excel will be updated so that big data will fit just fine (using cloud technologies).
- ur-whale 6y agoDo glaringly obvious things need to have an origin? What value could that have?
- edent 6y agoAs I say in the post, I saw it quoted in an academic paper and I wanted to see who actually said it. That's then a good way to understand why they said it and what they actually meant. So, for me, the value is understanding the provenance and assumptions of the quote.
- Scarblac 6y agoI seem to remember a blog post one day about someone interviewing for several "Big Data" positions - where the data turned out to be some Excel sheets. Or fit on a thumb drive. The hiring companies that thought they had "big data" really didn't, was the point. The blog post was widely shared around my office at the time and I see it as the origin of this idea. Edit: I think it was https://www.chrisstucchio.com/blog/2013/hadoop_hatred.html https://www.chrisstucchio.com/blog/2013/hadoop_hatred.html, HN discussion https://news.ycombinator.com/item?id=6398650 https://news.ycombinator.com/item?id=6398650 But the idea wasn't that data was big as soon as it didn't fit in Excel anymore - Excel was a reductio ad absurdum. If it still fits in Excel, it's laughably far away from being "big". That was the idea. Big data was about the problems you get when you need to join data together that you can't fit well in one database server, not even with the amounts of memory and disk space you can get these days. It was Google type problems, the stuff that map-reduce needed to be invented for. The kind of problem that most companies just don't have. So the term mostly disappeared from job descriptions, "data science" became popular instead, and people keep using Excel because it is great. (that blog post is from 2013 so it can't be the source of that quote from 2012. But please don't define big data in terms of Excel in a 2021 MSc thesis)
- edent 6y agoThanks for that link. Interesting that it was written a year-or-so after the phrase became popular.
- hkmurakami 6y agoFriend in one of the big data vendors: "we mostly work with medium data"
- nerdponx 6y agoThe way I always think of it: Medium data fits on disk but not in memory. Big data doesn't fit in either.
- alexchamberlain 6y ago
- Vaslo 6y agoThere are definite practical limits in Excel. Even at 100k rows, some of the more complex financial models we have made get very difficult to work with. We’ve spent days learning and trying new ways to speed them up (never use Vlookup or offset, etc) and they became usable but still frustrating. I guess the biggest upside is that it’s forced my team to go out and learn SQL, Python, etc. However that has it’s own downside because we are the only ones in our division that know these tools, so we get stuck maintaining stuff that we shouldn’t!
- bob1029 6y agoHaving everyone on the team competent in SQL is not something that is an unreasonable objective. Even project managers and executives could benefit from knowing how to write their own queries against various data sources. As wizened developers, we can assist our more business-like coworkers by developing views and reporting databases that help to present a more consistent and higher-order perspective of the world. Just having a document that lists out examples of SQL they can use is 99% of what most people need to get bootstrapped. Getting your problem domains modeled in SQL and using the appropriate form of normalization is foundational for managing non-trivial levels of complexity in larger projects. For me, non-trivial complexity means any domain model with more than 10 related types or more than 100 total properties to deal with. Relational modeling is one of the most powerful abstractions we have available for working with anything that goes beyond the 3 spatial dimensions that we can see with our eyeballs. I have dealt with queries in factory automation that join over 40 tables to produce some important projection. You could make a bunch of assumptions about how the domain model should be shaped in some complex object graph monstrosity and then stick it into MongoDB, or you can leave it neatly organized and indexed such that any reasonable query can be made of the data to produce virtually any shape of output you need. With all of that in mind, Excel is still one of the most powerful tools on your computer for documenting virtually anything. Any problem domain can be represented as tables of things and relations between them. Anyone can figure out the most important parts of this tool with just a few minutes of screwing around with it. It is trivial to take a model from someone's xlsx and turn it into proper SQL tables and then slap a front-end and business logic around it. When someone wants me to write a new piece of software for a new problem area, we always start with types, properties, and relationships between these things. Excel is a perfect fit for the first phase of any software project.
- shagie 6y agoThis reminds me of DevOps Borat, from Jan 8, 2013: https://twitter.com/devops_borat/status/288698056470315008?lang=en https://twitter.com/devops_borat/status/288698056470315008?l... > Big Data is any thing which is crash Excel.
- edent 6y agoThanks. Although that's a few years after the phrase was first popularised - it's still funny :-)
- inshadows 6y agoSorry for off-topic. Several years ago I watched part of a video lecture about implementing Excel principles. It was a talk about functional programming, and he may have used Haskell, or Scala. I found it interesting because it began with him explaining how Excel is huge array and how changes propagate. Do you have a link to this talk/lecture? Thanks in advance!
- known 6y agoComplete/Big data should be loaded into memory for doing any processing in Excel;
- CalChris 6y agoI'm reminded of Joe Hellerstein's (and Oz Nova's summation of it, You Are Not Google [1] [2]) about even actual Big Data people getting their scale wrong and thinking they're Google: The thing is there’s like 5 companies in the world that run jobs that big. For everybody else… you’re doing all this I/O for fault tolerance that you didn’t really need. People got kinda Google mania in the 2000s: “we’ll do everything the way Google does because we also run the world’s largest internet data service” [tilts head sideways and waits for laughter]. [1] https://blog.bradfieldcs.com/you-are-not-google-84912cf44afb https://blog.bradfieldcs.com/you-are-not-google-84912cf44afb [2] https://news.ycombinator.com/item?id=19576092 https://news.ycombinator.com/item?id=19576092
- nojito 6y agoHow does no one in this thread know about power query and powerpivot? I’ve been using excel with tens of millions of rows for years now.
- fencepost 6y agoKeep in mind that for the people discussing this 10-12 years ago Excel had almost always had a limit of 64k rows. As another comment notes Excel 2007 is where that was increased to > 1,000,000. If you were a recognized person in data science you'd probably been using tools with the 64k limit for at least a decade.
- mr_toad 6y agoThe point of the quote is not about a specific technology, it’s that regardless of what technology you use if it’s limited to a single machine you’ll eventually run out of resources.
- nojito 6y agoTalking more about the thread itself. Everyone likes to use excel as a punching bag for some reason.
- narush 6y agoExplicit data size limits (something on the order of 1M rows at time of writing) are only one part of the problem in trying to use Excel with Big Data (TM). Generally, if you've got millions of rows, you're working with some sort of data export from another tool (e.g. different from some financial data you're using to model your companies finances). And with millions of rows of data pouring out of some other tool, usually you're trying to define a repeatable process to clean/munge/transform that data into something more useful to you/your team/your management. Within Excel, there are ways of accomplishing the "define repeatable task" goal - but my personal experience working with VBA (and talking to VBA users across the spectrum) is that it's a horrible language that is absolutely no fun at all to write. Good luck using a nice library to do anything with it, really. I'm a co-founder of Mito [1], where we're taking a bit of a different angle. Rather than bringing big data into Excel, we're bringing an Excel ethos to where you might work with your big data otherwise. Mito is a spreadsheet interface that lives inside of a Jupyter notebook; you can write spreadsheet formulas, merge datasets, explore summary stats, all from within this spreadsheet. While you edit the spreadsheet, it generates valid Python code for you. Our current users mostly fit the bill of "previous Excel junkies who started teaching themself Python but still have a lot to learn, so use Mito to augment/speed up their workflow." Questions / comments / hard-hitting HN feedback greatly appreciated! [1] https://trymito.io/launch https://trymito.io/launch
- indymike 6y agoThe joke in our office was, "Big data doesn't fit on a thumb drive." At the time a big thumb drive was 64GB.
- dfilppi 6y agoSounds like a feature request for MSFT
- supernova87a 6y agoI'll get back to you on that as soon as my sheet finishes calculating for 5 min because I changed the value of 1 cell. Ohh..sorry, make that 10 min -- I had to save the file.
- social_quotient 6y agoMaybe it’s the limits that use to push you in to a corner of pain? 118 character file name limit 65,536 row limit till 2011 256 columns till 2011 2gb memory limits Those are the structural limits with should be good for a lot of things but... there are the practical issues of it freezing and having issues while actually using it at any scale or any sort of complexity.
- atat7024 6y agoAcademia.
- aappleby 6y agoIf you can lift the device it's stored on with your bare hands, it's not big data.
- whalesalad 6y agoExcel can barely handle a moderately sized csv these days. It’s inspired me to write a performant csv editor.