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.
> 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.
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!
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.
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.
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.
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.
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.
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).
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.
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.
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.
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...)
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)
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.
> 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".
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 ...
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.
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.
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?
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.
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.
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.
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.
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.
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 :)
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.
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.
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).
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.
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.
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.
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.
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.
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.
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.
> 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.
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!
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).
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.
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).
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.
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?
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
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.
> 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.
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.
- 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?
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.
> 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.
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.
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.
> - 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].
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.
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"
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.
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.
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.
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.
> 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?
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!
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.
It inspired me to do this 4 (almost 5 now) years ago in JavaScript. The prototype was written to prove a DOM renderer could handle trillion cell datasets having a high on screen cell density running at 60fps.
Bonus: Watch to the end of the video to see video running in a cell.
304 comments
[ 2.7 ms ] story [ 357 ms ] threadAfter all 90% of web apps are 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.
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.
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.
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.
It wasn't easy, and there was a lot to figure out, but the end result is starting to look pretty simple:
https://youtu.be/vn2aJA5ANUc?t=145
https://github.com/publicdomaincompany/copypaster
I love the premise - will give it a go.
Excel born in 85? Ok, but spreadsheets were around long before 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.
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.
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).
https://docs.microsoft.com/en-us/windows/powertoys/
I used to sometimes have to run massive spreadsheets. But these days, I mostly use it for things like personal activity tracking.
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...)
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.
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)
How is the number of rows related to cloud-based?
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.
That said, the multi-user editing is much smoother than excel and the remote API is better.
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.
This could be very powerful. It would help Excel to be repurposed to potentially something greater....
Of course, Google Sheets and Apple Numbers should tap into that same functionality...
SQL is useful to know, but it's hardly necessary in today's world.
https://youtu.be/vn2aJA5ANUc?t=145
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.
https://www.bbc.co.uk/news/technology-54423988
/s
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.
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.
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?
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.
Lisp has a function called LOAD, which can load source and/or compiled code.
RMarkdown + RStudio + knitr
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.
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 ?
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.
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!
[0] https://trymito.io/blog/transpiler
I agree, SQL is what I like more for mangling. Except for the pivot/melt part that is
The value Excel provides far outweighs the drawbacks of a vendor-specific solution.
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.
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.
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.
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 :)
The dark pattern is in repeatedly nagging me about this fact.
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.
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.
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.
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.
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.
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.
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.
[0] https://support.office.com/client/Data-Model-specification-a...
[1] https://support.office.com/client/power-pivot-powerful-data-...
But yes, big fan of vertipaq which I believe also powers PowerBI.
Any day: rmarkdown and csv
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!
Now someone with a bit of balance, can go quite far with it.
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).
> https://github.com/ipython/ipykernel/pull/589
But sadly the priorities of big open source projects don't always match your own. So I had to extract that logic into my own kernel.
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.
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.
Posted here but didn't get traction: https://news.ycombinator.com/item?id=24980325
- 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?
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.
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.
In many cases you are just not fully up to speed with the features in windows / excel.
- 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.
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?
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.
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.
Pasting the possibly modified value is, IMO, always undesirable; so you can ignore the possibility altogether
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".
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.
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.
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.
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.
[1] https://superuser.com/questions/611854/prevent-excel-from-cl...
[2] https://web.archive.org/web/20160725070440/http://discuss.fo...
[1]: https://youtu.be/0nbkaYsR94c
This makes excel open in a separate instance
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].
1: https://developers.google.com/apps-script/guides/sheets
2: https://developers.google.com/apps-script/reference/url-fetc...
3: https://excel.uservoice.com/forums/304921-excel-for-windows-...
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).
Why send an Excel via mail? If you make changes, you have to send the file again and you can't collaborate with others.
That's what the share function in Google Sheets is for :) If that doesn't work, you can still download the file as .xlsx and send it via mail.
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.
https://cbi-blog.s3.amazonaws.com/blog/wp-content/uploads/20...
Now someone make that for web apps that began as an excel spreadsheet.
Or even better, ML projects that should have stayed a simple spreadsheet.
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.
https://youtu.be/0nbkaYsR94c
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.
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.
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.
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.
Do named ranges in Excel not match some of what you're after here?
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.
Yes, there were plugins to support XLSX (and even ODS) in Excel 2003, but it wasn't well-known.
It inspired me to do this 4 (almost 5 now) years ago in JavaScript. The prototype was written to prove a DOM renderer could handle trillion cell datasets having a high on screen cell density running at 60fps.
Bonus: Watch to the end of the video to see video running in a cell.
https://m.youtube.com/watch?v=ff-eDS4OZpk