People are all talking about how it's useful for quick stuff with datasets, but it goes far beyond that. It's useful for quick anything you might want a small database for. Imagination is the limit.
For example, if any of you play D&D online, you might be familiar with dndbeyond's character sheets. They're a fantastic way to onboard new players who might not have the inclination to spend hours with the rule books before they even start playing. It does all the calculations for you and gives you some buttons like "roll athletics" and doesn't let you add more spells than your character can have with their stats.
I recently persuaded some friends to give FATE a try and built analogous push-button character sheets with google sheets [0]. It was quick and simple. With conditional formatting, you highlight bad states (rules say you can't have more of X than Y!). With the script editor, you can add full on buttons for dice rolls and other state changes with whatever logic you want (anything you can code up!). Checkboxes are obvious but super useful. And the transparency of the calculations is helpful for teaching people the system (this stat is "min(A4, B1+C5)").
Without google sheets, it would be a serious endeavor to build a stateful, database backed, live collaborative GUI that can be added to and customized on the fly by my users. With google sheets, it was a quick fun afternoon hack. Excel/google sheets is an amazing piece of technology.
We started a company with the core premise that spreadsheets will never die [0]. Spreadsheets are so good at the most casual data viewing and exploring tasks to creating complex financial models. They are also the de-facto choice when you have data (not big data) from multiple sources that you need to "join". We tend to underestimate the beauty of this tool which can be used productively at all points of the skill spectrum. Everyone feels at ease in the familiar territory of a spreadsheet, which is what makes it ubiquitous and basically impossible to kill.
If spreadsheets were two-way connected with your core systems like SaaS tools, DBs, Slack, etc then you could represent serious business logic and actions without being a programmer. It is the best platform to build a "no code" tool for non-programmers.
That's awesome, Navneet! I have heard from big hedge fund managers, and seen first hand working at "cutting edge" 401K fintech companies that spreadsheets are the backoffice workhorse in many many companies doing serious business and handling serious data.
I am curious about one aspect though: Debugging spreadsheets is seriously hard. How do you help customers verify their spreadsheet has the right things they are looking for, avoiding regressions due to a random change by some inexperienced person, etc.?
Also, at what point do you see companies move from spreadsheets to simply hiring developers to do what they want? It seems like beyond a point, spreadsheets can get in the way, and the company has enough resources to hire a team to build custom internal tools.
Yeah spreadsheets are ubiquitous workhorses -- small companies to large, old to new, across geographies ...
Spreadsheets have many problems. We are going after their "connectivity" to the rest of the company systems. Hidden errors / debugging is definitely another big problem which we are only tangentially solving. If your data is imported into the sheet through Coefficient (instead of a copy-paste of a downloaded CSV), then you can always trace the lineage/timestamps/etc all the way to source.
As for hiring developers, the truth is that day-to-day business needs grow faster than you can hire devs. So yes, at a certain point companies move some workflows to dev-built tooling or specialized SaaS tools, but their bucket of unhandled workflows still grows larger every day. That is why you can't kill spreadsheets.
Debugging can be hard. That's often because what they're used for is done very fast. So, there's a business expert reasonably IT literature that has a powerful tool with a built-in IDE that doesn't take approvals or hiring requisitions or even budget and a problem's solved within a week on what otherwise would be a month (being kind).
The problem starts when whatever frankensheetvbasharepointdb is now an engrained part of a workflow, excelbizdev has disappeared, and 'maintenance' falls to nonexcelwhizz, or perhaps worse, a dev team that lacks much legacy business understanding to figure out why the particular implementation was done, screws up understanding, and creates something worse.
Spreadsheets are everywhere. Heck our local power company uses a complex spreadsheet for load prediction. It includes holidays, football events, historic weather and weather predictions. Saves them a bundle.
I'm running a game of D&D and needed an accessible character sheet that works from a phone with good support for pinch-to-zoom.
Tried fillable PDFs and a bunch of online stuff. None of it worked well. The spreadsheet fields' font sizes were all weird, and even if you manually correct them it would reset on every edit. There were some promising web-based options described as "responsive character sheet", but they tended to fall apart at large text sizes.
Instead of trying to shove an 8.5x11 paper layout into a phone, it just groups stuff into tabs that make more sense anyway. And if you were completely blind I bet it's still easy to navigate with VoiceOver.
We're using Numbers because it's what we both have, but I think Excel should work similarly.
If anyone's reading this from the Google Docs team, please take another look at Sheets' pinch-to-zoom behavior. That was the first place I ended up when I went looking for character sheet spreadsheets online, and it was the first one I ruled out because of how shitty the experience was on mobile.
I think every IT professional should at some point work for a non-IT company (where hopefully they are tech-savvy enough to be using spreadsheets). It's amazing to see what people will use it for (answer: everything).
I find with excel it takes just as much time to set up a spreadsheet to do what I want as it takes for me to do the same in R, usually a lot more time with Excel. Both pieces of software have learning curves, just to me R is the better tool for the job for working with tabular data. Excel forces you to hardcode your fomulas and ultimately adds a lot of cruft and time wasted, compared to R which is much more modular. That "min(A4, B1+C5)" is liable to break if your spreadsheet changes. R functions on the other hand are pretty well documented, and you could do anything you want in R after following a tutorial for a couple hours. Instead of having to hard code a position, you can refer to it relatively or by some unique identifier, so your calculations still work no matter how your underlying spreadsheet changes or is shuffled around (and familiar formulae like sum and min and max are there by the same names). It's way easier to do statistical tests and plot data consistently in R as well. Oh, and you can export to .csv or .xlsx from R if you'd like of course.
2. How does the UX for your R solution to the "DND Character Generation" problem compare to the screenshot from grandparent comment, for users not familiar with either R or Google Sheets?
Doesn't grandparent also know how to program? At this point we're just asking what threshold of UX either programmer cares about. I'm sure either could whip up a desirable UI in either execution.
I mean, I work with laymen that use Excel to encode multimedia content state machines and its not pretty (dreadful bespoke schema with all the caveats you can imagine) but it satisfies their need
Grandparent here. Yeah I know how to program, but the point here was to make my players a simple interactive GUI that saves its data somewhere we can all access together (e.g. the cloud). Doing that in R would be well beyond my skillset, even as an R user. Doing it with a web stack would be more appropriate, but even then it would have been a lot more work and I can't think how I'd do it without spinning up a server and setting up some http endpoints.
> That "min(A4, B1+C5)" is liable to break if your spreadsheet changes.
I don't usually have that problem. Inserting or deleting rows or columns around the cells doesn't break these formulas. Only changing what type of information a cell contains would. Does this happen often for you?
Cell references in Excel are not hard-coded; they are automatically updated if you modify the spreadsheet by deleting or inserting other cells, and unless you use the $ before the col or row they automatically increment/adjust when pasted into another cell.
And you can just name a cell or range if you want to use a variable name to refer to some data in a pivot table or formula.
At no point do you talk about UI here -- for a character sheet (or most simple things), I want to control layout to some degree, let users edit some numbers, and see others automatically update. Obviously I could have a file full of constants and a bunch of print statements at the end, but is there anything nicer / more dynamic?
> I find with excel it takes just as much time to set up a spreadsheet to do what I want as it takes for me to do the same in R, usually a lot more time with Excel.
I mean, this is I reach for Pandas over Excel, but most people would be infinitely more comfortable with spreadsheets than specialized tools. Spreadsheets also happen to be useful enough for almost everybody.
I hadn't used Excel in years and now that I do, there's some things about it that drive me nuts:
- It forgets what you've copied to clipboard. Copy something. Insert another row so that there's space for it. Paste. Nothing happens. Huh? It lost my copy. It does this for a large number of operations and it drives me crazy. I've never seen any other program do this.
- You can't open two spreadsheets of the same name. This is because spreadsheet formulas can refer to cells in other tables. But I don't use that feature - can't I just open the second spreadsheet with a warning that this feature won't apply to it?
- (This one applies to too many pieces of Microsoft technology.) You can't use common keyboard shortcuts properly. Ctrl-backspace deletes a word in any useful text box. Not in formula editing in excel. And ctrl+delete deletes the rest of the line instead of just the next word. Why?
> - It forgets what you've copied to clipboard. Copy something. Insert another row so that there's space for it. Paste. Nothing happens. Huh? It lost my copy. It does this for a large number of operations and it drives me crazy. I've never seen any other program do this.
Try 'WindowsKey + V'.
It'll bring up a list of your previous copies. Not as easy as 'Ctrl+V', of course, but it does save a bit of time. And yes I agree, Excel's Alzheimer's is quite annoying.
One of those things that I wouldn’t google myself as it’s a minor annoyance and yet when I see a tip like this it definitely makes me feel good about the time I spend on HN :D
The thing about the clipboard has been around since the beginning of Excel. I thought I had read an article about it by Joel Spolsky years ago but all I could find now was a quote by Joel answering a question on a forum. A superuser comment quoted Joel as an answer [1]. The archive link for the original discussion is here [2].
- Excel constantly wants to change cell contents, in the name of improving formatting. For example, paste a table with something that excel thinks are dates, it will convert it to dates, and show the data in some format. Formatting it back to text will give you the #days since epoch instead of the original text.
- Excel hates text cells containing numbers. It whines about it all the time and eagerly changes the data to what it thinks it should be.
- Excel doesnt get it if a sheet contains a data table with consistent formatting. Just recognize it and store it internally as a small Infile DB. Often, an Altertx table will blow up 100-fold when exported to Excel.
> Excel constantly wants to change cell contents, in the name of improving formatting. For example, paste a table with something that excel thinks are dates, it will convert it to dates, and show the data in some format. Formatting it back to text will give you the #days since epoch instead of the original text.
This is definitely annoying behavior, but you do know that if you format the cell as text prior to pasting the data in it will keep it as text, right?
> This is definitely annoying behavior, but you do know that if you format the cell as text prior to pasting the data in it will keep it as text, right?
This only works sometimes, and I have no idea when or why.
The most reliable way I've found is to copy more than one column and use the text importer thing, where you can specifically mark columns as being text.
Some times yes, other times Excel will replace the format you set with what it guesses from the data. I have no idea what triggers each behavior.
What's more interesting is reading those dates by the COM interface. Depending on how the user input the data, you'll get formated dates as text or seconds since the epoch as number.
I love Excel and it's one program that's always open on my computer. But the thing that still kills me after all these years is that every open Excel process shares the same undo stack.
Say you have have Excel Process A and Process B, and you make an edit in A, then an edit in B, then an edit in A again. If you try to undo the edit in B, it will instead undo the edit you did in A. Infuriating.
You can open separate Excel instances by holding ALT when starting it up (second time onwards). Not quite the same thing as you want, but think the undo behaviour you describe makes sense if you're working in two sheets that are linked in some way.
Can you elaborate on this? It sounds like it would solve my "I can't open two files with the same name" problem too, but I hold down ALT and double-click my second file in explorer and I get the "Properties" dialogue for that file. If I instead hold ALT and click on Excel itself, it opens a new window but it still seems to be linked to the same process (and gives the same error when trying to open the second file of the same name).
This should work on windows - I'm not sure about Macs: you press and hold the ALT key, then right-click the Excel icon in the Windows taskbar and click the Excel icon above the taskbar, but keep holding ALT down. It should pop up a window saying "do you want to start a new instance of Excel". You can then open your (same named) file in the new instance.
The clipboard being cleared when you do some not-paste action is pretty annoying. I assume it is this way though because if you alter the sheet in some way which intersects with the cells being copied paste might not know what to do. Like if you insert rows, or change a value, the thing you copied might have changed. It might not have but it would be more confusing I think to have Excel make than analysis that it changed in some meaningful way then clear the clipboard sometimes than to just always clear the clipboard.
Aren’t things that could change always defined by a formula? You already have two paste operations — by value or by function. It seems to me the reasonable thing is to simply follow through: if you paste by value, you paste whatever it was at the time of copy — if you paste by formula, you paste the formula... and whatever computed result.
Pasting the possibly modified value is, IMO, always undesirable; so you can ignore the possibility altogether
I think if Paste Values pastes something that is no longer on the screen that is weird. How long do you wait to clear the clipboard if that's the case? What if I copy some cells, then change everything about my workbook (changed sheet names, changed named range names, refreshed linked data, inserted rows in the middle of my copied range...), then paste, do you still expect it to paste the original values? Do you expect that most users understand what's about to happen?
I think the whole question is avoided by clearing the clipboard earlier than might sometimes be desirable.
Paste Values should paste the values that it had at the time of copying, period. Other programs behave thusly. If I'm making an image in Inkscape, I copy a portion, delete it and then paste it, I get the value I copied even though it had since been deleted. Same as if I edited it after copying, like changing a color. Excel should mimic that.
So if the underlying value changes enough that pasting anything but the values doesn't make sense then what's the UI here? Ctrl+C does nothing but Ctrl+Shift+C gives you the option to paste values with the inappropriate ones greyed out? Does the dotted border on the copied text go away because ctrl+C isn't going to work or does it stay because some portions of paste special can still work?
I think the scenario for pasting formula is the same as for pasting values: paste what was there at the time of copying. Is there a situation where this doesn't make sense? I'd be fine with it being kinda-wrong in situations like the original cells referred to another cell but now there's been a new row inserted and those cells are elsewhere and the clipboard hasn't updated. That's what I would expect a copy to do. Excel allows references to cells in other spreadsheets and if you edit the other spreadsheet, then the references in your first spreadsheet don't update to reflect that, so the clipboard would just act like another spreadsheet.
Edit: the better way to say this is that "copy (do something) paste" should always act the same as "copy, paste on a new blank spreadsheet, (do something) and then copy and paste that onto the original spreadsheet".
I'm not sure I am; the current behavior is almost always surprising and unexpected. You effectively need to keep a list in your head of everything that will clear your clipboard, which is really a list of everything that could ever cause problems, more often than not a flag that is entirely unrelated to your actual intent/action.
You might as well have it clear the clipboard if your next action isn't immediately paste and be done with it. Just don't bother to set the expectation that pasting will work like elsewhere.
The dotted border is kind of the problem. It represents an active connection between the current spreadsheet and what was copied, and that connection shouldn’t exist.
When implemented normally, ‘copy’ puts a copy of the selection on the clipboard; ‘paste’ takes whatever was on the clipboard and puts it at the current location.
Once you copy, it shouldn’t matter what you do on the spreadsheet, because you’re pasting from the clipboard.
Normal behaviour can be emulated by copy+pasting into a blank spreadsheet when you want to copy, and then copy+pasting from there when you want to paste.
But then you’ve just turned that spreadsheet into what the clipboard is supposed to be.
The one that drives me insane is that while editing a cell formula, that using the arrow keys move cells (and update [read: wreck]) the formula rather than moving the cursor within the formula you're editing.
Just hit F2 while editing a formula to toggle between Edit and Enter modes, one of which will behave as you expect. The other mode, which you hate, is very useful when you want to add references to other cells into your formula
> is very useful when you want to add references to other cells into your formula
I really wish the default is the other way around. When I do want to add other cells, more than half the time there is a better way than using arrow keys.
Yes, the edit mode (using arrows to create formulas) is actually something I really find convenient.
To me it is a feature, not a bug. But yeah, F2 is fairly easy to use once you get used to it. Its like using VIM, the shortcuts seem annoying to the uninitiated, but once you get understand how everything works, the power can really speed things up and you appreciate it.
If something is driving you nuts, ask on a forum, because excel is EXTREMELY flexible and used in some pretty high volume environments and the original developers really paid attention to user input (for a long while that stopped).
In many cases you are just not fully up to speed with the features in windows / excel.
I haven't used Excel heavily in at least 5 years. Google Sheets is much better in most ways, especially sharing and collaboration.
Google Sheets has a killer feature that few know about: you can attach Apps Script (JavaScript) to a sheet [1] including a "fetch" function to make API calls [2].
While Excel has a show-stopping preposterously ridiculous behavior: auto-converting long number strings to "scientific notation" (and even data loss rounding!!) [3].
Google sheets also has some large limitations and doesn’t contain anywhere near the feature richness of Excel, so it’s a little tit for tat.
For Power Users of excel, google sheets misses the following:
* Inability to import large datasets - number of cells limit is much smaller and there is no ability to support larger datasets (eg powerquery in excel). This means while sheets can typically handle 100k rows (with 10 columns), excel can handle well over 10 million (with hundreds of columns).
* No dynamic array formulas - in google sheets they are all “drag down” while in excel formulas can be created which expand/shrink according to the data
* No ability to handle relationships and measures - Excel has the capability to define relationships between data similar to a relational DB allowing for more powerful querying.
* You can’t send a google sheet on email - important for big companies!
In terms of a JavaScript API - excel has one for their web app (also called app script!), it’s just not supported on their desktop app yet (although this is being planned).
Because it’s easy, convenient and an accepted standard in business. Many orgs also have policies that disallow direct sharing outside the org, or are blocked from using google docs, but all these people can receive an excel file on email.
Excel does not actually do a simple clipboard copy. It copies the cell references and when you paste, it adjusts the cell references and formulae and puts them in the destination. If something changes the structure of the spreadsheet between copy and paste, it cannot guarantee that the pasted formulae will still work. For that reason, it clears the copy buffer.
It’s a complex problem to manage all of the cell references in a dynamically changing spreadsheet and early on the Excel team chose to keep it simple.
You can see a version of this if you do a cut > paste. The source does not move to the clipboard, it just gets highlighted. When you paste, Excel does a move operation that adjusts the references on the fly.
Excel just works so well, almost too well. Wanna share some formulas? Excel. Finance calculator? Excel. A small (<2000 lines) database? Excel. Even with Python for Google Sheets, I sometimes want to rip my hair out when I use Google Sheets compared to Excel (though the Google Python is pretty useful for autogenerated sheets).
> I sometimes want to rip my hair out when I use Google Sheets compared to Excel
Earnest, non-"gotcha" question - what is it about Google Sheets that you dislike or find irritating? I'm only an entry-level user for both, but I've found them of similar quality and functionality.
I have extensive experience with both and the main benefit Excel has over Google Sheets (in my opinion) is the amount of rows you can handle all at once. With Excel you can manipulate 200k rows easily. The same can not be said with Google sheets due to the fact that it remains a cloud based tool.
I found Google sheet to be enough though in 90% of my cases (may change depending on what you usually work on). The scripting ecosystem google sheets has is amazing (apps scripts and various python libraries) and is much stronger compared to Excel.
Updating a cell in Excel is a CPU round trip, whereas updating a cell in Sheets is a network round trip. An operation that touches as few as 1000 lines (e.g. autofill a large range, change a value that feeds into many cells) incurs many seconds of latency as it's fed through the cloud and the new cell data is refreshed. The more rows you have, the slower the response time.
Google sheets is useful for basic tasks. But as a power tool for complex models etc. it pales in comparison to excel. Not to mention that keyboard shortcuts are not the same which makes everything take significantly longer.
Agreed. Google sheets feels great initially. Then you go to do that thing that you do in Excel, and...you look for it, and it seems like a basic omission, so you figure you just missed it, and Google around for it, and no, it's just not there.
Even silly little things, like the fact that Sheets doesn't have an indent function, which makes it harder to neatly format financial data. I think the accepted workaround is to manually put spaces in front of every single row you need indented.
What we'd do - as the layer between engineering and upper management - was to do as much aggregation as necessary using notebooks and at the end run the "export to google sheets" call (thin layer on top of google apis). That would give the recipient some kind of control and allow them to feel the data and twist and turn it their own way, while not having to do the "big data" python/SQL stuff themselves.
I am at a new company now and I have yet to figure out how to create the "export to onedrive/excel" command. Google libraries to google sheets seemed so much more competent and well built. (But maybe i am biased...)
One can customize the ribbon at the top for must used functions, which can make Excel such a fast tool to use compared to Google Sheets or even Excel for Mac (speaking as a Windows user).
If I had a big Excel project to do, and I had the choice of 1/2 day on Excel (Windows) vs. a full day with Google Sheets or Excel (Mac), I would pick the 1/2 day with Excel (Windows).
The keyboard shortcuts improve productivity so much. Yeah, as another said it’s amazing when I try to use Excel on a Mac how much I evidently depend upon the shortcuts in normal use. They’re all different on the Mac version, and I can only take so much of it before I just email myself what I was working on and pick it back up on the PC. Seems like an easy thing for MS to reconcile but I don’t want to give up my keyboard mapping, and I’m sure the Mac Excel guy doesn’t either. Nice feature would be to choose what shortcut layout you wanted despite platform.
I removed the F1 from a keyboard so that I don't misfire when going for F2, resulting in the dreaded 'Help' window that you have to use a mouse to click out of.
Ctrl+Space, C used to work for closing the Help window until recent versions. Unfortunately that doesn’t work anymore, the only solution is to use a VBA macro bound to a keyboard shortcut. At least in return the keyboard focus now remains in Excel instead of switching to the Help dialog.
This is true of GSuite generally. So long as you need fairly basic functionality (which is all a lot of people need), its simplicity is a virtue and it works well. I prefer it to Microsoft Office 99% of the time. (Though I sometimes need Office for interoperability as well.)
I used to sometimes have to run massive spreadsheets. But these days, I mostly use it for things like personal activity tracking.
> Earnest, non-"gotcha" question - what is it about Google Sheets that you dislike or find irritating?
Using Tables in Excel is a gamechanger. Not having support for them is a huge point of frustration for me whenever I have to use GSheets. 95% of that is the fact that I can refer to the Table and columns by a given, logical name rather than having to use arbitrary cell identifiers.
Excel Tables combined with Excel Power Query will turn "hey boss, I figured out a way to save a few hours a week" into "hey boss, I just eliminated several people's jobs".
It's a kind of tongue-in-cheek video explaining why "You Suck at Excel", but what it's mostly doing is going through a ton of really awesome Excel features, many of them things you can't do in Sheets, and explaining them to a technical audience.
Highly, highly recommended - most of the stuff in that lecture I use every single day.
Google sheets has a 5 million cell limit, and can't usefully do things cross-workbook. In practice complex spreadsheets substantially smaller than that are slow and unstable.
That said, the multi-user editing is much smoother than excel and the remote API is better.
Except that it keeps changing. It's already on version 4 and at least one forced a complete rewrite of accessing cell values (and of course the old version was discontinued). Compared to Excel files from the 90s that keep running
As someone who pretty much only uses them for quick and dirty data shaping, at least for me Google Sheet's filter experience is garbage compared to Excel. Takes 5 clicks to do anything, can't right click to add/remove filters, the filtering process itself is janky, doesn't recognize data types ...
Sheets is awesome and has a lot of power, but it’s constrained in the browser and is defined by its competitor.
The other issue is that you don’t see as many power users of Google Docs and Google doesn’t have a clear strategy. For example, they could easily make a power bi type tool on top of Sheets and Slides.
I didn't know about Looker, but my BI needs are modest and Data Studio is free and simple enough.
Looker is a third party solution right? Or does Google offer Looker directly in some way? If you're up for sharing the pricing for looker, I'd be curious (the looker website has a request quote button, so I'm guessing it's not cheap)
Access is an interesting beast. Much maligned by IT shops everywhere. But it’s pretty powerful when you think about it as excel with a relational table model and a built-in reporting interface. I mean it makes all the sense in the world why it exists as a business tool, but it’s a cul-de-sac, usually tipping you off that it’s time for a “real” database and/or some COTS apps. Been decades since I seriously used Access, was before I ever got really exposed to true RDBMS’s and writing against them. But it’s probably where I got introduced to SQL, something a surprising number of developers don’t know these days.
Front end dev, video games, devs that use Firebase or Mongodb all the time? Devs that work in companies where only a trusted few touch the database, and everyone else works on their abstractions? Heck, with stuff like Rails, you can mostly treat the DB as a strongly typed KV store unless people want heavy reporting.
SQL is useful to know, but it's hardly necessary in today's world.
Excel, and more importantly, the spreadsheet is the best way to build intuition for a dataset, hands down. The alternatives, especially when it comes to Python and the default pandas output in a Jupyter Notebook are horrendous.
Where Excel falls short, is data size limitations + auditability. Putting more than 1M rows of data into Excel is not possible, and once you get into the low 100K's, it becomes almost unbearable. And handing off an Excel workbooks to a colleague is handing them hours of cell dependency tracing. On the other hand, data size + auditability are the super powers of Python data analysis.
I've been building a Python package, Mito (https://trymito.io/), its an interactive spreadsheet that automatically converts your spreadsheet analysis to the equivalent pandas code. You can write spreadsheet formulas, merge datasets, create pivot tables, etc. And because its implemented in Python, you can manipulate datasets with 10M rows of data with no problem. Our goal is to bring the intuitiveness of Excel data manipulation to Pandas.
> Putting more than 1M rows of data into Excel is not possible, and once you get into the low 100K's, it becomes almost unbearable.
I dispute this. Yes, the normal spreadsheet view of excel will buckle under 1M rows, but excel has another feature called "Power Pivot" that is backed by an embedded database and scales into the high millions at least.
I've personally used excel on a dataset of 18M rows and PowerPivot handled it just fine.
Big fan of this, but powerpivot sits on vertipaq (I believe) which is an in memory columnar DB or sorts (apologies if that’s incorrect). So at this point you’re getting awfully close to direct querying (another msft feature) which while analogous resembles more traditional db/client if you squint hard enough.
But yes, big fan of vertipaq which I believe also powers PowerBI.
Just to clarify.. Yes, vertipaq is the tech behind power pivot, power bi and sql server analysis services (in tabular mode) and the same column-oriented storage is also used in sql server. Excel generates queries against the data stored in a vertipaq model. You cannot write normal excel formulas on top of it and you have to use DAX (a unique to msft language, which is the replacement for MDX) instead, which is pretty much a no-go for anyone but well-trained power users.
IMHO where Excel falls short is in interoperability. Try processing .xlsx files in anything other than Excel and it can be painful. Let's see the new more open Microsoft really embrace competing on an even playing field that doesn't rely on ossified proprietary file formats.
Not really. Processing xlsx files in full generality is basically reimplementing Excel, but if all you need to do is extract/modify values and formulas or produce a workbook excel will accept there's plenty of libraries that will do that, and it's not that crazy to just hack something up using an xml parser.
I had to port some stuff that was using the google sheets API over to manipulating xlsx files instead, and it wasn't a big deal.
Whatever is in your XLSX can be converted to a static CSV file. Charts, pivot tables and formulas won't be transferred over, but then again I wouldn't expect them to.
The value Excel provides far outweighs the drawbacks of a vendor-specific solution.
The data in an Excel file can be exported as CSV, but the value in an Excel file is the dependencies and formulas that have been built up into a representation of the business rules. The problem is that this often becomes the only representation of the rules, and auditing it or puzzling the data out of that Excel sheet after the fact is like pulling teeth.
A business might want to get to improve, say, their quoting accuracy. I've seen lots of places that write quotes using Excel. They use a complicated spreadsheet to estimate "We need $4500 in parts from vendor A, but in previous projects with components from vendor A often needed rework, so we multiply their quotes by 1.5 to account for the risk and for someone (typically Bob) to rework them; Bob's workload is over 90% and he's less efficient when he works overtime, so multiply his total hours by an additional 1.25, we also have to adjust his hourly rate by 1.5 to account for overtime..."
It's a Hard Problem to convert the quoting process from one of a few engineers who also do quoting by copying and modifying the blank Excel template and years of human domain expertise into a process where data entry techs input stuff to a CRUD webapp. This is fraught with peril because the Javascript/SQL guy you hired to write the webapp (or, heaven help you, the SAP consultant) hasn't been reworking gear from Vendor A for 15 years and sees what looks like an error when the formula for actual cost from vendors B, C, and D takes their quote price multiplied by 1.1 (for shipping? margin? ) and vendor A's quoted price is multiplied by 1.5, and, hold on, the VBA macro separately takes the the estimated dollar amount purchased from Vendor A, divided by 2000, and adds it to the head of maintenance's estimated hourly total for the project?
Making business decisions about logic tied up in Excel formulae is hard. Writing logic in something other than Excel where you can more easily see the business logic is probably harder. Convincing non-technical decision makers to learn VBA to evaluate their vendor selection is probably harder still.
I wasn't really able to follow this, if I'm being honest. But given the number of dependencies you listed, both human and software-based, it does not seem like Excel is the problem here.
It sounds like VBA has allowed that team to build an advanced prototype of a quote generation web app. The next step seems to be to convert the Excel formulas and scripts into JS or Python. Quality assurance may be a hassle, but that is to be expected with any kind of refactoring.
I'm sorry it was hard to follow. The Excel sheets I've seen have been hard to follow, too! Also, most of the work is not actually VBA (there's a little), but formulas in spreadsheet cells.
The key difference is that the Excel spreadsheet is not a prototype: It's an MVP, which includes "viable"; many businesses have been making money with them for years. Another key feature of the 'prototype' is that the team is able to edit the Excel formulas, but burying the formulas as JS or Python (whether locked away serverside or simply obfuscated by nature of being different language with a new learning curve) removes a critical feature.
If you want to convert a hacked-together VBA app into something more robust, you have a migration path via VB.net which will interop properly with C# or F# or more-or-less-Python (IronPython). Or you can use something like thrift/protocol buffers to have your hacky part talk to your nicer part. It's harder to do that with Excel - make an application that's half Excel sheet and half "real program" - that's the part that's missing (though I don't think it's actually as bad as grandparent is saying - there are options for integrating external datasources into Excel that do work).
Lack of interoperability is an interesting angle, but not one that I've ran into quite as much. I've usually gotten around that by converting to a csv file in the case of data, or screenshotting graphs, etc.
Would love to hear a bit more about your workflows where you're trying to process an .xlsx file in another system. I'd imagine it would be a nightmare, but haven't ran into it myself :)
CSV files is one of the main problems with excel. Auto-guessing formats is the bane of many casual importers. And the warnings about tsv or csv when you open is tantamount to dark pattern. I can accept it once, but let me disable the pop up. Do Not auto save to xls or xlsx.
I don’t see that as a dark pattern because it’s honest - I will often open up a CSV and then turn on filters, sort the data, maybe highlight a few rows. The warning about losing that when trying to save as a csv is an honest warning. The same as opening a jpg in Photoshop or Gimp, adding layers and trying to save it back out - you’ll also get a warning that you’re about to lose work by saving to a simple file format.
How is that a “dark pattern?” There’s no deception involved. You’re calling things “dark patterns,” but I think you think dark patterns are just UX you find annoying.
I call it a dark pattern because it indirectly discourages the use of interoperability in file formats.
The suggestion is 'use xlsx or suffer this annoying pop up'.
It's an easy fix. Have an option to disable it.
The other dark pattern, since we are chatting about them, is that you cant disable auto formatting. Excel will try to guess at your data, forcing you to jump through hoops to prevent it. The subtle suggestion is 'just use xlsx and this problem goes away' when in fact, they could offer an option to disable autoformating.
In terms of interoperability, on the greatest things about Excel is Ole Automation. You can write a C++ or Visual Basic or C#, program that can programmatically drive Excel. You can pretty much do whatever you want.
Horrendous? Strongly disagree if you're doing anything non-trivial. As soon as you start mixing the two, both become worse. You're basically losing the UX benefits of native Excel and adding confusion to Pandas.
Yes, Pandas has a learning curve but so does Excel once you get into advanced functionality. It's inevitable. Once you get through this it's a fairly intuitive powerhouse.
That's a good point -- and maybe horrendous was too strong of a word. It's actually been really interesting talking to people who come from the Excel world vs the Python world first. There's a group of people we've ran into who have never used Excel before, and for them, you're right, giving them a spreadsheet interface to write formulas and manipulate their data is actually a huge disadvantage.
I think the way that Mito tries to walk the line is by making the Python code visible for the user to see what the equivalent Python looks like + easily usable in your analysis, but also completely generated for you. So hopefully, we're not introducing the confusion of pandas into your workflow.
Could you elaborate on this please ? I work with a lot of datasets, and have found python + libraries (plt/pd/np/scipy/regex) to be far more useful. But, that might just be my inexperience with excel.
Can you give a few examples of analyses that work better in excel than python ?
Not the GP, but I think they are speaking specifically to non-programmers with this statement.
It's not about which analyses are more performant/easier in one object versus the other, it's how do you most easily introduce the general audience to big data, both reading, manipulating, and transforming.
I actually disagree with their statement tbh, as I think that it's too nuanced of a situation to scope like this.
I used to work in a university, and depending on the dataset and the intended output, I would switch between R and Excel for the students. Those who needed R level analysis eventually saw why it was more useful for them than Excel and got good at seeing when to use R versus when to use Excel.
Those who had datasets/output goals that didn't need heavy lifting really just needed Excel. It's not incorrect to say that learning heavier tooling/languages is a benefit, there is also a time consideration to learn and become efficient at a given toolset. The heavier toolsets have their nuances and accomplishing the same task in less robust toolings like Excel is the more efficient and better approach for those who have extremely limited time and for those who are not likely to need the heavier toolset in the future.
It's just a simple cost benefit analysis -- what tool is going to give the best return on time investment?
There is a very valid and reasonable argument that investing into the heavier toolsets will eventually reach a point where even the simple tasks that Excel and other tools allows users to perform more easily with less knowledge is faster/better with the heavier language; the question is "when is it optimal for a given person to invest the time to get to that stage?", and that's a question that doesn't always have all available data to make an informed decision on since it's hard to predict the future.
You're definitely correct that its a nuanced question whether for a given (user, analysis) pair they are better off in Excel or Python/R/etc. Specifically with respect to building intuition for a dataset, however, there is a huge benefit of having an interactive data representation (if only for the ability to scroll and see all of your data).
Because you can think of Mito as a frontend interface to Pandas, using Mito doesn't prohibit you from building intuition or analyzing your data in the same way you would if you didn't have the spreadsheet frontend. It just helps you write the Python/Pandas code faster + see the most up to date version of your data set in live time.
The typical Mito user uses Mito multiple times throughout an analysis. A common pattern is: start by just visualizing the data in Mito, create a few graphs to help understand the distribution using matplotlib (right now we only have a tiny bit of graphing support), passing the data back into Mito to do some filtering and cleaning, then lastly creating a pivot table output using Mito. Of course, it varies greatly from user to user, but that's a general flow we see often!
As a bit of background on Mito, it works by passing the parameters from the frontend spreadsheet to the Python kernel backend, which transpiles the spreadsheet formula into Python/Pandas code [0]. So what we're hoping to do down the line is let the user pick which language to translate to, SQL and R being the obvious next steps. But that's a ways away :)
Mito looks really interesting, I look forward to trying it out. I'd there a way to sign up for Mito without the hubspot meeting?
For us we are going the opposite approach, we are building a VB interpreter to make it easier to run, build, and extend existing Excel programs. We allow calling libraries written in WebAssembly and GraalVM supported languages.
What non-spreadsheet programming languages/environments do you think work better than Python/Jupiter?
I’ve been fairly happy with the default Matlab IDE personally. Visibility and representation of data has the straightforwardness of a spreadsheet. But surely there must be others?
A lot of environments do not have easy to use hot reloading out-of-the-box (and also quick GUI creation support). This is the primary reason why development feels less intuitive in a lot of text based languages versus more visual tooling like Excel which can give instantaneous feedback.
Hot reloading is most famous for being a staple of Lisp languages (but they tie it to the repl rather than as a standalone feature). For Microsoft languages this is provided by Visual Studio (commonly known as edit-and-continue, it is available in some form or other since the original VB days). You can try it out with the embedded VBA interpreter in Excel (under the Developer tab).
For JavaScript this is a recent innovation (driven primarily by the React/SPA crowd). In Java, most IDEs have the feature but it requires a fair bit of setup and configuration (look up hot swap for Intellij). The closest thing Python has is Jupyter which admittedly is not that pleasant to use.
I really wish Python had the kind of support for Markdown that R does. Notebooks are fine, but the simplicity of having essentially a plain text file is just amazing.
Knitr theoretically supports anything you can call from R. Chunks pass their code and options to "engine" functions that return the results. Knitr provides a bunch of engines out of the box (Python, awk, SQL). But you can also write your own.
yihui.org/knitr/options/#language-engines
I once made a SAS engine to show coworkers how to adopt report automation without having to rewrite all existing code.
Nice product, I noticed that you are updating the next Jupyter cell; what was your solution to doing that reliably since `set_next_input` is so damn flakey?
I personally grew so frustrated with the state of GUI development in Jupyter that I tried to fix it in such a way that would allow proper message passing between cells and python code (because you can't wait on Comm events).
There's a bunch of ways that Mito and Visidata are different. The two largest probably being:
1) Mito is an extension to JupyterLab whereas Visidata is a CLI tool. As a result, Mito is a react frontend that is more of a traditional Excel-styled spreadsheet interface. You can use your mouse to perform point-and-click transformations, like writing configuring pivot tables or writing spreadsheet formulas.
2) Mito generates Python/pandas code for every edit the the user makes. So users are generating a script to manipulate their dataframes, running that script, and then continuing to use their dataframes throughout the analysis. People use Mito in a Jupyter notebook the way that they use pandas code, multiple times throughout their analysis, interspersed with graphing, ML, etc.
We're still exploring the business model to support the project. Right now, Mito is free to trial, and we're experimenting with a subscription plan somewhere in the range of $10 per month. I'd say about half of our users are using it for free and half of them are on some sort of subscription.
We're also considering open sourcing the tool, and doing the classic Enterprise Sales / consulting / other value add services on top.
If you have ideas about which direction to take it, would love to hear!
The company I work for has their CAM manufacturing calculations run through excel.
The non technical director has calculated every possible route line required for our CNC process. This is something that would be very hard to do in a conventional programming language. He did it with no coding background and it's one of the most maintainable pieces of software in the business. It's all laid out in front of you. If they need a diagram to explain something, it's there inline. There are no unreadable long nested if statements. He didn't even know you could nest them. It is truly amazing, I've not seen anything like it. I've seen plenty of train wrecks where people try to run other parts of the business through excel.
The world would be shocked to know how many hedge funds and mutual funds are managed in excel workbooks. I worked with one RIA who (correctly, IMO) listed "excel" as a systemic risk to their business. If excel ceased to exist tomorrow they (and much of Wall Street) would be in a world of trouble.
Like a lot of Linux zealots who ran Linux everywhere for 20 years, I hated most things associated with legacy Microsoft, because of EEE, and what they did through SCO. However, I had to relax my attitude about 10 years ago, when I realized that I owed my entire career to Excel.
I've mostly made a living by being an engineer who creates proper, focused software tools for other engineers to use, and almost every program I've written is because there was a crappy Excel tool trying to cope with the problem, and falling over due to size and unshareability. That's when I write a website in Rails, or a WinForms .NET application, keep adding features until users stop asking for fixes, then move on to the next one.
Of course, there's been ebb-and-flow in my career, but the bulk of it has been driven by the fact that Excel is so seductive, and easy to start something useful. Then, like a lot of Microsoft products, leaves you hanging when it's time to get serious. So, credit where it's due. Whatever you can say about it's shortcomings, they've been my bread and butter for 27 years, and counting.
>> "Then, like a lot of Microsoft products, leaves you hanging when it's time to get serious."
This is like a moving company saying their small moving vans "left them hanging" once they outgrew them and started doing national vs. local moves. A tool that proves useful at one stage is not useless because it can't be as useful at ALL stages.
The great thing about Excel as it enables you to do the one thing that kills most start-ups, projects, etc., which is simple to start.
> Excel may be the most influential software ever built
I'd argue that award should go to the real pioneers of spreadsheet applications: LANpar, VisiCalc, and to an extent Lotus 1-2-3 and LoGisTiX. What has really stood the test of time is the concept of spreadsheets, not anything specific to Excel.
Excel didn't do anything particularly revolutionary, it didn't set out to "influence" anything. It just competed on feature-matching and cut-throat commercial practices until all the others had died. A great achievement for sure, but praising that over VisiCalc and Lotus is a bit like praising Toyota over Henry Ford when it comes to automobile production chains.
Well, the linked article points out that Excel's engineers came up with a new, better, and much faster way of doing recalculation than Visicalc and 1-2-3 had at that point -- and it was arguably the first spreadsheet program built for the GUI era. Both of those things were new features, and they really mattered. Excel didn't win just based on marketing alone.
the same people who designed and built Microsoft Multiplan (a dud if there ever was one) also went on to create Excel, an odyssey that started in 1983, and that version was created for an early version of Windows that didn't get any traction, and then Excel was rebuilt for the Mac, so that's v2 (3 if you count Multiplan), and then when recreated again it finally took off after many years with the success of Microsoft Windows 3.0 starting in 1990.
Excel only won from the years of longevity provided by a deep pockets company with a vested interest in extending its monopoly, with a little help from Lotus who really dropped the ball on the switchover to GUIs
There was a really useful spreadsheet application for DEC's RSTS/E... named "calc" maybe? The innovative part of it was a cell formula included an output directive.
So the normal excel formula in cell b1 of "=sum(a1..a26)" has it's output written to b1. But with calc you could in cell b1 put "b2=sum(a1..a26)" and the result would be written to b2.
This became super powerful when dealing with ranges. You could have 1 formula that that calculate the row-wise total for each row... and it would be in 1 cell, so for example "$1..$10=sum($1..$9)" (forgive me I can't recall the addressing specifics for relative/fixed/named ranges).
Spreadsheets, as they incarnated in Excel are akin to running old style no-named-subroutines, only-two-chars-per-variable-name, goto-line-number BASIC but on modern hardware.
Ideas that could potentially grow into something safe and powerful cannot grow now because of market dominance of that outdated monster.
And I keep hearing the same arguments, like "it can be used without knowing how to program" - but have you actually seen those formulas they enter? How exactly it differs in complexity from, say, SQL? At least in SQL you'll have sanely named columns and can actually see logic, reading it a month later.
Mixing datasets and freeform reports in the same sheets is a design mistake.
Having auto-conversion for data is a design mistake.
Not having enforced row/column sets instead of just typing any formula anywere is a design mistake.
All that leads to millions of wasted hours of human life just looking closely on rows and rows of numbers with squinted eyes, trying to figure where accidental keystroke had broken your data.
I really liked how MacOS Numbers approached spreadsheets, before they were somewhat butchered for ipads and excel compatibility: spreadsheets were not "infinite", separating concept of tables from concept of pages; column and row headers automatically used as names in formulas instead of undescriptive A1:B2 format, making them actually readable.
Unfortunately it was also slow as hell.
Airtable is a good direction for data entry purposes by multiple people, unfortunately it's no good for even medium sized tables, and exporting is limited.
Tables are, of course, an excellent way of organizing data. Now add in the easy point and click UI and simple formulas to get you hooked, and soon you find yourself deep in VLOOKUPS and macros with no way back. Excel eases you into data organisation and manipulation. Many tools are better for any given task, but Excel excels at generalising.
I taught an engineer how to do 2D iterative scenarios with TABLE(), and his XLS files began taking 30+ minutes to open. Every XLS ended with "-DANGERTABLES.xls" to remind us not to accidentally click it and kill our machines. [For those who don't know, it's basically a way to reduce a massively interconnected spreadsheet (dozens of sheets) as f(x,y)=z, and then make hundreds of tables built with that function. Insanely powerful, but causes spreadsheets to grind to halt every time you touch a cell (unless you turn off auto computation).]
Excel is faster to get off the ground. You can get a initial feel for a data set in excel far faster than in Python of SQL. I grant that may be the practitioners limitations than the software.
That’s what’s so awesome about it. All the elitist notions of what a programming language or database cannot be or has to be, about speed, best practices, standards, safety, harmful functions like GOTO, etc, fall to the wayside with a pragmatic interface that gets rid of all the required cruft and toolchains, just giving all the users direct access to power. And practically everyone in a desk job uses it occasionally. It’s as ubiquitous as coffee.
It’s a refreshing rebuke to modern convoluted toolchains, binary signing, prohibitions against doing this or that, all the ceremony of modern programming. It goes directly against the trend to make computers increasingly locked down in functionality, mere appliances for passive consumption by and harvesting of data from the masses. It’s like giving everyone in a desk job a Leatherman multitool and a lighter. A bicycle for the mind in actual truth. Permissionless innovation at its finest. Excel! Excelsior!
But they're pain in the ass to use, modify, and really feel as an afterthought which barely used by anyone. Compare this to "Numbers": you just enter any text int row or column header and this text is immediately usable and visible as a range specifier in formulas. How intuitive is that?
Also excel has (inner) tables inside of tables, with more restrictions that.
Also excel even has SQL-dialect for querying columns. That's not important, important are defaults, and how your simple spreadsheet you can fit into a screen is going to evolve. Excel makes it VERY hard to not make a mess, even if you have time to learn it.
You talk about SQL being great, yet you probably never had to download data from tables where the headers are German abbreviations, or gibberish due to 8 character limit in 1985.
SQL is greatER than excel's default mode of addressing data. As russian, I've worked with a very diverse data sources in different encodings during my career, fair amount of broken CSV's, xml's, etc, cleanups, joins, sqlite processing etc. And yes, many, many excel files used as a poor man's data entry software to cleanup. No german abbreviations, right, just poorly made russian transliterations.
This is the most confusing article layout I have ever seen. The first part is almost an article in size and is an ad and then what seems to be another ad about some people. I was about to close this article assuming it was just spam with a click baity title.
Me and spreadsheets go way back. My first job was hacking Lotus 1-2-3 at Kodak as a summer intern in 1985. I consider it my first programming job.
A year later, I got interested in neural networks, and built back-propogation models in Excel on the mac. Yes very SLOW but still a great way to learn.
As side project ten years back was to leverage Excel's native web query (IQY) mechanism to build a profitable SaaS company just based upon letting users get data into Excel from various 3rd party social media and analytics platforms.
Now I work in big oil and our team basically turns Excel models that users create into scalable data warehouse apps.
Even after years working with Excel, I still consider myself a journeyman.
Using excel for learning NN. Multi input on a node would be a bit wonky (but doable) and layers could just be sheets in the workbook. It even already has a bunch of nice built in tools to import/export data. That is one of those 'why did I not think of that' things!
> just forms with some validation and lists of things - a spreadsheet can do all that with greater flexibility as a bonus.
Speadsheets are great at a lot of things, but data validation doesn't tend to be one of them. In fact, I'd argue that's one of the main reasons to move off of spreadsheets: to make your data more structured.
It may well be possible to create a spreadsheet-like UI that is good at these things though. And I can certainly see that being successful. It'd be difficult to tradeoff flexible vs constrained though.
> Speadsheets are great at a lot of things, but data validation doesn't tend to be one of them.
What limitations are you thinking of? I can go to Data -> Data Tools -> Data Validation and restrict to whole number, decimal, list, date, time, or string length. If that's insufficient, you can create a custom formula which has to evaluate to TRUE for the input to be considered valid. Regex isn't supported out of the box, but quite a number of string functions are, and there are readily available Regex user defined functions (VBA called via formala) available online.
You can also customize the error alert that would be displayed to the user if they try to input something invalid. I think it'd work quite well for many MVP/single page web apps.
The question is not what a sufficiently motivated competent person can do with the tools.
It's what a sufficiently motivated incompetent person can do to your data with the tools.
Infinite flexibility in the tooling means infinite ways to mess the database up in subtle and/or irredeemable ways, and a formula in a cell or even a regular expression are not great ways to tidy up real-world data, you often want autocorrect, autosuggest, defaults and friendly error messages for bad data rather than just ERROR IN CELL G91. When you reach that level of complexity it becomes much harder to build something useful with a spreadsheet-like tool alone.
It is a really interesting idea though and for certain classes of data could really work well.
I think Microsoft dropped the ball big-time by not making it easy to export an Excel workbook or Access project as a mobile-ready turnkey Web application. (It looks like it's possible to do, but not easy to do—from what I can tell, it either requires Office 365 or third-party software.)
If that functionality existed and was easy to use, it could have been a very convenient option for businesses running their operations on Access database and looking to take advantage of mobile.
I know Microsoft has InfoPath and Forms, but those aren't Access, so they're not going to be as popular.
Airtable (airtable.com) is doing something like that. It’s less Excel and more a souped up relational database, so a slightly different angle where you surface the CRUD of a CRUD app to the user.
I see Airtable as an online database with the ease of use of Excel. I created a system for a local charity using Airtable. I found it worked really well and the charity are delighted.
lol. One of my observations from my experience at startups : if you want to create a B2B SaaS company, convert a company’s spreadsheets into a webapp and voila!
One of the main excel advantages come from the fact that the app presents useful features and never changed their interface. Unlike modern software that try to change their workflow to "improve" it, excel has kept the same stable interface for decades. This allows people to create workflows built on the software, not fighting it. Excel users know that the interface they employ will be stable for another 20 years, or even more. Very few applications can say this, the other ones I know of including Vim and Emacs.
"Stable"? Like the version that screws up basic window management features? (Simulates the Alt+Tab behavior; doesn't actually let you have two Excel windows side-by-side.) Thanks, Office team.
I will never trust Excel for manipulating any real world data. Period.
Excel only keeps the first 15 digits of any number you give it [0]. If you want to keep the full number, you have to store it as text instead. And then you can't perform calculations with it without converting back to a number and losing fidelity.
The two most prevalent data types in business are numbers and dates. It's incredible that Excel is rubbish at dealing with both and yet the world thinks it's the gold standard for doing "business-y stuff" in.
This probably has been said before and relates to spreadsheets in general, instead of only to Excel: Well written spreadsheets, if they have need for formulas, can be seen as examples for functional programming, where a cell takes input from specified other cells and the result is in the cell itself, not modifying its arguments, so that it stays the true result at any point in time. This has some advantages and can give a spreadsheet a set of advantages over imperatively written programs processing the same data. Additionally spreadsheets allow for a very short feedback cycle. Of course there are downsides to it as well.
I used to think that I hated excel, and that as a DataScientist-TradingDeskAnalyst, python or R would save the world. Now I realize that my hatred for Excel has nothing to do with the software itself, but the way it is used for political motives. If your VP or Top Trader or Boss choose to keep everying on 'his' excel, its not for sofwtare quality. Its for control. You can run away with your excel-data-model on a USB stick. Not so true for SaaS. You can keep it on your PC, and not share it with others. You can put passwords without IT knowing about it. Excel = Shadow IT department with hidden political goals.
Aside: Joel Spolsky's now famous talk about Excel is a must watch for anyone that uses Excel but hasn't been arsed to actually take a course in it. Even for Excel haters out there, the talk is very much worth your time today.
I would love an entire OS (like ChromeOS) & protocol around Excel/spreadsheet. you log in, and everything on your desktop is a spreadsheet : contacts, calendars, email/messaging, forms. Like a primitive hypercard with only table view.
I remember playing around with Multiplan and 1-2-3 in the 80s but the first time I saw Excel I knew I witnessed the birth of the greatest SW product of all times, ever. At the time engineers still spent a lot of time writing crappy one-off homebrew scientific apps and Excel really helped as a first platform for calculations you didn't want to do on your calculator.
It's hard to imagine now but at the time PCs were command-line only and the early Excel versions, at least the one I saw, booted up a runtime version of Windows just to run Excel. I'm not sure whether they ported it over from MacOS to that special version or what but it was shocking to see.
This is a well researched article touching on some key points as to why Excel lives on. Interesting for me is the unbundling of Excel, and the birth of many B2B SaaS products. I'm not sure Excel was ever the right program to generate many of these products, and so it makes sense someone built a specific program to house it (product boards, CRMs, calendars all fall in this imo). Others however, absolutely, I'm not sure the long term value in some products that can simply be done in Excel - often when a startup goes after one of these verticals it's hard to create value for the user and the business, because by building a product you are by definition limiting the potential/power of the program by limiting what it can do. So you're asking someone to pay for something that is more limited than it's Excel-cousin, but might be simpler to use and look prettier. An example to me of this is financial modeling / flightpath type apps for businesses. Instead of paying for a bunch of saas, maybe a company should go back to hiring more Excel ninjas, ergo Excel Never Dies.
In academic data management I have identified what I suspect is a common progression over the course of a career. Obliviousness to the problem. Rage at the inadequacy. Search for alternatives. Realization that you do not have the resources to build and maintain something else, much less train all the labs to use it. Further realization that it is harder to explain how to set the right file encoding and the fact that a csv file is not a colon separated file to hundreds of labs than to just accept xlsx. Acceptance that Excel is a more reasonable user interface for data deposition than anything you could come up with.
It is sad, but at the end of the day, however bad Excel is for life sciences data (to the point where standards bodies renamed genes due to autoformat issues!?), it ends up being better when usability and bad data edge cases are considered. Defaults matter for non-technical users, and even asking them to change the format they save in to csv is likely to cause issues because it is one more manual step that can go wrong, or there is some locale nonsense that will cause something to break etc.
I have found Excel deeply awesome to use, and I am building a reactive programming language inspired by the concepts (for board games!).
The key is that a single script defines the entire workbook's logic, then data sits externally within a giant JSON object.
I'm trying to find a balance between the engineering concepts (source control, data versioning, audit logs) and productivity (how easy is the language and related tools).
I'm currently working on a editor for board games where I get playful with how to build UIs for the language. It's fun, but I'm wondering if I am re-inventing hypercard.
Excel is amazing for rapid data analysis and modeling, the flexibility it provides is exceptional when you're working alone and are trying to do one thing, fast.
It becomes a pain when you're trying to use it to enforce workflows, across multiple people.
The problem is that it works "well enough" that building a proper data model/system to replace the spreadsheet workflow is not going to get prioritized because it already sort of works and there are likely bigger problem areas in the org.
It's a pretty classic problem where something enables you to solve a problem fast and well enough as to often preclude an excellent solution.
On the flip side, if something is new/evolving and is handled in excel, it's a mistake to try to tighten it up in an application right away because you are going to end up evolving the app at a cost whereas evolving the spreadsheet would have been nearly free.
I'm wondering if spreadsheets could be used as a tool to introduce programming to kids, since all the intermediate values are visible, and most programming is just data transformation. The fact that spreadsheets are effectively functional languages might mean that looping could be implemented as recursion with tail-call optimization... instead of only having cell references you could have references to keyboard or mouse input values, or URLs pulled from outside sources, or files read off the local storage... Instead of just having charting and graphing tools, you could have, say, a Turtlegraphics output box that would follow the instructions from some designated series of cells (which perhaps were themselves generated by formulas)
Another thing Excel can't do well is when you need multiple people to be updating a single spreadsheet shared in OneDrive / SharePoint. If too many people have it open, OneDrive can fail to merge the changes, and you might have races and end up with data corruption.
You know you've won when there's a special interest group (with a yearly conference!) focused on risks the software is creating http://www.eusprig.org/
We started with a simple excel tool, that has morphed into a visual pay system that talks to the database through SQL queries - it impresses me everyday how much this excel file does, and the 2000 users that use it daily say the same thing
I'd have a couple of development proposals for excel:
1. Remove the ability to save workbooks. This would keep the excel in organisations where it excels (pun intended), namely quick & dirty sketches and visualizations. If you need something twice, it is likely you need that more than twice and you should be using something else.
2. If not that, give me a worksheet type that forces each cell in a column to have same formula or data type. and just a sheet that you can refer to as any other sheet, not a powerwhantnotthingy.
3. Version control. Seriously, Microsoft, what on earth are you paying your excel developers for if not this?
I totally agree that this makes sense for certain types of Excel work -- the data analysis, report generating workflows. You can see my comment above about how we're addressing some of these issues with Mito.
However, not sure that these three points hold for the type of financial modelling work that is done in Excel today. There isn't a great unbundling of Excel for the LBO, etc world (yet), so the inability to save or have columns with multiple data types/formulas seems quite limiting for that world.
I was thinking that there were two types of worksheets. the current, freeform sheets, and in addition to that a new type that would enforce data integrity over columns. That would make it much safer to have worksheets for structured data and freeform sheets for visualizing etc separate.
(the first one was admittedly a bit tongue in cheek.)
I think this is provided through either OneDrive/SharePoint or some other offboard solution. Do you really want Excel to have a native version control system? Seems like other solutions would always do this better.
Native Windows XP + Excel was badass. No new features were needed. No one asked for anything else. It was feature complete.
Have you tried running an older version of Excel (2007 era) even in a VM? It is lightning fast. No cell-movement animations and this unibar crap at the top, saving a file is 2 clicks away unlike the newer version going off to the cloud, making requests and having to back out to save locally. WTF.
Excel is an amazing app. UI engineers and PMs at Microsoft are trying to kill it.
That save as... workflow is most annoying. Adobe does that too. In almost every scenario there’s a sync tool to take care of cloud storage. The web is great, but the web is slow and brittle. The cloud save is best when it’s an asynchronous background task.
...but where Excel really shines and enhances productivity is in combination with that other tool we all love and will never give up, email. Someone emails you (and 10 other persons) some data in an Excel sheet, you (and unbeknownst to you, 2 other persons) update the sheet with some new data and reply to the email, and after a few iterations nobody knows anymore how many versions of the document are in circulation and which one is the most up-to-date. Oh, joy!
Sounds like a training issue - Excel has a much better tool available now. Click the “Share” button in upper-right corner and now you’re all working on same doc and have concurrent editing capability.
If anyone wants to gather ideas on an app idea, they would be well served by running the following query in their favorite search engine : "<app idea> xlsx"
I've just read the chapters on VisiCalc and Lotus 1-2-3 in "Founders at Work". It looks like VisiCalc made the Apple II take off back in the early days. And Lotus 1-2-3 addressed all the pain points people had with it a few years later on a more powerful platform (PC). Interesting stuff for sure. Why do I discover this book 12 years after it was published?
At my current job, I've really come to hate Excel. Business is completely addicted to it. Despite Data Quality being an explicit concern that we're supposed to work on, they love maintaining their hand-typed data in Excel sheets, referring to other data with hand-typed names instead of proper foreign keys, and then I'm supposed to load and connect all that crap.
And nobody is using it for actual calculations, which is what spreadsheets are for.
I would really, really like a flexible database with an Excel-like front-end and easily-definable columns that make use of foreign keys, so we can actually maintain some data quality here. And what I'm looking for sounds so obvious that I have a hard time believing there's not already a good open-source solution for it, but I have no idea what it is. I'm this close to just building it myself. Anything to wean these people off their Excel addiction.
Excel is like the yellow engineering pads: essential for an engineer.
I tried to use Google Sheets, but nothing matches the speed of popping open excel, and the functionality (still can't do 2D TABLE solvers in Sheets AFAIK). I know Sheets can work offline, too, but it is sluggish compared to native Excel. I had issues for years with Excel on Mac doing wonky things, but with the latest non Office365 version, they've fix a lot of issues (mostly rendering).
I got into JMP but only because my employer paid for it (it's like $15k i think), but while it is powerful stats engine, it lacks the sheer imaginative flexibility of a spreadsheet.
There really aren't many other choices, are there? I mean, prior to excel, lotus123 was de-facto, and the last time I tried StarOffice it hurt my soul.
It is used as a graphics output of those financial models as well. Bankers, both commercial and investment, use it to copy and paste into PowerPoint presentations that form the basis of billion dollar transactions everyday.
Excel never dies, but BOY does it get worse with each iteration.
It often feels to me that in the last several releases all that's been done is apply more and more lipstick to a pig that's already 90% lipstick.
I recently fired up excel 2003 on a whim (yep, I'm that much of a party animal.)
MY GOD. It was blazingly fast. Beautiful. Usable.
(If you ignore the fact that the one good change in the last 2 decades is the XLOPER12, allowing >255 rows and lots of columns). (X)
(X) Yes, Lambda looks interesting, but HOF aren't really necessary in basic sheets, I'd rather they got it usable first.
Do others here still recommend using Excel/Google Sheets despite having programming knowledge, or are there more powerful tools available (eg. Airtable) which might be less accessible to people without tech backgrounds? If spreadsheets are still the way to go, any recommended resources on learning some best practices with them?
I never got into spreadsheets because it seemed unnecessary after learning how to program, but I end up missing out on applications that might work in excel but which I don't care enough about to hand-write.
Excel is incredibly useful for all sorts of quick data crunching. My preferred introduction to intermediate-level spreadsheet usage is Joel Spoelsky’s “You Suck at Excel”
Absolutely. Excel is a workhorse. It would be like asking if, since I got my bulldozer, do I ever still use a shovel. Yes. All the time. It has massive casual utility on a day-to-day basis. Add to that a lot of your business partners communicate information in Excel. When I was a young programmer I intentionally developed good excel skills because it was the idiom my customers knew. I felt like they would think less of me if I wasn’t competent on such a basic tool of business. And I think that instinct was correct. I look at someone in the corporate world who’s unable to sling a good spreadsheet as developmentally disabled. Like if you walked in saying you don’t know how to use a word processor. Spreadsheets are a way of exchanging data between humans in the same that word processors facilitate this, but for structured data sets. Time spent learning basic Excel functions (math stuff and xlookup for example) and how to build pivot tables will pay you dividends over the years.
It highly depends on what work you need to do, but in general I use Excel for quick and dirty data crunching where the number of rows isn't that big (<100,000) and I don't expect to need to repeat the analysis often. For example, as a cyber security analyst, one-off sifting through some CSV format logs. Being able to do some basic transforms on the data with the benefit of real-time visualization is nice.
As a person who mostly does (large) data analysis and mostly uses python, I've recently thought of when I prefer to use spreadsheets vs python and my (personal) conclusion was to use spreadsheets, when my data can fit into one screen. In that case with excel you directly see all your data, you can change inputs and see output change in real time. But if the amount of data is larger than that, I am better off with dedicated calculations (in say python) and plots summarizing the data.
Over the years, the wife and I tried numerous shared calendars - but nothing has yet topped the one I made fairly manually in Google Sheets. I just copy a few rows down every once in a while and we keep it going. Color coded, Emoji for different types of events, each week is collapsible and shows just the emoji for that day. Never happier.
Every other calendar's interface and customization seemed like a limitation rather than a feature.
I've been thinking about trying to create my own open source spreadsheet. I can't decide if they're simple or complicated.
The simple view is that all you need is: (1) One main UI component, "the cell" (2) a domain specific formula / programming language (3) the underlying reactive system that tracks cell dependencies and updates them, etc.
I think it depends on what you intend for it to be used for.
The reductionist view of it is that it's a workspace for crunching numbers. But in practice, Excel is sometimes used like a frontend for a database engine, with sometimes heavy scripting to integrate into software processes and workflows. I think Excel's ability to stretch beyond what anybody would still reasonably consider the scope of "spreadsheet software" is why Excel is as entrenched as it is.
I don't think many programmers see it as a safe or ideal way of handling the kinds of workloads that people use it for, but I think we all acknowledge its unmatched ability to let non-programmers automate data crunching.
One minute we're effusing about Excel's magical powers as if databases and programming languages were an afterthought, the next we're all over Excel's flaws after yet another financial company is caught with its trousers down for relying on Excel for too much of its data infra. Excel is just a spreadsheet already.
Just wrote a complex calculation for my wife's work in Anthropology, regarding translating a Canadian grid to gps coordinates. In Excel. Because it was there, and it can encapsulate a complex expression and present it as a field and resulting value.
So no, even if Excel died we'd need something much like it the next day.
I went to live in a German speaking country for a few years and the most astounding and unsettling thing I learned the whole time there was that function names in Excel are also translated to German. For example, you need to use SUMME instead of SUM.
I've seen spreadsheets with horribly complex, incomprehensible algorithms, that are so much easier and simpler to write in C++ or Python or any programming language.
BTW XLWare makes a great library LibXL to create genuine .XLSX files from a program.
Is there a way to manipulate Excel cells from within Python/Pandas? Last time I checked I had a hard time to write to an existing Excel file (= prevent overwriting it).
Would love to do the complex calculations in Python instead of using VBA.
Makes its closed-source storage format all the more a travesty.
All office file formats have stopped document and information processing and search way harder, and have been a lock-in pox on the world for, what, 3 decades now?
I don't see any software produced today approaching the level of user-centric productivity and speed one can get out of (keyboard only) Excel. Is software like this just not being produced now?
Excel is great no doubt but everyone forgets that it is solving only some types of problems - mostly data input, fomula-based calculations, etc. It is completely unfit for purpose for others.
I have built 3 commercial products: a seating planner, a visual planner and a data transformation tool. I'm confident that Excel is the main competitor for all 3.
Someday accountants and business people everywhere will bite the bullet and spend the 5 hours or so it takes to get a working knowledge of R and finally step into the first world of programming languages.
Sign up for our mailing list (link in profile), that's what we are building (and more including support for collab editing, version control, and cloud native deployments).
But that's rare for the vast majority of users. How many people need to really calculate with 1 million rows of data? That may be common in some fields, but when you hit that level of data, you know that you should be using other tools. Excel hits a solid sweet spot for a large percentage of the computing public.
Agreed. People who use Excel daily knows it's limitations.
They know that it can't work with huge amount of data, but they do know they could have their internal tech team upload the data into their database and send them a snippet of the data.
Excel is horrible to program in. Example: I needed to import a bunch of URLs, then pull the query params out and auto-create a SQL query based on those. I was able to do it, but when I entered the Visual Basic editor, it was a whole new word of UI hurt.
> Excel, and more importantly, the spreadsheet is the best way to build intuition for a dataset, hands down.
Excel is a low-dimensional, untyped, flat database. I couldn't think of something worse. It has been successful only because its design mimicked traditional accounting books. But for more complex datasets, ugh.
Back in NeXTSTEP days there was Lotus Improv (and later Lighthouse Design Quantrix). It permitted high dimensions, true names for rows, columns, hypercolumns, cells, and so on, and sophisticated modeling capabilities. It was, clean, required none of the ugly bug-filled hacks you see in Excel, and very easy to get your head wrapped around. Of course it's dead now.
> It permitted high dimensions, true names for rows, columns, hypercolumns, cells, and so on, and sophisticated modeling capabilities. It was, clean, required none of the ugly bug-filled hacks you see in Excel, and very easy to get your head wrapped around. Of course it's dead now.
Do named ranges in Excel not match some of what you're after here?
For example, if any of you play D&D online, you might be familiar with dndbeyond's character sheets. They're a fantastic way to onboard new players who might not have the inclination to spend hours with the rule books before they even start playing. It does all the calculations for you and gives you some buttons like "roll athletics" and doesn't let you add more spells than your character can have with their stats.
I recently persuaded some friends to give FATE a try and built analogous push-button character sheets with google sheets [0]. It was quick and simple. With conditional formatting, you highlight bad states (rules say you can't have more of X than Y!). With the script editor, you can add full on buttons for dice rolls and other state changes with whatever logic you want (anything you can code up!). Checkboxes are obvious but super useful. And the transparency of the calculations is helpful for teaching people the system (this stat is "min(A4, B1+C5)").
Without google sheets, it would be a serious endeavor to build a stateful, database backed, live collaborative GUI that can be added to and customized on the fly by my users. With google sheets, it was a quick fun afternoon hack. Excel/google sheets is an amazing piece of technology.
[0] Screenshot of the "app": https://raw.githubusercontent.com/imh/public_images/main/Scr...