I'll be honest, the article kind of felt lacklustre in expectations. Some suggestions: An interview with THE creator of the spreadsheet, Dan Bricklin (2015) Beyond rad starting quote on that one...
Exemplary
I'll be honest, the article kind of felt lacklustre in expectations. Some suggestions:
Beyond rad starting quote on that one about creating the digital spreadsheet: "It’s kind of cool. It’s like whoa, we haven’t thought of something better yet."
Dan also has a fantastic, very web 1.1, history and collection of historical photos, items, and writings on the development and history on his website. There is a lot there.
A decent short piece of writing outlining the history, as it was when it was written in 1995.
Addendeum: Two of my favorite books from the past year was reading, "The Filing Cabinet: A Vertical History of Information" by Craig Robertson, or one that may have been even better, "The Phone Book: The Curious History of the Book That Everyone Uses But No One Reads" By Ammon Shea. Both are some of those 'boring' subjects I love to read about in intense detail.
I am surprised there isn't one about the spreadsheet, going all the way back to paper records, to the creation, the legal and corporate wars of the 80s-00s, and then some. Maybe something I should look into writing, been looking for a good topic - but if anyone has any books on the subject, let me know! My cursory search is coming up nil.
My personal why Excel is so enduring:
Excel is a programming language that is easy to use, easy to become moderately decent at, and impossible to master. The average person feels like they are in control and doing some interesting computer magic.
It's really one of the very few ways you can use a computer these days and feel like you are doing something Important.
It feels like magic. You can see your changes in real time, you can combine, link to, and change variables at will and the entire document changes. Graphs can go up, down, colours can be validation, and you can connect ideas and pieces of information together and truly do something with it. It's visual magic that gives value.
Stable, never really changes other than the basic interface, this isn't because they reached some pinnacle of engineering excellence, it's purely because it's so bug ridden and has decades of cruft and legacy work that relies on it. It's stable, never-changing, only because it's so unstable and never will be able to change. You learn the basics once, and you're good for the next twenty years, people like that certainty even if they haven't thought about it.
An infinite canvas of possibilities, you can scroll and never reach the end, it feels limitless.
Convergence of the above: It's so bland, it's perfect. You can just open it, and immediately start using it. Some people use it for data, others go so far and use it for knitting patterns.
I use excel for a lot of more typical spreadsheety uses, but similar to the knitting patterns, one of the more creative applications Ive done is garden planning. It's great for It's versitility....
I use excel for a lot of more typical spreadsheety uses, but similar to the knitting patterns, one of the more creative applications Ive done is garden planning.
It's great for It's versitility. On one sheet I can use to colourcode a garden plan (1 cell = 1 sq ft), another I can list and calculate various dates (seeding, sewing, tranplanting, harvest...), on another I can figure out how much of which plant to buy, etc.
I've heard Excel referred to as "the second-best tool for everything", and that's not terribly inaccurate in my experience. I don't know what engineering would look like without it.
I've heard Excel referred to as "the second-best tool for everything", and that's not terribly inaccurate in my experience. I don't know what engineering would look like without it.
I learned so much using excel for school, university, work.... I mostly use sheets now because my work is based on Google Workspace, but I have no preference for a spreadsheet tool as long as I...
I learned so much using excel for school, university, work.... I mostly use sheets now because my work is based on Google Workspace, but I have no preference for a spreadsheet tool as long as I can do what I need to do, and it's fairly easy to write a script or macro as needed.
This is a fluff piece with no real content. All it really says is “these three random people use and like excel”. I don’t really have anything to say on this article. Excel is fantastic and I love...
This is a fluff piece with no real content. All it really says is “these three random people use and like excel”. I don’t really have anything to say on this article.
Excel is fantastic and I love it! I also despise it with every fiber of my being. I truly think that nobody can ever love excel without also hating it, unless they don’t really use it very much.
Spreadsheets are central to computing. After all, basically all data in the world lives inside a database at some point. A database is nothing more than a spreadsheet without a front end. Fundamentally there is nothing that can be done in a database that couldn’t also be done in a basic spreadsheet program. Postgres will always be better for large data sets and transactional data, and duckdb will always be better at handling massive amounts of data, but they are both fundamentally identical to a spreadsheet.
Having access to a spreadsheet is a huge productivity boost. Once you get some experience, a spreadsheet starts looking like a fantastic hammer for every nail you come across. Excel in particular has so much cruft built in to solve all sorts of issues people have. That, I think, is why excel is the best spreadsheet program. I’ve tried libreoffice calc, and I keep coming back to apple numbers and wanting to love it, but I always end up back on excel. When you hit a limit in one of the other office programs, you are at the limit. You will never be able to do what you want to do. Excel doesn’t really have limits. When you hit what looks like a limit, you can find some ancient forum post with some magic formula, or some VBA script and get your task done.
There are massive problems with spreadsheets though: Bad access control (should X have access to Y) No versioning No auditing Bad backup options Compatibility issues over time If a spreadsheet is...
There are massive problems with spreadsheets though:
Bad access control (should X have access to Y)
No versioning
No auditing
Bad backup options
Compatibility issues over time
If a spreadsheet is only ever used by one person, it works great. As soon as that spreadsheet gets shared and gets turned into a small application, things start getting hairy, fast.
People who love spreadsheets often never think about these aspects of it, until one day its discovered some intern wiped half the spreadsheet by accident and the backups aged out.
Don't get me wrong, I use spreadsheets daily. It's how I track my budget. But it's mostly used as a glorified calculator.
Some of these problems get ironed out by tying the spreadsheet to a database backend, but the coding aspects can definitely get accidentally borked very easily, especially in a multi-user environment.
Google Sheets solves a lot of those issues, but introduced a number of others, like depending on third party tools for things that are native to Excel.
Google Sheets solves a lot of those issues, but introduced a number of others, like depending on third party tools for things that are native to Excel.
No structure. This doesn't matter (and is potentially kind of an asset) when human beings use it as computerized graph paper, but it is an enormous fucking pain in the ass when they then decide...
No structure.
This doesn't matter (and is potentially kind of an asset) when human beings use it as computerized graph paper, but it is an enormous fucking pain in the ass when they then decide they need their abomination data liberated and hand it off to me to write software to process, which always happens eventually for any sufficiently long-lived spreadsheet.
Some (almost certainly not exhaustive, I've blocked plenty of trauma from my memories) examples I've personally run into:
Excel actually allocates cells, and it does so lazily, and there's a failure mode where it will allocate the bottom-right cell in a sheet for no reason. If you're iterating rows (which is basically the only reasonable way to try to get structured data out), you'll get your data rows, then a million empty rows, then the very last row with one empty cell in it (in a column you're certainly not looking at). This will either break your processing or just waste a ton of time, depending on how much effort you put into handling ill-structured data.
Also, oddly enough, a hair over a million rows is not enough for lots of datasets. (And if, god help you, someone is slinging around the Office 2003 format, that has a limit of 64Ki, which is not enough for any dataset worth wasting a software engineer's time on.) It is oddly easy to silently truncate when you hit the row limit, as very infamously happened to England's public health service with COVID data.
If there are dates in a spreadsheet, they are wrong. You should basically just count on it. Excel's date recognition and mangling is tailor-made to corrupt dates.
Because Excel goes to great lengths to infer cell types, you can't round-trip arbitrary strings through it. It will decide some strings are a different type, convert them, then format them differently on the way out. (This is far and away most prevalent with dates, but definitely affects other types, too.)
Of course, the data in any individual cell is totally free-form. You have a sheet that looks like a big table, with one column that looks like it's got numbers in it, but if your software isn't prepared for one of those cells to contain an embedded rickroll, it's going to crash. Excel has this nice feature where you can set a limited list of possible values, but aside from being per-cell (and thus almost certainly inconsistent), it's part of the cell's format, purely advisory, and you should probably count on some cells having data that doesn't conform.
All of those are why I hate excel. Also poor options to have multiple users editing the same document. In theory multi user editing works with OneDrive/sharepoint. In practice, it breaks almost...
All of those are why I hate excel. Also poor options to have multiple users editing the same document. In theory multi user editing works with OneDrive/sharepoint. In practice, it breaks almost every single time I used it. It might be okay for very simple spreadsheets, but for anything complex it breaks so quickly.
But I have one nitpick/question about your issue list. Compatibility issues. I will admit that I didn’t have much need to open old excel files, but my impression was that excel compatibility was legendary. The latest excel 365 has native support for the excel file format used back in 2003. I am kinda tempted to build out a spreadsheet in the copy of excel I have for my Mac Plus and see if it can open on my modern copy. Heck, excel still has known bugs because fixing them would break old spreadsheets (the 1900 leap year bug was apparently copied from lotus 123 to maintain compatibility). Excel even has some examples of forward compatibility I found. They made a LAMBDA function that, combined with the name manager, allows you to define custom functions without using VBA. I wanted to use this for sheets for my job, so I had IT install the version (I think 22h2 or 23h1) before it was deployed to the entire company. I was fully expecting sheets made with lambdas to break when my coworkers with old versions opened them. But they could open them without error, and the calculations still worked perfectly. They couldn’t see the nice lambda formatting, but as long as they didn’t change my formulas, it worked on their version of excel that was released before lambdas existed. Personally I have never seen excel intercompatibility issues.
They do have a pretty heavy-handed warning. Maybe it's just my trauma from desktop support circa 2007 and having to deal with heterogenous environments with files dating back to the early 90s. And...
Maybe it's just my trauma from desktop support circa 2007 and having to deal with heterogenous environments with files dating back to the early 90s. And database connectivity gets messy fast, especially when versions change.
That and the subtle periodic breakage of 3rd-party tooling which is intended to mitigate some of these problems. This was way worse in those .xls days though.
You'll find that the majority of those problems are either unimportant to a large number of users or are otherwise acheivable to a good enough extent. For access control, cells can be locked...
You'll find that the majority of those problems are either unimportant to a large number of users or are otherwise acheivable to a good enough extent. For access control, cells can be locked behind a password. Some spreadsheet formats do allow for versioning. Most offer some sort of comments system to use for auditing functionality. Backup is done with an external program. And compatability isn't really as much of an issue as you'd think if for no reason other than most people aren't familliar enough with the more advanced functionality to worry about things breaking.
If anything, I think the problem with spreadsheets is that a lot of the most powerful things are not obvious and they're things that people wouldn't even know how to ask how to do. Go ahead, ask the average person off the street what a pivot table is.
You're right, for the majority. And that's kind of why it's a problem. None of those features get used. Even if they did, you can't gate access to a spreadsheet with SSO in a reliable way. You...
You're right, for the majority. And that's kind of why it's a problem. None of those features get used. Even if they did, you can't gate access to a spreadsheet with SSO in a reliable way. You can't gate access permissions based on department or data security policies.
When 95% of the time it's not an issue, there is a lack of recognition of when it is. And that often means a lot of firefighting, as often the spreadsheet is deeply embroiled in a process before those problems appear. And some of this is just bias from my position... I don't see 99% of spreadsheets, I only encounter the ones that became a problem.
This might all seem overworrying, but if you're handling legally protected data, it makes it much murkier to audit who has access to what data. A lot of small data breaches happen because somebody's computer got compromised and they had 200 spreadsheets saved on it. Involving spreadsheets means you can't be 100% certain who modified data in it.
And if a spreadsheet becomes part of a critical process, it becomes a bottleneck for streamlining. There was a process at work that generated a CSV dump from the ERP system. The accounting department would import the CSV to their spreadsheet, make their modifications from another system, and then upload the changed CSV to be re-imported to the database for a later processing job to be done.
This process was critical for payroll processing. If it failed for any reason, checks wouldn't go out on time.
Of course. I completely agree with what you're saying. But the problem isn't the tool, it's the people using them. Spreadsheets are a multi-tool; you can use it for just about everything, but they...
Of course. I completely agree with what you're saying. But the problem isn't the tool, it's the people using them. Spreadsheets are a multi-tool; you can use it for just about everything, but they have limitations that mean you probably shouldn't be relying on them over specialized tools to run a business.
I didn’t mention it explicitly, but power query is the big reason I can’t use libreoffice calc, Google sheets, or Apple numbers. Once you get a feel for it, power query is fantastic, as you say. I...
I didn’t mention it explicitly, but power query is the big reason I can’t use libreoffice calc, Google sheets, or Apple numbers. Once you get a feel for it, power query is fantastic, as you say. I don’t think there is any tool on the market as flexible and powerful as powerquery. At one point I tried to replicate a relatively simple PQ ETL logic into python, specifically pandas. Pandas did some things better, but it was much more clunky to set up. I wish there was a tool like powerquery that exported into pandas, parquet, or similarly flexible format without having to use excel.
And on top of all of that, powerquery can execute arbitrary python scripts. So it’s literally more powerful than all of python (okay that might be a bit hyperbolic, but it’s really powerful). My job for the last 3 years was almost entirely powerquery. It is one of the best parts of excel.
To Echo you and u/lp4ever55, anyone who uses excel regularly to manipulate data and hasn't imported data sources with Power Query should absolutely give it a shot and start experimenting with it....
To Echo you and u/lp4ever55, anyone who uses excel regularly to manipulate data and hasn't imported data sources with Power Query should absolutely give it a shot and start experimenting with it. It's extremely useful when you want to work with multiple data sources in the same workbook. I use it with CSVs fairly often so that I can look at and work with the CSV data in a work book, but then can very easily refresh my data source if the CSV file changes without having to worry about excel locking the file if I were to have the CSV open directly.
I will say when it comes to processing and restructuring data I strongly prefer working in Python, especially when working with data sets that come from multiple files. I've found doing things like grabbing data files with a name pattern (something like data_09--24.csv) from folders (and especially if they're arranged in subfolders) to be trivial in Python leverage it's standard libraries but basically impossible with Power Query.
I know power query supports using Python code to import and manipulate data, but what kills me is it's not portable. It seems silly I can't point it to a script that has the code, but instead I have to copy and paste the code into the box. It kills me because I can't write modular code I can update and easily reuse for multiple workflows. (Which if this has changed pardon the ignorance, it's been a year since I last tried it). I suspect your preference for Power Query over Python/Pandas vs my preference for the opposite likely stems from having different processing workflows. Fundamentally they're both really powerful tools though.
In my mind the dream is for Microsoft to add just a bit more Python integration to Power Query to have hybrid workflows and leverage the best of both of them.
Agreed. Powerquery is essential for csv data. Excel doesn’t natively support quoted commas in a csv file unless you import using powerquery. Although even powerquery will default to...
Agreed. Powerquery is essential for csv data. Excel doesn’t natively support quoted commas in a csv file unless you import using powerquery. Although even powerquery will default to QuoteStyle.None instead of QuoteStyle.Csv for some stupid reason.
Also you can import a range of documents using powerquery quite easily. It’s explicitly supported too, and not just a hack that technically works. But the user interface for doing it is so opaque that you pretty much have to know it exists before you can find it. You start by importing a folder data source. It gives you a table with file names, folder paths, other metadata, and the binary contents of the file. From here, you can do any manipulations you would like to select the files, for example string extraction to select a specific range of dates. Then if you “expand” the binary column field (I forget the actual name of the operation, but it’s one of the options at the top of the column), powerquery will do a bunch of stuff for you. It selects one of the files as a sample file. You fill in any mutations you want to that file (promoting the header row, changing data types, removing columns, etc). Powerquery applies those same functions to all of the targeted files and combines those separate outputs into a single query. Once you get the hang of it, it’s very simple to set up and quite powerful.
That being said, if you have a python setup that works for you, keep using that. The only advantage powerquery has over a skilled python dev is that it’s installed by default on almost all corporate computers. I am reasonably proficient in python, but not as experienced as it sounds like you are. And after I found typescript, I don’t really like writing python anymore. I would much rather run typescript, but it doesn’t have the same support for pandas and other useful libraries.
I think the abundance of libraries and big user base is a constant pull back to Python. And being able to leverage large language models to get some starter code has sped up my workflow quite a...
I think the abundance of libraries and big user base is a constant pull back to Python. And being able to leverage large language models to get some starter code has sped up my workflow quite a bit. I haven't tried getting help with power query yet from something like ChatGPT, but I wonder how well it would do.
What have you found is your draw to typescript? I've seen it mentioned here periodically but haven't had a chance to look into. Python was the first language to "click" for me after years of trying to learn programming, but as I've gotten proficient I've found I can make changes to other languages much easier and can recognize common structures to things. I've had to pick up Ruby for my graduate work but it's really similar to Python so that was pretty easy. Been curious to branch out a bit.
ChatGPT is pretty terrible at powerquery. It would often give me syntactically incorrect code and be unable to fix it. The most I could use it for was finding new PQ features. I would often need...
ChatGPT is pretty terrible at powerquery. It would often give me syntactically incorrect code and be unable to fix it. The most I could use it for was finding new PQ features. I would often need to look through Microsoft’s documentation and reimplement what I needed myself. It isn’t a great experience.
The main draw for me is the type system (very surprising 😜). First, there are a few other things I like, and then I’ll wax poetic about TS. I like the idea of pure functional programming. FP is obviously doable in almost any language, but it feels at home to me in JS. I really like chaining operators with “.” on the new line in JS. You can do the same in python if you wrap the entire thing in parentheses, but that feels slightly clunky. I like the ()=> lambda syntax better than the lambda() syntax. These all are very minor annoyances. These alone wouldn’t make me choose one language over another
Now for the type system. I am not experienced enough to weigh in on the fundamental question of static or dynamic type systems. What I can say is this: static type systems seem fine for mature projects that are mostly self contained. Dynamic types seem much better for getting a project started from scratch.
I work best when I can see something working quickly and make many small iterations from there. JS in general seems more suited to my programming approach. I have my editor set to save on every change, and the code is executed immediately on save. So as soon as it is syntactically valid, I get an output. I know immediately if something is right or wrong. I don’t need to go through the steps of compiling and executing like for c or c++. I don’t know if python has a similar watcher utility, but for JavaScript it is so essential that it is built into projects like bun, deno, and I think node is working on it too.
A dynamic type system also works well for my preference to have something simple working and iterate from there. I don’t really care in the beginning if some variable is a string, integer, object, array, etc. just run my code and see if it works. If my code doesn’t work, me fixing type annotations is a waste of time. Then when I have something working, I can go back and annotate types to make sure it’s reliable and correct. This is where typescript separates itself from python. Python does have function type annotations, but if I recall correctly, it does not give you errors if you pass an incorrect type. You can write a typescript program with absolutely zero type information by using the “any” type everywhere. The once you are ready to add types, you change one line in your tsconfig and fix all the errors. So TS can be a fully dynamic or fully static type system.
Furthermore, TS has excellent support for very complex types. Let’s say you have an api that returns json with two keys: objectType and payload. ObjectType is just a string, but payload is an entirely different object depending on the value of objectType. I don’t know if python’s type annotations can do that, but TS will handle that with ease. JavaScript in general also maps much better than python to JSON (to be expected since JSON came from JS). These two combined make working with JSON way easier in TS than Python. Even if you do it in python, you won’t be able to have your editor autocomplete accessors within a deeply nested object structure.
That turned into a long diatribe, but I hope it makes some sense. Modern typescript is a fantastic language.
Ruby chains operations similar to what you're describing which I have found is generally easier to think in as I type (where the order I'm thinking of the operations follows as I type). I agree...
Ruby chains operations similar to what you're describing which I have found is generally easier to think in as I type (where the order I'm thinking of the operations follows as I type). I agree that nesting operations in Python gets gross pretty quickly. That being said, I appreciate that it's a flexible enough language that a lot of libraries can follow dot chaining operations together (which pandas tends to do for the most part).
When it comes to the significance of types, that's a part of programming that I don't really appreciate yet. I really like being able to use a scripting style that's closer to natural language where objects regularly get mutated following my mental flow. So for example I might bring my data in with data = glob.glob(data_*\data.csv) where I want to bring in a list of file paths matching the file paths (please ignore any errors in my glob, I always have to Google the formatting), then iterate through each file in a for loop data_list.append(pandas.read_csv(file_path)), and then after convert data into a final aggregated dataframe with something like data = pandas.concat(data_list). That being said, as I understand when it comes to types, it would be better practice to have a separate data_paths object with my list of paths and then a data_df object with the final dataframe. It's just easier for me mentally writing something to reuse variable names, which I could probably mentally retrain, but it's a comfortable way for me to write. 🤷
That being said JavaScript feels like such a useful language to have in my back pocket. But it sounds like TypeScript is like a sibling language? Which if that's the case maybe it's best to just start there.
Typescript is a strict superset of JavaScript. If you remove the type annotations without changing anything else, you can put typescript code into a JavaScript compiler unmodified and it will work...
Typescript is a strict superset of JavaScript. If you remove the type annotations without changing anything else, you can put typescript code into a JavaScript compiler unmodified and it will work exactly the same. In fact, there is a proposal going through ECMA (they are the governing body for JS. In fact, JS is properly called ECMAScript) to have the standards allow interpreters to do exactly that (it wouldn’t check the types, it would just ignore the type annotations.
Typescript does two things. First, it “compiles” TS code into JavaScript. It can output valid JS for any flavor of JS you want. So you can program with all the latest JS language features, and TS will compile it back to the JS from internet explorer 6, if you want. This part doesn’t have to even check types if you don’t want it to.
The second thing it does is “compile” time type checking. It’s important to note that TS has zero runtime type checking. If you have a number variable in TS, and no type errors, it’s still possible at runtime for that variable to be changed to a string and break your program. TS helps prevent these errors, but doesn’t remove them entirely (depending on how you use it).
Those two aspects are very powerful, but they aren’t the heart of the programming language. When you program in typescript, you are really just writing JavaScript. You just also include some extra info that isn’t included in the final output. So yeah, TS is kinda like a sister language to JS. And yes, it’s a fantastic tool to have in your toolbox. The amount of programming resources that have been put into making JavaScript fast and reliable for browsers means it’s one of the fastest interpreted languages. I’ll always love python. It’s what got me to like programming. But JS is fantastic.
WRT Excel specifically, and not spreadsheets in general, we can thank Microsoft's manipulative tactics. Lotus 1-2-3 was way better in those early days, but Microsoft's repeated shady tactics let...
WRT Excel specifically, and not spreadsheets in general, we can thank Microsoft's manipulative tactics. Lotus 1-2-3 was way better in those early days, but Microsoft's repeated shady tactics let them overwhelm pretty much every competitor.
Look through the history of spreadsheet software, and you'll see that there were huge numbers of viable competitors in the 90s. It wasn't until Windows 3.11 hit the scene (courtesy of them tanking OS/2), that they started to gain momentum against Lotus Notes. They then leveraged donating software to education (windows, office, and server, naturally) to insure that future generations grew up with it.
Pair that with lax enforcement of piracy, as well as shady AF OEM deals, and that's all she wrote.
Besides that whole ball of wax (which I agree with you on); I'm surprised the article references excel only instead of spreadsheet programs in general. Outside of macros (which you should try to...
WRT Excel specifically, and not spreadsheets in general, we can thank Microsoft's manipulative tactics
Besides that whole ball of wax (which I agree with you on); I'm surprised the article references excel only instead of spreadsheet programs in general. Outside of macros (which you should try to avoid anyways) LibreOffice is basically a drop in replacement and I always forget about Google Sheets (I'm sure it's great as well).
I disagree that LibreOffice is a drop-in replacement. It's what I use at home, and it's fine for most stuff, but I still find myself missing Excel features I use heavily at work (like today,...
I disagree that LibreOffice is a drop-in replacement. It's what I use at home, and it's fine for most stuff, but I still find myself missing Excel features I use heavily at work (like today, "format as table").
I haven't used "format as table" so I looked it up and tried it in excel and LibreOffice Calc does open it with *mostly* as it was displayed in excel. I was going to type a longer comment here,...
I haven't used "format as table" so I looked it up and tried it in excel and LibreOffice Calc does open it with *mostly* as it was displayed in excel.
I was going to type a longer comment here, but I think it boils down to me using a pretty loose definition for what "drop in replacement" means.
For that specific example it's not how it displays, it's that in Excel the table is itself an object with useful properties like acting as a dynamic named range and auto-populating formulas and...
For that specific example it's not how it displays, it's that in Excel the table is itself an object with useful properties like acting as a dynamic named range and auto-populating formulas and formatting for new rows. I've not found any kind of equivalent to that in LibreOffice Calc.
I’m reminded of a dictum about requirements gathering from The Daily WTF to the effect that, no matter what your users say they want, what they really want is Excel. To echo what others have said...
I’m reminded of a dictum about requirements gathering from The Daily WTF to the effect that, no matter what your users say they want, what they really want is Excel.
To echo what others have said here, it really seems like Excel has a near-ideal interface. Everyone wants to use it for everything.
It's modularity to the extreme. The top comment as of writing my comment mentions it being referred to as "the second-best tool for everything" which does indicate people recognize modularity has...
It's modularity to the extreme. The top comment as of writing my comment mentions it being referred to as "the second-best tool for everything" which does indicate people recognize modularity has its flaws and thus not being the best tool for anything, but it makes sense why everyone wants to use it. Not only is it modularity to the extreme, it manages to keep it relatively simple considering taking anything to the extreme generally doesn't stay simple.
It's interesting for me to think about more purpose-built software that is more specific to a particular situation, because often times I just find myself wishing it was more modular. Even though its specificity is its advantage in some regards, it's also its Achilles heel at times. Mostly I'm thinking of software I encountered working for different businesses. It seems to be true with a lot of software, many times something starts out specific and is really good at the one or two things it does, but then people want more and the tool tries to become a multi-tool and then it often is lackluster at many things, and it seems to be really just because people want modularity like in Excel. In some cases people end up hating the software because it becomes bloated and unwieldy. Yet somehow Excel manages to thread this needle perfectly.
Excel was a tool that everyone keeps saying it is one of the most important tools in your life…. I still haven’t had a significant use for it after all these years.
Excel was a tool that everyone keeps saying it is one of the most important tools in your life…. I still haven’t had a significant use for it after all these years.
I'll be honest, the article kind of felt lacklustre in expectations. Some suggestions:
Addendeum: Two of my favorite books from the past year was reading, "The Filing Cabinet: A Vertical History of Information" by Craig Robertson, or one that may have been even better, "The Phone Book: The Curious History of the Book That Everyone Uses But No One Reads" By Ammon Shea. Both are some of those 'boring' subjects I love to read about in intense detail.
I am surprised there isn't one about the spreadsheet, going all the way back to paper records, to the creation, the legal and corporate wars of the 80s-00s, and then some. Maybe something I should look into writing, been looking for a good topic - but if anyone has any books on the subject, let me know! My cursory search is coming up nil.
My personal why Excel is so enduring:
Convergence of the above: It's so bland, it's perfect. You can just open it, and immediately start using it. Some people use it for data, others go so far and use it for knitting patterns.
I use excel for a lot of more typical spreadsheety uses, but similar to the knitting patterns, one of the more creative applications Ive done is garden planning.
It's great for It's versitility. On one sheet I can use to colourcode a garden plan (1 cell = 1 sq ft), another I can list and calculate various dates (seeding, sewing, tranplanting, harvest...), on another I can figure out how much of which plant to buy, etc.
I've heard Excel referred to as "the second-best tool for everything", and that's not terribly inaccurate in my experience. I don't know what engineering would look like without it.
I learned so much using excel for school, university, work.... I mostly use sheets now because my work is based on Google Workspace, but I have no preference for a spreadsheet tool as long as I can do what I need to do, and it's fairly easy to write a script or macro as needed.
This is a fluff piece with no real content. All it really says is “these three random people use and like excel”. I don’t really have anything to say on this article.
Excel is fantastic and I love it! I also despise it with every fiber of my being. I truly think that nobody can ever love excel without also hating it, unless they don’t really use it very much.
Spreadsheets are central to computing. After all, basically all data in the world lives inside a database at some point. A database is nothing more than a spreadsheet without a front end. Fundamentally there is nothing that can be done in a database that couldn’t also be done in a basic spreadsheet program. Postgres will always be better for large data sets and transactional data, and duckdb will always be better at handling massive amounts of data, but they are both fundamentally identical to a spreadsheet.
Having access to a spreadsheet is a huge productivity boost. Once you get some experience, a spreadsheet starts looking like a fantastic hammer for every nail you come across. Excel in particular has so much cruft built in to solve all sorts of issues people have. That, I think, is why excel is the best spreadsheet program. I’ve tried libreoffice calc, and I keep coming back to apple numbers and wanting to love it, but I always end up back on excel. When you hit a limit in one of the other office programs, you are at the limit. You will never be able to do what you want to do. Excel doesn’t really have limits. When you hit what looks like a limit, you can find some ancient forum post with some magic formula, or some VBA script and get your task done.
There are massive problems with spreadsheets though:
If a spreadsheet is only ever used by one person, it works great. As soon as that spreadsheet gets shared and gets turned into a small application, things start getting hairy, fast.
People who love spreadsheets often never think about these aspects of it, until one day its discovered some intern wiped half the spreadsheet by accident and the backups aged out.
Don't get me wrong, I use spreadsheets daily. It's how I track my budget. But it's mostly used as a glorified calculator.
Some of these problems get ironed out by tying the spreadsheet to a database backend, but the coding aspects can definitely get accidentally borked very easily, especially in a multi-user environment.
Google Sheets solves a lot of those issues, but introduced a number of others, like depending on third party tools for things that are native to Excel.
This doesn't matter (and is potentially kind of an asset) when human beings use it as computerized graph paper, but it is an enormous fucking pain in the ass when they then decide they need their
abominationdata liberated and hand it off to me to write software to process, which always happens eventually for any sufficiently long-lived spreadsheet.Some (almost certainly not exhaustive, I've blocked plenty of trauma from my memories) examples I've personally run into:
All of those are why I hate excel. Also poor options to have multiple users editing the same document. In theory multi user editing works with OneDrive/sharepoint. In practice, it breaks almost every single time I used it. It might be okay for very simple spreadsheets, but for anything complex it breaks so quickly.
But I have one nitpick/question about your issue list. Compatibility issues. I will admit that I didn’t have much need to open old excel files, but my impression was that excel compatibility was legendary. The latest excel 365 has native support for the excel file format used back in 2003. I am kinda tempted to build out a spreadsheet in the copy of excel I have for my Mac Plus and see if it can open on my modern copy. Heck, excel still has known bugs because fixing them would break old spreadsheets (the 1900 leap year bug was apparently copied from lotus 123 to maintain compatibility). Excel even has some examples of forward compatibility I found. They made a LAMBDA function that, combined with the name manager, allows you to define custom functions without using VBA. I wanted to use this for sheets for my job, so I had IT install the version (I think 22h2 or 23h1) before it was deployed to the entire company. I was fully expecting sheets made with lambdas to break when my coworkers with old versions opened them. But they could open them without error, and the calculations still worked perfectly. They couldn’t see the nice lambda formatting, but as long as they didn’t change my formulas, it worked on their version of excel that was released before lambdas existed. Personally I have never seen excel intercompatibility issues.
They do have a pretty heavy-handed warning.
Maybe it's just my trauma from desktop support circa 2007 and having to deal with heterogenous environments with files dating back to the early 90s. And database connectivity gets messy fast, especially when versions change.
That and the subtle periodic breakage of 3rd-party tooling which is intended to mitigate some of these problems. This was way worse in those .xls days though.
You'll find that the majority of those problems are either unimportant to a large number of users or are otherwise acheivable to a good enough extent. For access control, cells can be locked behind a password. Some spreadsheet formats do allow for versioning. Most offer some sort of comments system to use for auditing functionality. Backup is done with an external program. And compatability isn't really as much of an issue as you'd think if for no reason other than most people aren't familliar enough with the more advanced functionality to worry about things breaking.
If anything, I think the problem with spreadsheets is that a lot of the most powerful things are not obvious and they're things that people wouldn't even know how to ask how to do. Go ahead, ask the average person off the street what a pivot table is.
You're right, for the majority. And that's kind of why it's a problem. None of those features get used. Even if they did, you can't gate access to a spreadsheet with SSO in a reliable way. You can't gate access permissions based on department or data security policies.
When 95% of the time it's not an issue, there is a lack of recognition of when it is. And that often means a lot of firefighting, as often the spreadsheet is deeply embroiled in a process before those problems appear. And some of this is just bias from my position... I don't see 99% of spreadsheets, I only encounter the ones that became a problem.
This might all seem overworrying, but if you're handling legally protected data, it makes it much murkier to audit who has access to what data. A lot of small data breaches happen because somebody's computer got compromised and they had 200 spreadsheets saved on it. Involving spreadsheets means you can't be 100% certain who modified data in it.
And if a spreadsheet becomes part of a critical process, it becomes a bottleneck for streamlining. There was a process at work that generated a CSV dump from the ERP system. The accounting department would import the CSV to their spreadsheet, make their modifications from another system, and then upload the changed CSV to be re-imported to the database for a later processing job to be done.
This process was critical for payroll processing. If it failed for any reason, checks wouldn't go out on time.
Of course. I completely agree with what you're saying. But the problem isn't the tool, it's the people using them. Spreadsheets are a multi-tool; you can use it for just about everything, but they have limitations that mean you probably shouldn't be relying on them over specialized tools to run a business.
Don't forget the built in Power Query.. that thing is awesome!
I didn’t mention it explicitly, but power query is the big reason I can’t use libreoffice calc, Google sheets, or Apple numbers. Once you get a feel for it, power query is fantastic, as you say. I don’t think there is any tool on the market as flexible and powerful as powerquery. At one point I tried to replicate a relatively simple PQ ETL logic into python, specifically pandas. Pandas did some things better, but it was much more clunky to set up. I wish there was a tool like powerquery that exported into pandas, parquet, or similarly flexible format without having to use excel.
And on top of all of that, powerquery can execute arbitrary python scripts. So it’s literally more powerful than all of python (okay that might be a bit hyperbolic, but it’s really powerful). My job for the last 3 years was almost entirely powerquery. It is one of the best parts of excel.
To Echo you and u/lp4ever55, anyone who uses excel regularly to manipulate data and hasn't imported data sources with Power Query should absolutely give it a shot and start experimenting with it. It's extremely useful when you want to work with multiple data sources in the same workbook. I use it with CSVs fairly often so that I can look at and work with the CSV data in a work book, but then can very easily refresh my data source if the CSV file changes without having to worry about excel locking the file if I were to have the CSV open directly.
I will say when it comes to processing and restructuring data I strongly prefer working in Python, especially when working with data sets that come from multiple files. I've found doing things like grabbing data files with a name pattern (something like data_09--24.csv) from folders (and especially if they're arranged in subfolders) to be trivial in Python leverage it's standard libraries but basically impossible with Power Query.
I know power query supports using Python code to import and manipulate data, but what kills me is it's not portable. It seems silly I can't point it to a script that has the code, but instead I have to copy and paste the code into the box. It kills me because I can't write modular code I can update and easily reuse for multiple workflows. (Which if this has changed pardon the ignorance, it's been a year since I last tried it). I suspect your preference for Power Query over Python/Pandas vs my preference for the opposite likely stems from having different processing workflows. Fundamentally they're both really powerful tools though.
In my mind the dream is for Microsoft to add just a bit more Python integration to Power Query to have hybrid workflows and leverage the best of both of them.
Agreed. Powerquery is essential for csv data. Excel doesn’t natively support quoted commas in a csv file unless you import using powerquery. Although even powerquery will default to QuoteStyle.None instead of QuoteStyle.Csv for some stupid reason.
Also you can import a range of documents using powerquery quite easily. It’s explicitly supported too, and not just a hack that technically works. But the user interface for doing it is so opaque that you pretty much have to know it exists before you can find it. You start by importing a folder data source. It gives you a table with file names, folder paths, other metadata, and the binary contents of the file. From here, you can do any manipulations you would like to select the files, for example string extraction to select a specific range of dates. Then if you “expand” the binary column field (I forget the actual name of the operation, but it’s one of the options at the top of the column), powerquery will do a bunch of stuff for you. It selects one of the files as a sample file. You fill in any mutations you want to that file (promoting the header row, changing data types, removing columns, etc). Powerquery applies those same functions to all of the targeted files and combines those separate outputs into a single query. Once you get the hang of it, it’s very simple to set up and quite powerful.
That being said, if you have a python setup that works for you, keep using that. The only advantage powerquery has over a skilled python dev is that it’s installed by default on almost all corporate computers. I am reasonably proficient in python, but not as experienced as it sounds like you are. And after I found typescript, I don’t really like writing python anymore. I would much rather run typescript, but it doesn’t have the same support for pandas and other useful libraries.
I think the abundance of libraries and big user base is a constant pull back to Python. And being able to leverage large language models to get some starter code has sped up my workflow quite a bit. I haven't tried getting help with power query yet from something like ChatGPT, but I wonder how well it would do.
What have you found is your draw to typescript? I've seen it mentioned here periodically but haven't had a chance to look into. Python was the first language to "click" for me after years of trying to learn programming, but as I've gotten proficient I've found I can make changes to other languages much easier and can recognize common structures to things. I've had to pick up Ruby for my graduate work but it's really similar to Python so that was pretty easy. Been curious to branch out a bit.
ChatGPT is pretty terrible at powerquery. It would often give me syntactically incorrect code and be unable to fix it. The most I could use it for was finding new PQ features. I would often need to look through Microsoft’s documentation and reimplement what I needed myself. It isn’t a great experience.
The main draw for me is the type system (very surprising 😜). First, there are a few other things I like, and then I’ll wax poetic about TS. I like the idea of pure functional programming. FP is obviously doable in almost any language, but it feels at home to me in JS. I really like chaining operators with “.” on the new line in JS. You can do the same in python if you wrap the entire thing in parentheses, but that feels slightly clunky. I like the ()=> lambda syntax better than the lambda() syntax. These all are very minor annoyances. These alone wouldn’t make me choose one language over another
Now for the type system. I am not experienced enough to weigh in on the fundamental question of static or dynamic type systems. What I can say is this: static type systems seem fine for mature projects that are mostly self contained. Dynamic types seem much better for getting a project started from scratch.
I work best when I can see something working quickly and make many small iterations from there. JS in general seems more suited to my programming approach. I have my editor set to save on every change, and the code is executed immediately on save. So as soon as it is syntactically valid, I get an output. I know immediately if something is right or wrong. I don’t need to go through the steps of compiling and executing like for c or c++. I don’t know if python has a similar watcher utility, but for JavaScript it is so essential that it is built into projects like bun, deno, and I think node is working on it too.
A dynamic type system also works well for my preference to have something simple working and iterate from there. I don’t really care in the beginning if some variable is a string, integer, object, array, etc. just run my code and see if it works. If my code doesn’t work, me fixing type annotations is a waste of time. Then when I have something working, I can go back and annotate types to make sure it’s reliable and correct. This is where typescript separates itself from python. Python does have function type annotations, but if I recall correctly, it does not give you errors if you pass an incorrect type. You can write a typescript program with absolutely zero type information by using the “any” type everywhere. The once you are ready to add types, you change one line in your tsconfig and fix all the errors. So TS can be a fully dynamic or fully static type system.
Furthermore, TS has excellent support for very complex types. Let’s say you have an api that returns json with two keys: objectType and payload. ObjectType is just a string, but payload is an entirely different object depending on the value of objectType. I don’t know if python’s type annotations can do that, but TS will handle that with ease. JavaScript in general also maps much better than python to JSON (to be expected since JSON came from JS). These two combined make working with JSON way easier in TS than Python. Even if you do it in python, you won’t be able to have your editor autocomplete accessors within a deeply nested object structure.
That turned into a long diatribe, but I hope it makes some sense. Modern typescript is a fantastic language.
Ruby chains operations similar to what you're describing which I have found is generally easier to think in as I type (where the order I'm thinking of the operations follows as I type). I agree that nesting operations in Python gets gross pretty quickly. That being said, I appreciate that it's a flexible enough language that a lot of libraries can follow dot chaining operations together (which pandas tends to do for the most part).
When it comes to the significance of types, that's a part of programming that I don't really appreciate yet. I really like being able to use a scripting style that's closer to natural language where objects regularly get mutated following my mental flow. So for example I might bring my data in with
data = glob.glob(data_*\data.csv)where I want to bring in a list of file paths matching the file paths (please ignore any errors in my glob, I always have to Google the formatting), then iterate through each file in a for loopdata_list.append(pandas.read_csv(file_path)), and then after convert data into a final aggregated dataframe with something likedata = pandas.concat(data_list). That being said, as I understand when it comes to types, it would be better practice to have a separate data_paths object with my list of paths and then a data_df object with the final dataframe. It's just easier for me mentally writing something to reuse variable names, which I could probably mentally retrain, but it's a comfortable way for me to write. 🤷That being said JavaScript feels like such a useful language to have in my back pocket. But it sounds like TypeScript is like a sibling language? Which if that's the case maybe it's best to just start there.
Typescript is a strict superset of JavaScript. If you remove the type annotations without changing anything else, you can put typescript code into a JavaScript compiler unmodified and it will work exactly the same. In fact, there is a proposal going through ECMA (they are the governing body for JS. In fact, JS is properly called ECMAScript) to have the standards allow interpreters to do exactly that (it wouldn’t check the types, it would just ignore the type annotations.
Typescript does two things. First, it “compiles” TS code into JavaScript. It can output valid JS for any flavor of JS you want. So you can program with all the latest JS language features, and TS will compile it back to the JS from internet explorer 6, if you want. This part doesn’t have to even check types if you don’t want it to.
The second thing it does is “compile” time type checking. It’s important to note that TS has zero runtime type checking. If you have a number variable in TS, and no type errors, it’s still possible at runtime for that variable to be changed to a string and break your program. TS helps prevent these errors, but doesn’t remove them entirely (depending on how you use it).
Those two aspects are very powerful, but they aren’t the heart of the programming language. When you program in typescript, you are really just writing JavaScript. You just also include some extra info that isn’t included in the final output. So yeah, TS is kinda like a sister language to JS. And yes, it’s a fantastic tool to have in your toolbox. The amount of programming resources that have been put into making JavaScript fast and reliable for browsers means it’s one of the fastest interpreted languages. I’ll always love python. It’s what got me to like programming. But JS is fantastic.
WRT Excel specifically, and not spreadsheets in general, we can thank Microsoft's manipulative tactics. Lotus 1-2-3 was way better in those early days, but Microsoft's repeated shady tactics let them overwhelm pretty much every competitor.
Look through the history of spreadsheet software, and you'll see that there were huge numbers of viable competitors in the 90s. It wasn't until Windows 3.11 hit the scene (courtesy of them tanking OS/2), that they started to gain momentum against Lotus Notes. They then leveraged donating software to education (windows, office, and server, naturally) to insure that future generations grew up with it.
Pair that with lax enforcement of piracy, as well as shady AF OEM deals, and that's all she wrote.
Besides that whole ball of wax (which I agree with you on); I'm surprised the article references excel only instead of spreadsheet programs in general. Outside of macros (which you should try to avoid anyways) LibreOffice is basically a drop in replacement and I always forget about Google Sheets (I'm sure it's great as well).
I disagree that LibreOffice is a drop-in replacement. It's what I use at home, and it's fine for most stuff, but I still find myself missing Excel features I use heavily at work (like today, "format as table").
I haven't used "format as table" so I looked it up and tried it in excel and LibreOffice Calc does open it with *mostly* as it was displayed in excel.
I was going to type a longer comment here, but I think it boils down to me using a pretty loose definition for what "drop in replacement" means.
For that specific example it's not how it displays, it's that in Excel the table is itself an object with useful properties like acting as a dynamic named range and auto-populating formulas and formatting for new rows. I've not found any kind of equivalent to that in LibreOffice Calc.
I’m reminded of a dictum about requirements gathering from The Daily WTF to the effect that, no matter what your users say they want, what they really want is Excel.
To echo what others have said here, it really seems like Excel has a near-ideal interface. Everyone wants to use it for everything.
It's modularity to the extreme. The top comment as of writing my comment mentions it being referred to as "the second-best tool for everything" which does indicate people recognize modularity has its flaws and thus not being the best tool for anything, but it makes sense why everyone wants to use it. Not only is it modularity to the extreme, it manages to keep it relatively simple considering taking anything to the extreme generally doesn't stay simple.
It's interesting for me to think about more purpose-built software that is more specific to a particular situation, because often times I just find myself wishing it was more modular. Even though its specificity is its advantage in some regards, it's also its Achilles heel at times. Mostly I'm thinking of software I encountered working for different businesses. It seems to be true with a lot of software, many times something starts out specific and is really good at the one or two things it does, but then people want more and the tool tries to become a multi-tool and then it often is lackluster at many things, and it seems to be really just because people want modularity like in Excel. In some cases people end up hating the software because it becomes bloated and unwieldy. Yet somehow Excel manages to thread this needle perfectly.
Excel was a tool that everyone keeps saying it is one of the most important tools in your life…. I still haven’t had a significant use for it after all these years.
It's hard to say anything about that without more context. What's your line of work?