Excel never dies (2021)(notboring.co)
notboring.co
Excel never dies (2021)
https://www.notboring.co/p/excel-never-dies
281 comments
Google Sheets is pretty basic compared to Excel in terms of the kinds of data analysis and queries it permits (without dropping into another language). Excel added tables over a decade ago that allow for some very useful and much cleaner query and data analysis stuffs in straight Excel. Then there are power queries and pivot tables, not sure how long those two have been around but last I used Google Sheets it had nothing like either.
My point being, don't judge spreadsheets by Google Sheets. Actually use Excel and you'll see a much more capable system and get a better understanding of why people (particularly non-programmers in business settings) stick with it.
EDIT: Pivot tables are in Google Sheets, so either I missed them before or they were added after I last gave it a serious look. My google-fu is not discovering the date they were added.
My point being, don't judge spreadsheets by Google Sheets. Actually use Excel and you'll see a much more capable system and get a better understanding of why people (particularly non-programmers in business settings) stick with it.
EDIT: Pivot tables are in Google Sheets, so either I missed them before or they were added after I last gave it a serious look. My google-fu is not discovering the date they were added.
> Excel added tables over a decade ago
I ran into this... last year when trying to do something with excel (I forget what excactly, apart from it needing joins and some analysis between several datasets).
It felt so unintuative with the tables "embedded" into sheets, it feels like they should be a sheet or a table, not both.
Power queries seemed a really neat tool for non-coders to munge data as needed.
I ran into this... last year when trying to do something with excel (I forget what excactly, apart from it needing joins and some analysis between several datasets).
It felt so unintuative with the tables "embedded" into sheets, it feels like they should be a sheet or a table, not both.
Power queries seemed a really neat tool for non-coders to munge data as needed.
Not sure what you mean by "power queries", but Google Sheets support SQL queries.
Would be easier to see on an example.
Would be easier to see on an example.
PowerQuery. It's a tool built into Excel. It's a GUI that wraps an almost purely-functional DSL designed for ETL and data munging, called the M language. You can either use the GUI or write the code directly. It has first class functions and closures and normies are programming in it. It's great. More people should know about it.
Btw it's kind of funny seeing so many HN users, many of whom must be working on software that competes with Excel either directly or indirectly, who are so unknowing of the full capabilities of Excel, capabilities that are the bread and butter of any e.g. financial analyst, or logistics manager, or any smart non-programmer white collar worker. Maybe this "hacker repulsion field" is the secret of its dominance -- you can't compete with it if you never learn what it can do.
Btw it's kind of funny seeing so many HN users, many of whom must be working on software that competes with Excel either directly or indirectly, who are so unknowing of the full capabilities of Excel, capabilities that are the bread and butter of any e.g. financial analyst, or logistics manager, or any smart non-programmer white collar worker. Maybe this "hacker repulsion field" is the secret of its dominance -- you can't compete with it if you never learn what it can do.
Your aside is exactly why I wanted to use a spreadsheet. It's the only tool that has that market penetration for non-programmers, and I wanted to see what made it tick. It seems like that may have backfired by not using Excel, however.
If you enable PowerPivot (hidden in the COM addons settings) you'll get a quite capable data analysis tool for small-ish data with M + DAX with some rough edges.
They're the only reason I actually like to use Excel now. PowerBI has those natively built-in in a more modern iteration but is not as flexible (little direct data entry capability). That said PBI is ultimately meant for reporting.
They're the only reason I actually like to use Excel now. PowerBI has those natively built-in in a more modern iteration but is not as flexible (little direct data entry capability). That said PBI is ultimately meant for reporting.
I would love to understand more what limitations you faced with GSheets that are non-existent in Excel. I used to think the same but then I built some pretty complex computations in GSheets that I could not easily replicate in Excel. And U have built some complex stuff in both.
Extending on this, if you want to make really powerful business software — ingest and emit Excel sheets that other departments can use in their own flows.
PowerQuery is powerful, but also compromised by the fact it is so closely tied to Excel. If you want to do data transformation tasks (such as joins) on your Excel data and you don't want to learn R or Python+Pandas then you might be better off doing it in a no-code tool designed explicitly for the task, for example Alteryx (if you have deep pockets) or Easy Data Transform (if you don't).
There is another reason that is less often openly disclosed, but very much in play. I recently proposed RStudio or Python/Panda combo to deal with some of the limitations of Excel ( which also conveniently bypasses Alteryx's cost ), but the response I got was somewhat surprising, because it revolved around and I am paraphrasing 'will others be able to learn and use it'. And here is the rub. Excel has a lot of online tutorials and in some ways it is a known quantity. And, well, some people don't want to learn new thingsshrug.
No code data wrangling tools are significantly easier to learn than R or Python+Pandas. That is their main selling point (inevitably they trade some flexibility for this) and there is a range of them to cover all budgets.
[deleted]
In this context I’m interpreting DSL to mean “domain-specific language”
Querying Google sheets SQL doesn't support joins
> My point being, don't judge spreadsheets by Google Sheets.
I've used Google Sheets for years and greatly prefer it over Excel at this point. The killer feature is the sharing, which Excel does not do unless Microsoft 365 has greatly changed. The graphics, pivot tables, and functions are entirely sufficienty for cash flow and revenue models.
I've used Google Sheets for years and greatly prefer it over Excel at this point. The killer feature is the sharing, which Excel does not do unless Microsoft 365 has greatly changed. The graphics, pivot tables, and functions are entirely sufficienty for cash flow and revenue models.
Seriously doubt Google Sheets lacks powerful features that Excel has (except niche features). Why would the developers intentionally cripple their product? It's not like any common operations on table data is too challenging for the Sheets team to implement. If there's any real difference between the softwares it's in their respective focus.
I really despise Microsoft (from an irrational place) and even I have to agree that Sheets is relatively crippled.
Even basic chart types are not supported, I think it might be due to limits of what's possible in the browser.
Granted though, excel can't backed on bigquery.
Even basic chart types are not supported, I think it might be due to limits of what's possible in the browser.
Granted though, excel can't backed on bigquery.
Google Sheets is nowhere close to Excel in terms of features, and not just niche ones. The biggest being tables, IMO.
Not intentionally cripple. It takes years to get to feature parity even with laser focus. If that focus isn't there (deciding Sheets has a slightly different positioning in the market for instance) it's not getting to feature parity.
Excel has some functionality that Google Sheets are missing that is used in this particular use case. More specifically, it has a primitive called Table. After you set up your data as Tables, you can then reference whole columns as Table1[Column1] and it also fills your formulas down as you add more rows.
I don't want to defend Excel too much, as it is not ideal in many ways. Nevertheless, over time, I found myself using it more and more to prototype and visualize data. With magic features like Pivot charts, Flash fill, and Data tables you can hammer out a one-off "app" in a matter of minutes.
I don't want to defend Excel too much, as it is not ideal in many ways. Nevertheless, over time, I found myself using it more and more to prototype and visualize data. With magic features like Pivot charts, Flash fill, and Data tables you can hammer out a one-off "app" in a matter of minutes.
Interesting. It definitely seems like that would fix a lot of my issues, which prompts the question of why Google hasn't built this functionality
Google has, for better or worse (and I'd argue usually for better), created a 90-95% product across Word/Sheets/Slides. Every now and then I run into limitations but the sparseness is mostly a win. That said, every now and then I run into a limitation (perhaps especially with Excel) that I have to either work around or use the Microsoft product.
Someone who has extensive experience in Excel but only basic knowledge in software development will quickly realize that they can develop something in Excel faster than they can build it in a CRUD app.
I think this raises another point. An apples-to-apples comparison is only possible if you can do both yourself. If you're not a coder, then you're comparing creating a spreadsheet with managing coding. And the latter is even more difficult to learn than coding itself.
I'm in the middle ground -- can code until the cows come home, but can't manage a coding project to save my life. I am extremely sympathetic when someone has to manage me coding. I'm always thinking to myself: How can I avoid turning this into a nightmare for them?
I'm in the middle ground -- can code until the cows come home, but can't manage a coding project to save my life. I am extremely sympathetic when someone has to manage me coding. I'm always thinking to myself: How can I avoid turning this into a nightmare for them?
I certainly get that, but I'm primarily pointing out that as a non-layman from the software side, it doesn't seem like a particularly amazing tool. It certainly could be the case that it's extremely good for non-programmers, I was simply pointing out that I naively think it's not very well designed for those usecases.
Can you give a more detailed description of what you were trying? I can't know for sure, but it sounds like Excel can easily handle what you've described, if you use it right.
>the entire paradigm revolves around knowing the shape of your data in advance.
How exactly do you program without knowing the shape of your data in advance? You need to know your database columns, or your JSON schema, etc.
>(I was using Google Sheets, but I don't think Excel would have been much different).
It would have been very different, because Excel has tables and Powerquery and Google Sheets doesn't.
>since it's a variable number of rows returned, it is difficult to then operate on that data without filling your formulas down for some indeterminate number of rows.
Were you using dynamic array formulae? They can handle the old problem of needing to fill down formulae to an arbitrary depth. Or again, tables.
Programmers routinely underestimate Excel. Unlike most Microsoft products, it has improved year on year over the past few decades. There are heaps of great power-user features they keep introducing. The skill ceiling is very high .. not as high as proper software engineering, but still damned high.
It also really annoys me when I see Linux/FOSS partisans tell Windows normies "oh you can do everything you can do in Excel in LibreOffice Calc" -- no you fucking well cannot. (And I use Linux on my personal computers full time).
>the entire paradigm revolves around knowing the shape of your data in advance.
How exactly do you program without knowing the shape of your data in advance? You need to know your database columns, or your JSON schema, etc.
>(I was using Google Sheets, but I don't think Excel would have been much different).
It would have been very different, because Excel has tables and Powerquery and Google Sheets doesn't.
>since it's a variable number of rows returned, it is difficult to then operate on that data without filling your formulas down for some indeterminate number of rows.
Were you using dynamic array formulae? They can handle the old problem of needing to fill down formulae to an arbitrary depth. Or again, tables.
Programmers routinely underestimate Excel. Unlike most Microsoft products, it has improved year on year over the past few decades. There are heaps of great power-user features they keep introducing. The skill ceiling is very high .. not as high as proper software engineering, but still damned high.
It also really annoys me when I see Linux/FOSS partisans tell Windows normies "oh you can do everything you can do in Excel in LibreOffice Calc" -- no you fucking well cannot. (And I use Linux on my personal computers full time).
> It also really annoys me when I see Linux/FOSS partisans tell Windows normies "oh you can do everything you can do in Excel in LibreOffice Calc" -- no you fucking well cannot. (And I use Linux on my personal computers full time).
It seems like your argument is that alternatives don't have PowerQuery. That might be true (I don't even know what it is), but isn't that like saying Linux can't compete with Windows, because it doesn't have Internet Explorer? I mean, it doesn't, but there are excellent alternatives that can accomplish exactly the same task.
Unless you can come up with an example task that can't be completed, then it seems like it's just a matter of opinion which is the better solution.
As far as I know both Google Sheets and LibreOffice have SQL and Pivot Tables, and -- believe it or not -- Lotus 1-2-3 had "/Data queries" in 1989. Naturally, the queries possible in 1-2-3 were limited, but you really could query large tables for things like "[Date] <= #date(2017,6,1)", which is the first result I got from typing "Power Query example statement" into Google.
It seems like your argument is that alternatives don't have PowerQuery. That might be true (I don't even know what it is), but isn't that like saying Linux can't compete with Windows, because it doesn't have Internet Explorer? I mean, it doesn't, but there are excellent alternatives that can accomplish exactly the same task.
Unless you can come up with an example task that can't be completed, then it seems like it's just a matter of opinion which is the better solution.
As far as I know both Google Sheets and LibreOffice have SQL and Pivot Tables, and -- believe it or not -- Lotus 1-2-3 had "/Data queries" in 1989. Naturally, the queries possible in 1-2-3 were limited, but you really could query large tables for things like "[Date] <= #date(2017,6,1)", which is the first result I got from typing "Power Query example statement" into Google.
Do Sheets and LibreOffice have Solver? No. They don’t.
Lotus 1-2-3 had a Solver (two actually, one called "Solver" and one called "What-If") in 1989. Yes, LibreOffice has one too.
Does Microsoft tell their customers they invented these features? Just a few days ago I saw a Windows developer who thought Microsoft invented conditional breakpoints.
Does Microsoft tell their customers they invented these features? Just a few days ago I saw a Windows developer who thought Microsoft invented conditional breakpoints.
LibreOffice does have an solver. but i have no idea how it compares to others.
>It seems like your argument is that alternatives don't have PowerQuery. That might be true (I don't even know what it is), but isn't that like saying Linux can't compete with Windows, because it doesn't have Internet Explorer? I mean, it doesn't, but there are excellent alternatives that can accomplish exactly the same task.
It's true that Google sheets and LibreOffice don't have Powerquery, and that's a big pain. But the worse thing is that they don't have tables. As in, the "format as table" button in Excel. As in, the bread and butter of anyone who gets serious work done in Excel.
Maybe it's a problem of naming -- "format" makes people think it's just about aesthetics, but actually it imparts real semantic structure onto a rectangular grid of data. It also isn't the same thing as pivot tables, with which they are often confused. It gives the grid a name that you can refer to in formulae, and the columns are named too, with their names living inside the table namespace ("structured references" is what Microsoft calls it). The table automatically expands its boundaries when you start typing a column header to the right of the current columns, and likewise it expands to comprise the row beneath it if you type values into that row. And it has smart indexing: there's special syntax to refer to "this table" and "this row" in formulae.
So you can have say, a table named "ExpensesTable", labelled "Date", "Type of Expense" and "Amount" in columns A:C. Then you can type "Tax" at the top of column D, it will expand the table to include a new blank column for Tax. Then in D2, type
More about structured references: https://support.microsoft.com/en-us/office/using-structured-...
Also the "You Suck At Excel" talk by Joel Spolsky: https://www.youtube.com/watch?v=0nbkaYsR94c
And no, I have no idea why the eggheads at Google don't implement this for Sheets. Maybe Microsoft has a patent on it? Wouldn't surprise me. But this is why you'll have to pry Excel out of spreadsheet jockeys' cold dead hands -- the alternatives don't have this basic thing.
It's true that Google sheets and LibreOffice don't have Powerquery, and that's a big pain. But the worse thing is that they don't have tables. As in, the "format as table" button in Excel. As in, the bread and butter of anyone who gets serious work done in Excel.
Maybe it's a problem of naming -- "format" makes people think it's just about aesthetics, but actually it imparts real semantic structure onto a rectangular grid of data. It also isn't the same thing as pivot tables, with which they are often confused. It gives the grid a name that you can refer to in formulae, and the columns are named too, with their names living inside the table namespace ("structured references" is what Microsoft calls it). The table automatically expands its boundaries when you start typing a column header to the right of the current columns, and likewise it expands to comprise the row beneath it if you type values into that row. And it has smart indexing: there's special syntax to refer to "this table" and "this row" in formulae.
So you can have say, a table named "ExpensesTable", labelled "Date", "Type of Expense" and "Amount" in columns A:C. Then you can type "Tax" at the top of column D, it will expand the table to include a new blank column for Tax. Then in D2, type
=[@[Amount]] * 0.2
=sum(ExpensesTable[Amount])
More about structured references: https://support.microsoft.com/en-us/office/using-structured-...
Also the "You Suck At Excel" talk by Joel Spolsky: https://www.youtube.com/watch?v=0nbkaYsR94c
And no, I have no idea why the eggheads at Google don't implement this for Sheets. Maybe Microsoft has a patent on it? Wouldn't surprise me. But this is why you'll have to pry Excel out of spreadsheet jockeys' cold dead hands -- the alternatives don't have this basic thing.
You need to come up with a task that cannot be accomplished, remember you said "no you fucking well cannot [do everything]", but so far I've seen no examples.
I mean, isn't this just a button that adds some named ranges for you?
You can replicate the exact example you gave with named ranges. If there is something it can do that named ranges can't, then please use that example instead. Similarly, if you think there is something that "Power Query" can do that SQL cannot, then please show that.
I literally use Lotus 1-2-3 for UNIX (I'm not kidding! http://123r3.net).
So far, all of the examples I've seen you give could have been done in 1989 on a VT100 terminal connected to SystemV. You could even write a quick macro in that generates the named ranges from column headers with one keystroke, it would be really trivial.
I mean, isn't this just a button that adds some named ranges for you?
You can replicate the exact example you gave with named ranges. If there is something it can do that named ranges can't, then please use that example instead. Similarly, if you think there is something that "Power Query" can do that SQL cannot, then please show that.
I literally use Lotus 1-2-3 for UNIX (I'm not kidding! http://123r3.net).
So far, all of the examples I've seen you give could have been done in 1989 on a VT100 terminal connected to SystemV. You could even write a quick macro in that generates the named ranges from column headers with one keystroke, it would be really trivial.
>I mean, isn't this just a button that adds some named ranges for you?
No, named ranges don't automatically expand when you add new rows, and they aren't automatically created when you add new columns. And they don't remain in groups, e.g. you can't make a reference like Namedrange1:Namedrange3, but in a table you can do Column1:Column3. Named ranges exist in a global namespace; column references exist in a per-table namespace. The table syntax makes columnwise operations clearer to express in formulae. Let's say you want to refer to the cell in the same row as the current cell, but in a different column: how do you do that if everything is just a named range? You need to do some kind of juggling with indexing and lookups, or else fall back to alphabet soup A1/R1C1 style referencing, because a named range is only good if you want to do an operation on every cell in the range. But that's often not what you want! In tables it's as simple as [@[other column]].
You would know this if you actually read the documentation or watched the video I posted. Or I could just repeat myself again (maybe I will write a macro to automate such tedium).
>Similarly, if you think there is something that "Power Query" can do that SQL cannot, then please show that.
Grab data from a csv file, a JSON file, a SQL database, and an Excel sheet, and combine them all together using a normie-friendly GUI.
Your question doesn't even make sense, it's like a type error. SQL and PowerQuery are not competing technologies, they're complementary.
>You could even write a quick macro in that generates the named ranges from column headers with one keystroke, it would be really trivial.
Yeah and you can also make Dropbox by getting an FTP account, mounting it locally with curlftpfs, and then using SVN or CVS on the mounted filesystem.
Spreadsheets are the only remaining programming system that people not inducted into the Programming Cult use.
No, named ranges don't automatically expand when you add new rows, and they aren't automatically created when you add new columns. And they don't remain in groups, e.g. you can't make a reference like Namedrange1:Namedrange3, but in a table you can do Column1:Column3. Named ranges exist in a global namespace; column references exist in a per-table namespace. The table syntax makes columnwise operations clearer to express in formulae. Let's say you want to refer to the cell in the same row as the current cell, but in a different column: how do you do that if everything is just a named range? You need to do some kind of juggling with indexing and lookups, or else fall back to alphabet soup A1/R1C1 style referencing, because a named range is only good if you want to do an operation on every cell in the range. But that's often not what you want! In tables it's as simple as [@[other column]].
You would know this if you actually read the documentation or watched the video I posted. Or I could just repeat myself again (maybe I will write a macro to automate such tedium).
>Similarly, if you think there is something that "Power Query" can do that SQL cannot, then please show that.
Grab data from a csv file, a JSON file, a SQL database, and an Excel sheet, and combine them all together using a normie-friendly GUI.
Your question doesn't even make sense, it's like a type error. SQL and PowerQuery are not competing technologies, they're complementary.
>You could even write a quick macro in that generates the named ranges from column headers with one keystroke, it would be really trivial.
Yeah and you can also make Dropbox by getting an FTP account, mounting it locally with curlftpfs, and then using SVN or CVS on the mounted filesystem.
Spreadsheets are the only remaining programming system that people not inducted into the Programming Cult use.
I'm happy that you like this syntax, but the claim you made was "no you fucking well cannot [do everything]", not "excel syntax is more fucking beautiful".
I appreciate your advice, but I don't need to watch a video on R1C1 syntax, I literally maintain a spreadsheet :)
It seems like your real claim is that you really like the way Excel does it, nobody can argue with that.
I appreciate your advice, but I don't need to watch a video on R1C1 syntax, I literally maintain a spreadsheet :)
It seems like your real claim is that you really like the way Excel does it, nobody can argue with that.
I have to agree with the OP (_dain_) here. Excel has evolved a lot in the last few years, first the whole Power Query and Power Pivot revolution and now all the functional stuff brought on by Simon Peyton-Jones and his crew like LET expressions and the functional constructs like LAMBDA, MAP, FILTER, ...
There's very little you can't do neatly and efficiently in Excel anymore. Yes you can in principle do those same things in Google Shets, but at what cost of readability?
I don't think it's worth spending much time getting into these arguments because the people arguing against Excel clearly don't know modern Excel very well.
There's very little you can't do neatly and efficiently in Excel anymore. Yes you can in principle do those same things in Google Shets, but at what cost of readability?
I don't think it's worth spending much time getting into these arguments because the people arguing against Excel clearly don't know modern Excel very well.
> I don't think it's worth spending much time getting into these arguments because the people arguing against Excel clearly don't know modern Excel very well.
That's not it at all. Excel has been in active development for over 30 years by a multi-trillion dollar development powerhouse with billions of sales, everybody is aware it's a perfectly competent product.
The dispute is the objective claim that it can do something that alternatives cannot, not the subjective claim that Excel is "neater", or more beautiful, or more user friendly. After 30 years of active development I would hope that Excel has some shortcuts, polish and syntax improvements to streamline common operations. That is not the same as not being able to do something.
I question the claim that it can do something unique, and want to hear an example. When pushed for an example I'm told that only Excel has a Solver, or only Excel has Pivot Tables. That is objectively false.
I don't want to hear about "Power Query" unless it's an example query that cannot be done in SQL. It's a proprietary query language, of course alternatives don't have it. I'm glad you're happy about it, but others might call that "Vendor Lock-in".
https://en.wikipedia.org/wiki/Vendor_lock-in
That's not it at all. Excel has been in active development for over 30 years by a multi-trillion dollar development powerhouse with billions of sales, everybody is aware it's a perfectly competent product.
The dispute is the objective claim that it can do something that alternatives cannot, not the subjective claim that Excel is "neater", or more beautiful, or more user friendly. After 30 years of active development I would hope that Excel has some shortcuts, polish and syntax improvements to streamline common operations. That is not the same as not being able to do something.
I question the claim that it can do something unique, and want to hear an example. When pushed for an example I'm told that only Excel has a Solver, or only Excel has Pivot Tables. That is objectively false.
I don't want to hear about "Power Query" unless it's an example query that cannot be done in SQL. It's a proprietary query language, of course alternatives don't have it. I'm glad you're happy about it, but others might call that "Vendor Lock-in".
https://en.wikipedia.org/wiki/Vendor_lock-in
[deleted]
Your comment about FOSS is spot on. While I'm very aware that Google Sheets is not OSS, it felt much more amenable to me than Excel (and I'm sure Excel's online free version isn't particularly fantastic anyway, though it may be better than Sheets from what people are saying here).
> How exactly do you program without knowing the shape of your data in advance? You need to know your database columns, or your JSON schema, etc.
This was a bit overloaded in my opinion, as in spreadsheets world, "shape" includes the number of rows, hence my comments. I know that the column layout needs to be known.
> Were you using dynamic array formulae
I looked into it, but couldn't figure out how to handle them without introducing a massive amount of formula duplication. The best I could figure out how to do was to do a single large FILTER (which is dynamic array) and doing a fill down on my other transformation formulas from there. I blacked out the rows past the end of the FILTER using conditional formatting rules (which felt very stupid to do, but I couldn't find anything better).
> The skill ceiling is very high
I don't doubt you, but if you can't discover the functionality, it might as well not exist. Admittedly I was clearly using the inferior tool, but in my searching for solutions I much more readily found Google's documentation over Excel's.
I also realize I'm not in the position of being forced into a corner; as most of us on this forum could, I just wave my magic wand and write the software to solve my problems. I imagine those who don't have that ability available to them will do "crazier and crazier" things to figure out how to accomplish their work in Excel, and therefore will learn much better ways than I have in my little experience with it.
----
I was building a tool to track the completion of finding parts for a given Lego set. You enter the set ID, it pulls the parts list for that set (Rebrickable nicely offers their database as a set of CSVs https://rebrickable.com/downloads/) and formats it nicely for consumption.
> How exactly do you program without knowing the shape of your data in advance? You need to know your database columns, or your JSON schema, etc.
This was a bit overloaded in my opinion, as in spreadsheets world, "shape" includes the number of rows, hence my comments. I know that the column layout needs to be known.
> Were you using dynamic array formulae
I looked into it, but couldn't figure out how to handle them without introducing a massive amount of formula duplication. The best I could figure out how to do was to do a single large FILTER (which is dynamic array) and doing a fill down on my other transformation formulas from there. I blacked out the rows past the end of the FILTER using conditional formatting rules (which felt very stupid to do, but I couldn't find anything better).
> The skill ceiling is very high
I don't doubt you, but if you can't discover the functionality, it might as well not exist. Admittedly I was clearly using the inferior tool, but in my searching for solutions I much more readily found Google's documentation over Excel's.
I also realize I'm not in the position of being forced into a corner; as most of us on this forum could, I just wave my magic wand and write the software to solve my problems. I imagine those who don't have that ability available to them will do "crazier and crazier" things to figure out how to accomplish their work in Excel, and therefore will learn much better ways than I have in my little experience with it.
----
I was building a tool to track the completion of finding parts for a given Lego set. You enter the set ID, it pulls the parts list for that set (Rebrickable nicely offers their database as a set of CSVs https://rebrickable.com/downloads/) and formats it nicely for consumption.
I wrote another comment that also answers yours for the most part: https://news.ycombinator.com/item?id=32365128
tldr your problem with Excel is that you don't grok tables. They eliminate the need to know the number of rows when writing your formulae.
I don't actually have Excel installed on the machine I'm using to type this, so I can't put my money where my mouth is like the vim guy did[1]. But I'm fairly sure you can achieve your goal with table references and liberal use of the XLOOKUP and FILTER functions. It'll get a little hairy since you have to go from Set -> Inventories -> Inventory Parts -> Parts, so maybe a bit of nesting. But I think doable. The LET function also helps to reduce formula complexity, it lets you make lexically-scoped variables inside your formulae. Use "data validation" to make a dropdown menu for the set names.
[1] https://stackoverflow.com/a/1220118
tldr your problem with Excel is that you don't grok tables. They eliminate the need to know the number of rows when writing your formulae.
I don't actually have Excel installed on the machine I'm using to type this, so I can't put my money where my mouth is like the vim guy did[1]. But I'm fairly sure you can achieve your goal with table references and liberal use of the XLOOKUP and FILTER functions. It'll get a little hairy since you have to go from Set -> Inventories -> Inventory Parts -> Parts, so maybe a bit of nesting. But I think doable. The LET function also helps to reduce formula complexity, it lets you make lexically-scoped variables inside your formulae. Use "data validation" to make a dropdown menu for the set names.
[1] https://stackoverflow.com/a/1220118
The problems you mentioned are solved by Excel using tables, which are named, variable in size, and can be referenced by column names instead of addresses.
Tables can also be joined and queried using Power Query.
Excel is still 100x more powerful and sophisticated than Sheets is.
Tables can also be joined and queried using Power Query.
Excel is still 100x more powerful and sophisticated than Sheets is.
I'm a happy customer of https://exploratory.io/ - it's a very user-friendly interface on top of R and I think you might find it helpful.
> it is very non intuitive to write a formula that retrieves all the rows in another sheet that match this rule
You can retrieve an entire range of data with a single formula in either excel or Google sheets. The formula is caller FILTER https://support.microsoft.com/en-us/office/filter-function-f...
You can retrieve an entire range of data with a single formula in either excel or Google sheets. The formula is caller FILTER https://support.microsoft.com/en-us/office/filter-function-f...
That's exactly what I did. Now separate out some columns and perform some additional transformation on that FILTERed data. Can you do it without repeating yourself (duplicating the FILTER statement, or any of the other transformations you need to do, besides just filling down a column). Can you perform these transformations only on the row height of the data, and not have extra rows with broken formulas?
I honestly wouldn't even be surprised if the functionality to do the above does exist, but for all of my searching I couldn't find it.
I honestly wouldn't even be surprised if the functionality to do the above does exist, but for all of my searching I couldn't find it.
This is something I faced in both Excel and GSheets (when working with formulae only). That is the need to repeat myself occasionally.
I will ramble a bit, but for complex data transforms, you can use PowerQuery instead of formulas. For semi-serious programmers like myself, I started doing C# add-ins to Excel using Excel-Dna. There’s also Query Storm. :)
"Several tables and selectively joining them" ... "Enter an id and filter"
Sure sounds like your creating a relational database in a spreadsheet, which is possible but not really the intended purpose?
Sure sounds like your creating a relational database in a spreadsheet, which is possible but not really the intended purpose?
Surely that's what lots of non-developer white collar workers use Excel for? I imagine there's orders of magnitude more people using Excel for data processing rather than Python or R. I'm well aware it's not the best tool for the job, but yet people are using it for purposes such as that. I wanted to learn more about that experience.
I think no. Most people don't do table joins very often. They wouldn't know how. What they'll do instead is create lookups with VLOOKUP or INDEX(MATCH()) to pull in values from other tables into their one master. And once they have the master flat file they'll use a pivot table for group by aggregations.
This is very accurate in my experience, down to the suggested formulas.
It's much simpler than all that. Excel is just easy to use and quick to get results and powerful enough to extend, easy to share with others, and is a broadly accepted file format. That's why it works. Everything else is more complicated, more vendor-locked in and takes longer to get results or requires more technical skills than the average Excel user has. Is it the best for everything? No. But it's damn good enough for a LOT of things. Its survival is proof.
Many people have tried launching an Excel-with-SQL-querying product, but it’s extremely hard to do the UI well. Also products where people write SQL are impossible to insure.
Your task could be trivially done with EasyMorph (https://easymorph.com). We've designed it exactly for such use cases.
Data needs of non-technical people have long been neglected. It was believed that any data wrangling should be done by IT people. So all non-technical people had was Excel. Luckily, the no-code movement finally started addressing that issue with a varying degree of success.
Data needs of non-technical people have long been neglected. It was believed that any data wrangling should be done by IT people. So all non-technical people had was Excel. Luckily, the no-code movement finally started addressing that issue with a varying degree of success.
> the entire paradigm revolves around knowing the shape of your data in advance.
This is true, but you can improve things significantly by using named ranges.
Using `$PAYMENTS` instead of `Sheet2!$B2:$B$21` for your column data and `$TAX_RATE` instead of `$Sheet4!$A$1` clarifies things quite a lot.
You still will need to know how your data is structured (e.g. this is a column that goes down, this is a fixed variable) but it is way more readable.
This is true, but you can improve things significantly by using named ranges.
Using `$PAYMENTS` instead of `Sheet2!$B2:$B$21` for your column data and `$TAX_RATE` instead of `$Sheet4!$A$1` clarifies things quite a lot.
You still will need to know how your data is structured (e.g. this is a column that goes down, this is a fixed variable) but it is way more readable.
I work in financial services and have build prototypes with Excel that are now multi-million dollar revenue providers (implemented properly).
Excel is powerful in this respect because it is a shared experience. Build something with SAS/R/Python, explaining the results is possible but getting buy-in from other teams is harder.
Excel is powerful in this respect because it is a shared experience. Build something with SAS/R/Python, explaining the results is possible but getting buy-in from other teams is harder.
>I was horrified to find that even with the supporting scripting capabilities, the entire paradigm revolves around knowing the shape of your data in advance.
Just record yourself finding the bottom of the data set (Ctrl + down arrow), then take a moment to make the code work in relative terms instead of absolute terms.
Just record yourself finding the bottom of the data set (Ctrl + down arrow), then take a moment to make the code work in relative terms instead of absolute terms.
What do you mean by this? What am I "recording" as the bottom of the data set?
My point was that it is very hard to have a dynamic number of rows feed a proportionate dynamic number of rows. Scripting makes it much simpler, but at least with Google Sheet's scripting, the API seemed pretty lacking for that processing (in the very least, it's very slow, since it's running as a very constrained shared resource).
My point was that it is very hard to have a dynamic number of rows feed a proportionate dynamic number of rows. Scripting makes it much simpler, but at least with Google Sheet's scripting, the API seemed pretty lacking for that processing (in the very least, it's very slow, since it's running as a very constrained shared resource).
Excel let's you create macros by hitting "record" and then doing the operations yourself. It'll generate the code to replicate the impact of your inputs.
I don’t know about GSheets, but Excel has dynamic arrays (of variable lengths) since at least two years ago. Or a couple of lines of VBA can accomplish the same.
> but I quickly realized that I could spin up a whole CRUD webapp for this problem faster than I, someone who understands indexing and windowing and such, could build it in a spreadsheet.
What would you use to build a CRUD app faster than an excel app
(Not the OP, but) A headless CMS is pretty great. Or Airtable.
I don’t all that much about excel but I remember being blown away as a 17 year old kid at my first job when someone showed me pivot tables.
as somebody who is very experienced with both writing crud apps and excel there's still a lot of cases where excel wins. a big one for me is making m&a models, stuff like that, excel is better bc you can organize view and move around data, re run calculations, whatever else in a tighter loop. and it's better at taking data in kinda inconsistent formats. there's a reason it is still king of IB, PE, all the finance world.
this is because excel was "low/no code" before it was a tech meme with vc money.
this is because excel was "low/no code" before it was a tech meme with vc money.
Exactly right about the seeing the data and rerunning calculations.
The programmers who look down their nose at Excel are doing the exact same thing in Jupyter Notebooks and in their REPLs.
>I can see the state of any given variable at any time >I can rerun the same function on different inputs, or different functions on the same inputs >All without having to restart my program!
Remind you of anything?
Anybody who does print(df.head()) is pining for Excel…
The programmers who look down their nose at Excel are doing the exact same thing in Jupyter Notebooks and in their REPLs.
>I can see the state of any given variable at any time >I can rerun the same function on different inputs, or different functions on the same inputs >All without having to restart my program!
Remind you of anything?
Anybody who does print(df.head()) is pining for Excel…
Have you heard of microsoft Access?
This stings. I work as one of the less-technical people in group of developers and maintain a fairly extensive flat-file database in Excel. I wanted to re-implement it in Access, but my boss said "No," because he was afraid if I left he wouldn't be able to find anyone to maintain it.
Also, Access isn't available with all Microsoft Office licenses. Excel is.
Also, Access isn't available with all Microsoft Office licenses. Excel is.
Did you use “Query”?
Excel runs the world. Every large company claiming to be ERP-native is telling lies. Shit gets done in Excel, and is then exported back into ERP, if at all.
Things you would consider quite irresponsible to be done in Excel, is done in Excel regardless. Indeed, that annual financial report of a Fortune 500 boils down to financialresults_v2_nowaitonemorechange_final_FINAL_withcomments_official.xlsx"
My g/f works for one of the leading ERP providers. I won't tell you which one but it starts with an S and ends with AP. They're dogfooding their own ERP but employees prefer Excel.
It's like gravity, just stop resisting.
Things you would consider quite irresponsible to be done in Excel, is done in Excel regardless. Indeed, that annual financial report of a Fortune 500 boils down to financialresults_v2_nowaitonemorechange_final_FINAL_withcomments_official.xlsx"
My g/f works for one of the leading ERP providers. I won't tell you which one but it starts with an S and ends with AP. They're dogfooding their own ERP but employees prefer Excel.
It's like gravity, just stop resisting.
Never, I will never stop resisting excel.
I wonder how many of these same companies end up with massive errors in their excel docs because of lack of formula control, input validation, and a whole host of other controls that ERP's are designed to prevent, that excel will happily do
Honestly I think that is why user want Excel, properly written software prevents the user from doing stupid things, excel does not
Human Error is rampant in Excel Docs, I remember a few years ago we had a project to convert some excel docs people were using into a web app, the number of mathematical errors, and invalid controls we found was unreal
I wonder how many of these same companies end up with massive errors in their excel docs because of lack of formula control, input validation, and a whole host of other controls that ERP's are designed to prevent, that excel will happily do
Honestly I think that is why user want Excel, properly written software prevents the user from doing stupid things, excel does not
Human Error is rampant in Excel Docs, I remember a few years ago we had a project to convert some excel docs people were using into a web app, the number of mathematical errors, and invalid controls we found was unreal
Us devs like to complain about Excel because it's sloppy, but ERPs tend to be so awful to use. It's the same as thinking you know how to write clean code, then balking at everyone else's code. Excel sheets are just another "someone else's dirty code".
Given the general quality of software in the world today, I would expect that most ERPs are just as bad as Excel sheets under the hood.
Given the general quality of software in the world today, I would expect that most ERPs are just as bad as Excel sheets under the hood.
We have non-excel financial reports via a web app, but of course the actual software requirements were gathered in an excel file
It is not vastly easier to use than Excel, but certainly nice when there’s a web app to find instead of a loose file
It is not vastly easier to use than Excel, but certainly nice when there’s a web app to find instead of a loose file
I know several scientists with PhDs whose workflow involved Windows 7 sticky notes to an extent that would make the average HN reader pull out their hair in shock.
We're talking about well-published in Nature experts.
Sure, eventually they publish results with LaTeX, but a lot of intermediate steps will involve Excel/Word or even something more primitive.
It is what it is.
We're talking about well-published in Nature experts.
Sure, eventually they publish results with LaTeX, but a lot of intermediate steps will involve Excel/Word or even something more primitive.
It is what it is.
Your story reminds me of this research lab with state-of-the-art 10 Trillion FPS camera. From the video they apparently are still using Windows 7 for the measurement set up [1]. They're publishing in Nature and most probably using Excel for some of their data processing workflow.
My former research lab is, however, mainly using Igor Pro software running on Windows. It is waves based professional data processing software tool that has friendly GUI and intuitive table interfaces not unlike Excel but geared towards scientists and engineers [2].
[1]Filming the Speed of Light at 10 Trillion FPS:
https://youtu.be/7Ys_yKGNFRQ
[2]IGOR Pro:
https://en.m.wikipedia.org/wiki/IGOR_Pro
My former research lab is, however, mainly using Igor Pro software running on Windows. It is waves based professional data processing software tool that has friendly GUI and intuitive table interfaces not unlike Excel but geared towards scientists and engineers [2].
[1]Filming the Speed of Light at 10 Trillion FPS:
https://youtu.be/7Ys_yKGNFRQ
[2]IGOR Pro:
https://en.m.wikipedia.org/wiki/IGOR_Pro
Well, theres one issue: everybody hates SAP.
You might decide to go to "cash in the mattress" if you realized how much of our modern banking system runs on excel.
Except for the back-end, which is COBOL.
Except for the back-end, which is COBOL.
fr lol everybody who's worked in IB knows this. you go look in a VDR and it's like 99% excel files and PDFs. although for smaller businesses it's usually a quickbooks export or whatever, but whenever people need to actually get shit done or do something SAP or Oracle or whatever doesn't do right or takes too long.
Excel is what you hope your competition is using.
Hey, I work on Excel at Microsoft and wanted to say: if anyone here has any feature requests they want escalated - write them here and I will bring them up.
P.S we actually do read all the feedback people leave in the feedback box - it goes mostly straight to the devs.
P.S we actually do read all the feedback people leave in the feedback box - it goes mostly straight to the devs.
Ok, besides python support... The thing I'd like more than anything:
A better way to edit cell with really long function calls.
If nothing else, add color coded parenthesis to the bar at the top and not just in the cell.
Like, sometimes you just need some if/else statements... but try to parse and edit even something fairly simple like:
=IF(AND(LongExpression > 3, Other_longexpression<5),AnotherLongExpression, IF(AND(LongExpression>5,Other_longexpression<10), AnotherLongExpression, 0))
Even with the color-coded parentheses, this is really hard! And God Forbid all those "LongExpression" have a bunch of parenthesis and PEMDAS that needs to be respected.
It's... really goddamn tedious. I lost track of the parenthesis while writing that in this window ...However, if I could just have a little popout window where I could add arbitrary new/lines and spaces, that would make a difficult thing into something downright enjoyable and productive. Something like:
A better way to edit cell with really long function calls.
If nothing else, add color coded parenthesis to the bar at the top and not just in the cell.
Like, sometimes you just need some if/else statements... but try to parse and edit even something fairly simple like:
=IF(AND(LongExpression > 3, Other_longexpression<5),AnotherLongExpression, IF(AND(LongExpression>5,Other_longexpression<10), AnotherLongExpression, 0))
Even with the color-coded parentheses, this is really hard! And God Forbid all those "LongExpression" have a bunch of parenthesis and PEMDAS that needs to be respected.
It's... really goddamn tedious. I lost track of the parenthesis while writing that in this window ...However, if I could just have a little popout window where I could add arbitrary new/lines and spaces, that would make a difficult thing into something downright enjoyable and productive. Something like:
=IF(
AND(
LongExpression > 3,
Other_longexpression<5
),
AnotherLongExpression,
IF(
AND(
LongExpression>5,
Other_longexpression<10
),
AnotherLongExpression,
0
)
)
Do you know about the LET function? It lets you make lexically scoped variables. And the IFS function is basically an if-elif(-elif...)-else chain, eliminating nested IF. So your example would be:
=LET(
foo, LongExpression,
bar, OtherLongExpression,
baz, AnotherLongExpression,
bax, YetAnotherLongExpression
IFS(
AND(foo > 3, bar < 5), baz,
AND(foo > 5, bar < 10), bax,
TRUE, 0))
Hey, this is all incredibly helpful!
LET alone will be very helpful for all the empirical fluid mechanics formulas I have to deal with.
Thanks a lot friend!
Also, just out of curiosity, why do you recommend that particular bracket style?
LET alone will be very helpful for all the empirical fluid mechanics formulas I have to deal with.
Thanks a lot friend!
Also, just out of curiosity, why do you recommend that particular bracket style?
Vertical space is at a premium, no sense in having lines with nothing but a single close paren. You don't want to drag down the formula bar and start hearing the Star Wars title crawl music in your head.
It's worth checking out the big alphabetical list of formulae, there's a lot of things that many people don't know are there. Look at the ones with the "Office 365" or "2019" labels for recently added ones (although annoyingly those are icons rather than text so you can't ctrl-f):
https://support.microsoft.com/en-us/office/excel-functions-a...
Also check out my other comment in this thread about tables if you don't already know what those are all about. And Powerquery too.
It's worth checking out the big alphabetical list of formulae, there's a lot of things that many people don't know are there. Look at the ones with the "Office 365" or "2019" labels for recently added ones (although annoyingly those are icons rather than text so you can't ctrl-f):
https://support.microsoft.com/en-us/office/excel-functions-a...
Also check out my other comment in this thread about tables if you don't already know what those are all about. And Powerquery too.
FYI, the formula bar in Excel is resizable already.
Expand the bar (or drag the vertical resize handle at its bottom edge), and then you can use alt+enter to insert a newline.
Expand the bar (or drag the vertical resize handle at its bottom edge), and then you can use alt+enter to insert a newline.
It's like coding in an adversarial text window -- the font isn't monospace, there's no syntax highlighting, no auto-indent based on parenthesis, and when you accidentally hit enter (instead of ctrl+enter) you leave the text editor (and then it'll pop up with complaints about your function not being parseable). Also, dragging the size of the edit box bigger means you won't get much of a view of your spreadsheet -- it would probably be nice to attach the edit window on the right (if it was turned into a proper formula editor).
+1 for python support. getting vba versions right for stuff that expects to interface through it can be a bitch if some vendor doesn't update stuff. you pay some stupid amount for a financial data product then it's like "lol this doesn't work with your version of office install 2016". not really excel dev's but python would be nice.
+1 for color coding in top formula bar
> If nothing else, add color coded parenthesis to the bar at the top and not just in the cell.
This seems like a simple enough suggestion - I will pass on the rest but let me see if this is something we can fit to the next FHL.
This seems like a simple enough suggestion - I will pass on the rest but let me see if this is something we can fit to the next FHL.
Great suggestion, but don’t do it Microsoft. This is one of my signals for when to move on to another tool so if you implement this I’d stay in Excel longer!
I regularly use gnumeric for my serious spreadsheet work because Excel can't keep up, and I'm always a little surprised when I have to send a spreadsheet to someone and I find yet another thing that Excel doesn't do; I haven't kept a written list but here's a few off the top of my head (of varying levels of seriousness):
- Excel still gets very confused if you have different files with the same filenames in different directories. At one point it would even, if you crashed while editing one `grades.xlsx` file and went to edit a different `grades.xlsx` on restarting, it would restore the new one from the old swap file, silently clobbering data.
- Last I checked, Excel can't do a lot of very basic data graphing (histograms are the ones that I've run into most often).
- Some versions of Excel (the web one, I think) will just silently not format text that is rotated, making some spreadsheets completely illegible
- I got immediately attached to CSE formulas once I discovered them---they do a lot of things I'd always thought I had to build a custom program for---but 90% of the time when I build and debug something in gnumeric with a CSE formula, it works just as I expected based on experience with abstraction and data structures in other languages, but then when I bring it over to Excel to share with other people, one or more of the Excel functions just don't work properly when lifted over arrays. Then I have to go create an explicit area of the sheet (or another sheet) for my intermediate data and copy formulas to make the computation work, ugh. I really want every single function that normally takes non-range arguments and produces a single value to map over a provided range and produce an array when dropped in a CSE formula. (PS to everyone: if you've never crossed paths with "Control-Shift-Enter formulas", look them up and they'll change your life)
Good to know about the feedback box, though.
- Excel still gets very confused if you have different files with the same filenames in different directories. At one point it would even, if you crashed while editing one `grades.xlsx` file and went to edit a different `grades.xlsx` on restarting, it would restore the new one from the old swap file, silently clobbering data.
- Last I checked, Excel can't do a lot of very basic data graphing (histograms are the ones that I've run into most often).
- Some versions of Excel (the web one, I think) will just silently not format text that is rotated, making some spreadsheets completely illegible
- I got immediately attached to CSE formulas once I discovered them---they do a lot of things I'd always thought I had to build a custom program for---but 90% of the time when I build and debug something in gnumeric with a CSE formula, it works just as I expected based on experience with abstraction and data structures in other languages, but then when I bring it over to Excel to share with other people, one or more of the Excel functions just don't work properly when lifted over arrays. Then I have to go create an explicit area of the sheet (or another sheet) for my intermediate data and copy formulas to make the computation work, ugh. I really want every single function that normally takes non-range arguments and produces a single value to map over a provided range and produce an array when dropped in a CSE formula. (PS to everyone: if you've never crossed paths with "Control-Shift-Enter formulas", look them up and they'll change your life)
Good to know about the feedback box, though.
I don't want this to sound snarky but I don't know how to phrase it better. How is the excel team going at fixing the calculation bugs nowadays after wilfully ignoring them for deacdes? Do you have the management buy in to calculate correctly given that's kind of what excel is meant to do?
https://www.tandfonline.com/doi/abs/10.1198/tas.2011.09076
Sometime in the early to mid noughts I recall MS announcing they'd fixed rand() returning a random number between 0 and 1. Someone filled a page with =rand(), set a conditional format, it recalculate a few times and watched many cells turning red showing a negative number. I replicated this at the time. Still?
www.gnumeric.org is what I've used when needing a spreadsheet because of those issues and the refusal to fix them. Annoying ui changes happened instead...
https://www.tandfonline.com/doi/abs/10.1198/tas.2011.09076
Sometime in the early to mid noughts I recall MS announcing they'd fixed rand() returning a random number between 0 and 1. Someone filled a page with =rand(), set a conditional format, it recalculate a few times and watched many cells turning red showing a negative number. I replicated this at the time. Still?
www.gnumeric.org is what I've used when needing a spreadsheet because of those issues and the refusal to fix them. Annoying ui changes happened instead...
Wait really?
Well if thats the case, making VLookups be able to use any column as an input or output, rather than being limited to having the input on the left and the output on the right.
Ideally I'd be able to have 2 additional parameters, one that would indicate the column where the input value is to be found, and another that would indicated the column from which the output value would come from.
I know there are some work arounds, but this would really simplify my life!
Well if thats the case, making VLookups be able to use any column as an input or output, rather than being limited to having the input on the left and the output on the right.
Ideally I'd be able to have 2 additional parameters, one that would indicate the column where the input value is to be found, and another that would indicated the column from which the output value would come from.
I know there are some work arounds, but this would really simplify my life!
You may already know this, but if not and for others: my Excel files became a lot more manageable, maintainable, and readable when I switched from _LOOKUP() functions to the INDEX-MATCH pattern, and in particular using the pattern with Tables.
Basically
- Put the data to be looked up in a named Excel Table with a short, meaningful name. `data` is a good default name, but something with more meaning is better.
- Put the lookup 'results' in an Excel Table (naming optional but recommended). The output will be one column of the table, with one of the other columns used as input.
- Construct the output formula like `=INDEX(data[[value_column]], MATCH([@[input_column]], data[[lookup_column]], 0))`.
- (Optional) Put all formulas at the far right of the results table, so that you can copy new data into the left side easily without overwriting the formulas.
The MATCH finds the first row in `data` that has the lookup value from `input_column` (in the current table) in the `lookup_column` (in the data table). The INDEX grabs the value from `value_column` of the `data` table in that row.
Using Excel Tables helps by making the formulas more readable, and resistant to change. If new columns are added or removed the formulas continue to work (not true for how most _LOOKUP formulas are written), and the formula gets copied down to new rows as you add them.
You can switch to row lookups if needed (though you can't really use Excel Tables anymore)
if you need dynamic lookups you can specify both row and column as MATCHes in the INDEX formula (and INDEX against the whole `data` table instead of just one column). Something like `=INDEX(data, MATCH([@[input_column]], data[[lookup_column]], 0), MATCH([@[column_name]], data[#Headers], 0))`.
Basically
- Put the data to be looked up in a named Excel Table with a short, meaningful name. `data` is a good default name, but something with more meaning is better.
- Put the lookup 'results' in an Excel Table (naming optional but recommended). The output will be one column of the table, with one of the other columns used as input.
- Construct the output formula like `=INDEX(data[[value_column]], MATCH([@[input_column]], data[[lookup_column]], 0))`.
- (Optional) Put all formulas at the far right of the results table, so that you can copy new data into the left side easily without overwriting the formulas.
The MATCH finds the first row in `data` that has the lookup value from `input_column` (in the current table) in the `lookup_column` (in the data table). The INDEX grabs the value from `value_column` of the `data` table in that row.
Using Excel Tables helps by making the formulas more readable, and resistant to change. If new columns are added or removed the formulas continue to work (not true for how most _LOOKUP formulas are written), and the formula gets copied down to new rows as you add them.
You can switch to row lookups if needed (though you can't really use Excel Tables anymore)
if you need dynamic lookups you can specify both row and column as MATCHes in the INDEX formula (and INDEX against the whole `data` table instead of just one column). Something like `=INDEX(data, MATCH([@[input_column]], data[[lookup_column]], 0), MATCH([@[column_name]], data[#Headers], 0))`.
I was going to reply with this too. Joel Spolsky explains it well https://youtu.be/0nbkaYsR94c?t=1824
Might be what you’re after: https://support.microsoft.com/en-us/office/xlookup-function-...
Well half that is already done(the output!)
Bring back Access. I understand it needs to be rebuilt, so probably build it on top of sqlite. There's nothing (popular) in the niche left by Access.
I love excel, but Access is a nightmare by comparison. It sits in exactly the wrong spot on the power/usability curve. It it's super easy to create a crappy database and hard to make a good one.
Regular expressions in formulas. (Preferably Perl-style.)
Golfing around with LEFT and MID and LEN and RIGHT gets old after a while, and I believe that regular expressions are much easier to explain to someone than the aforementioned nested formulas. (I have some Excel teaching experience.) Not just matching, regex string replace too.
Also, there's always room for adding new options when importing CSVs!
Golfing around with LEFT and MID and LEN and RIGHT gets old after a while, and I believe that regular expressions are much easier to explain to someone than the aforementioned nested formulas. (I have some Excel teaching experience.) Not just matching, regex string replace too.
Also, there's always room for adding new options when importing CSVs!
Best to document the steps with intermediate LET expressions.
Wishlist:
A way to truely, honestly, for the love of god, please, I beg you for mercy, force all pivot tables to fully refresh everything about themselves — their data, caches, retained items, etc.
Better pivot table value formatting: use the formatting from the source data set, let me format multiple value columns at once, or apply formatting from value cells to the value columns themselves.
Please let me hide everything from a pivot table except for value columns. There are many scenarios where I would like to insert two pivot tables right next to each other, then have a third columns that refers to their cells for a calculation. I don’t need any of the other pivot table options to be available to the user. Dynamic array and lambda functions are not a substitute, because they do not cache results, which causes significant performance problems.
A workbook level option to open the workbook in a new process that doesn’t allow interaction with other workbooks. Sometimes, I build computationally intensive standalone workbooks that my users hate to have open, because they degrade performance for all of their other open workbooks. They have to resort to using excel online (or the outlook web preview) to be able to have my workbook open for reference while working on something else.
Freeze(x) or Staticize(x): a function that evaluates once and retains its value. I know a similar effect is possible by enabling iterative calculation, but that feels hacky and I don’t know what else is affected by turning iterative calculation on (the fact that it is disabled by default implies significant consequences).
In Power Query, a way to append the content of a table to another table, on every Refresh All. This would make it much easier to create snapshots and temporal reports. E.g. I want to know what the value of all sales orders as they were reported each day, verses what I can infer from the database today.
I love all the investment in Excel! Are you hiring?
A way to truely, honestly, for the love of god, please, I beg you for mercy, force all pivot tables to fully refresh everything about themselves — their data, caches, retained items, etc.
Better pivot table value formatting: use the formatting from the source data set, let me format multiple value columns at once, or apply formatting from value cells to the value columns themselves.
Please let me hide everything from a pivot table except for value columns. There are many scenarios where I would like to insert two pivot tables right next to each other, then have a third columns that refers to their cells for a calculation. I don’t need any of the other pivot table options to be available to the user. Dynamic array and lambda functions are not a substitute, because they do not cache results, which causes significant performance problems.
A workbook level option to open the workbook in a new process that doesn’t allow interaction with other workbooks. Sometimes, I build computationally intensive standalone workbooks that my users hate to have open, because they degrade performance for all of their other open workbooks. They have to resort to using excel online (or the outlook web preview) to be able to have my workbook open for reference while working on something else.
Freeze(x) or Staticize(x): a function that evaluates once and retains its value. I know a similar effect is possible by enabling iterative calculation, but that feels hacky and I don’t know what else is affected by turning iterative calculation on (the fact that it is disabled by default implies significant consequences).
In Power Query, a way to append the content of a table to another table, on every Refresh All. This would make it much easier to create snapshots and temporal reports. E.g. I want to know what the value of all sales orders as they were reported each day, verses what I can infer from the database today.
I love all the investment in Excel! Are you hiring?
I'm a glass half full kinda of kind, but for excel, the glass is Feb 1st. Please, types would really help. Maybe as a property of named ranges ?
Hi, I know youve gotten bombarded here - but the single greatest low-effort high-reward change y'all could make would be allowing data labels to be outside the data (immediately above or below). Currently you can only do base, mid, or top. In finance (my field) pretty much all charts have a second ghost bar with the same numeric value put above any bar chart, for the sole purpose of removing the filled color and putting the data label in the base (i.e. immediately above the real bar).
You would be a hero to thousands and thousands of junior bankers if you made this change lol.
You would be a hero to thousands and thousands of junior bankers if you made this change lol.
Please bring back CTRL+SHIFT+V as paste values only
Ctrl-Shift-S? What did it ever do to hurt anyone that it had to go?
I still use the old excel way: Alt+E, A, V
The solution for this (in most MS Office products) is CTRL+V then CTRL then T. Annoying, I know.
Lambdas are awesome but the ui and their management could use some work.
It would be great to have a “script” view to manage defined names instead of hacking them one by one.
Single line entry isn’t efficient and the default assumption that cursor keys navigate a sheet instead of the formula box, is annoying. I find myself copying formulas out of excel, modifying them in a text editor, and pasting them back.
It would be great to have a “script” view to manage defined names instead of hacking them one by one.
Single line entry isn’t efficient and the default assumption that cursor keys navigate a sheet instead of the formula box, is annoying. I find myself copying formulas out of excel, modifying them in a text editor, and pasting them back.
Related project: https://aka.ms/get-afe
And shift space being the most annoying of all.
Being in a formula writing a closing brace only to have the formula bar explode because I didn't let go of shift in time. Who wants that?
Being in a formula writing a closing brace only to have the formula bar explode because I didn't let go of shift in time. Who wants that?
Wow great, I used to customize Excel a lot for financial companies. VBA was good enough until about 20 years ago. VSTO was never as good because you need admin rights to install it. ExcelDNA was much better. I gave up 10 years ago so dont know what is going on now tbh.
Right now I use Python and Pandas, mostly doing the same stuff but with more rows and a worse experience. If you could find an easy way to combine Python and Excel it would be awesome. Like embedding Python instead of VBA? Would need sandboxing.
Right now I use Python and Pandas, mostly doing the same stuff but with more rows and a worse experience. If you could find an easy way to combine Python and Excel it would be awesome. Like embedding Python instead of VBA? Would need sandboxing.
We use Xlwings. Xlwings pro allows for embedded python. We use xltrails to version control our excel files with embedded python and/or VBA.
Why doesn't CTRL+Backspace delete a word when editing a cell?
I'd love to drag the formula bar into a vertical column, or its own window, thereby encouraging the use of new lines and indentations in complex formulas for clarity
Why there is no "Check spelling as you type" in Excel?
I have a request related to RealTimeData server for Excel. If there is an active running RTD server it suspends updates whenever the scrollbar or mouse is clicked in Excel which means data updates are missed if user scrolls.
Can this be changed to keep running so behavior is same as a COM server update?
Typically COM server updates will still allow cells to be written to when clicking or scrolling and only suspend when a cell enters edit mode.
Can this be changed to keep running so behavior is same as a COM server update?
Typically COM server updates will still allow cells to be written to when clicking or scrolling and only suspend when a cell enters edit mode.
Another one:
There's something horribly un-optimized going on when I click and drag some values to a new location. Even when there's no overlap, dragging a tiny number of values, like say 3, ends up hanging for several seconds on my very fast computer.
I remember this also didn't used to happen back ~2011-2013 ish, and then I remember it started happening at some point and hasn't been fixed since.
There's something horribly un-optimized going on when I click and drag some values to a new location. Even when there's no overlap, dragging a tiny number of values, like say 3, ends up hanging for several seconds on my very fast computer.
I remember this also didn't used to happen back ~2011-2013 ish, and then I remember it started happening at some point and hasn't been fixed since.
In power query, there is no way to produce a viable hyperlink as a result. I can at most produce a HYPERLINK expression as text but I need to “edit and enter” for it to change to a hyperlink.
That’s just a specific case of the general case that power query outputs, when being formulas, can’t be autoevaluated whenever the query ends - you can just compute inside the query.
That’s just a specific case of the general case that power query outputs, when being formulas, can’t be autoevaluated whenever the query ends - you can just compute inside the query.
I would state improving the VBA development experience that feels stuck in Office '97 days, but it will most likely be ignored.
Managing conditional formatting rules is a pain in the ass. They multiply incessantly whenever you cut and paste, the popup is tiny and non-resizable (it could do with a redesign from scratch), and names are not kept as references. Instead, they get saved as range references.
A simple way to connect to the cloud. We're moving all apps to the cloud, but users prefer to use Excel for most tasks. It's not clear to me how to handle MFA or AWS auth challenges in Excel, and we typically end up using Xlwings or similar, which is not very nice.
Office 365 is the answer to this. Simultaneous editing is pretty incredible and seamless, as is versioning and instant save. Trying to hack in some third party solution is never going to be as good.
I miss the interactivity of selecting data in older versions of Excel (pre 2007).
You can see the data ranges highlighted when you select a series on the plot and you could drag them as desired.
Currently it’s very awkward and unintuitive why you can move ydata but not xdata.
Python support
I really wonder what Excel developpers think of connecting spreadsheets and master data management services so you can link data between a spreadsheet and the master data.
[wasn’t it the goal of OData?]
Make Excel online have first class support for CSV import and export.
Make that Excel auto conversions can be switched off entirely
Also, an option to make arrow keys always move the text cursor in the formula bar and never select cells would be great!
1. Some sort of toggle somewhere for dates before 1900 being supported just like dates after 1900.
I work with a lot of historical baseball data with dates before 1900. Constantly having to do string conversions to math and then back again is so tiring. Every time I port in data, I have to clean it up, and every time it screws up in some new and novel way.
Yes, I'm aware that XL's date automatic date conversion causes havoc in genetic data sets as is. And yes, I know that it would cause further havoc if pre-1900 dates were automatically seen as dates.
But some toggle somewhere that I could just click once and then be done with it would save me weeks of time.
2. Again, another toggle that keeps acutes, tildes, and other letters as separate from their non-marked twins when sorting alphabetically or otherwise processing data.
Currently when I sort baseball players by name, alphabetically, the 'á' and 'a' or 'ñ' and 'n' are seen as the same letter and sorted intermixed. This is a huge problem when dealing with Central American, South American, and Caribbean players. Common names like José are not the same as Jose. Same goes for string comprehension functions or searching.
I work with a lot of historical baseball data with dates before 1900. Constantly having to do string conversions to math and then back again is so tiring. Every time I port in data, I have to clean it up, and every time it screws up in some new and novel way.
Yes, I'm aware that XL's date automatic date conversion causes havoc in genetic data sets as is. And yes, I know that it would cause further havoc if pre-1900 dates were automatically seen as dates.
But some toggle somewhere that I could just click once and then be done with it would save me weeks of time.
2. Again, another toggle that keeps acutes, tildes, and other letters as separate from their non-marked twins when sorting alphabetically or otherwise processing data.
Currently when I sort baseball players by name, alphabetically, the 'á' and 'a' or 'ñ' and 'n' are seen as the same letter and sorted intermixed. This is a huge problem when dealing with Central American, South American, and Caribbean players. Common names like José are not the same as Jose. Same goes for string comprehension functions or searching.
Pop round to your mates in the MSTeams group and kick their asses for us. /s
Can you keep the upcoming UI butchering as opt in for at least 20 years?
Dark mode in the web version please!
DUDE, please for the love of god, implement/fix the following following few things and I'll be a happy spreadsheeter:
- INCREMENTAL FIND WITHOUT A FAILURE MODAL in the ctrl-f window. Right now, when no match is found, it pops up a modal saying "no match found" that you have to dismiss! Jeff Atwood called this craziness out in 2006 https://blog.codinghorror.com/unnecessary-dialogs-stopping-t... it's amazing it's still in a flagship Microsoft product in 2022.
- REGEX FIND-REPLACE in the ctrl-f window. Put it in an "advanced" tab or something, but it would be invaluable when doing archaeology on some giganormous spreadsheet someone hands off to you, and you have to figure out wtf is going on. Or I need to make a complicated change across the whole spreadsheet and I'm wishing for something like sed or awk.
- REGEX match / substitution as a cell formula would be pretty neat too. String processing is pretty tricky as it is.
- MULTIPLE-SUBSTITUTION. If I need to replace many substrings in a string, I need to do ="SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(..." etc, it's annoying. Do for SUBSTITUTE what you did for IF: a SUBSTITUTES version (note the S) that would work like: "=SUBSTITUTES(string, substring1, replacement1, substring2, replacement2, ...)"
- INDENTING in the formula bar. I don't like having to tap space all the time or copypaste from Notepad++. Also: monospace font in the formula bar, pretty please.
- The "trace dependents" / "trace predecessors" thing, when you click on an arrow (hard to click btw, very narrow target), the window that pops up can't be resized, so you can't actually read a long formula. It should be resizable.
- You can merge cells horizontally or vertically, but it's generally discouraged in favor of "center across selection". Problem: you can only "center across selection" horizontally. Would be good to be able to do this vertically as well.
- When using the "check compatibility" feature, it takes a LOT of clicking through menus to find a possible problem, and then when you click "go to" (I forgot the exact name of the button, but whatever it is you click to see the cell where the incompatibility is), the compatibility checker window you came from disappears. So if you want to find another incompatibility, you have to go through all those menus again. Immense pain.
- My excitement of using PowerQuery was matched only by my disappointment of finding out that it doesn't support SQLite databases.
- Meta note: I saw someone from the Excel team post on /r/excel a while ago soliciting feedback. I wanted to give some of my own, but I had to go through some dumb bureaucracy, and the data consent form / NDA said Microsoft would get rights over my biometric data or something preposterous like that. I just wanted to give feedback to the Excel devs but not if there's such dystopian nonsense to deal with. Can I just email you? Or you email me, it's in the "about" part of my profile.
- A way to track down and squish ALL external links. Sometimes a warning pops up about external references but it's not actionable, because they can be lurking in so many dark corners and there's no way to enumerate all of them. It's not as simple as searching through formulae for things like "C:\"; they can be in weird shit like chart axis labels and conditional formatting and god knows what else. I've had cases where I've been working on a single Excel document as part of a team, and somebody unknowingly introduced external links somewhere, the warning came up, and we couldn't find them. Org policy said we couldn't distribute it if there were external links, so we basically had to "declare bankruptcy" and start again, carefully reproducing our work in a blank document, copying stuff over a piece at a time.
- Generally: better tools for understanding a large unfamiliar project. The predecessors / dependents feature is very anemic, but it's about the only thing on the menu right now for understanding macro-scale control flow and data dependence.
- Linting / "code quality" tools? I definitely don't want some kind of clippy-esque flow-breaking "it looks like you're using vlookup, did you know xlookup is better?" popup, but maybe some kind of tab or button to highlight formula antipatterns and suggest autofixes. E.g. it could detect nested IF and suggest an equivalent using IFS (flat is better than nested). One thing I've noticed is that experienced Excel users get kind of stuck in their ways and don't know about new features that can simplify things, but if they got used to consulting this system, it would alert them to new features in a natural, non-annoying way. You could put this into the "check for problems" system, people are already used to checking that for version incompatibility and accessibility.
.. this is more than a few things, I kept thinking of more stuff as I was writing.
- INCREMENTAL FIND WITHOUT A FAILURE MODAL in the ctrl-f window. Right now, when no match is found, it pops up a modal saying "no match found" that you have to dismiss! Jeff Atwood called this craziness out in 2006 https://blog.codinghorror.com/unnecessary-dialogs-stopping-t... it's amazing it's still in a flagship Microsoft product in 2022.
- REGEX FIND-REPLACE in the ctrl-f window. Put it in an "advanced" tab or something, but it would be invaluable when doing archaeology on some giganormous spreadsheet someone hands off to you, and you have to figure out wtf is going on. Or I need to make a complicated change across the whole spreadsheet and I'm wishing for something like sed or awk.
- REGEX match / substitution as a cell formula would be pretty neat too. String processing is pretty tricky as it is.
- MULTIPLE-SUBSTITUTION. If I need to replace many substrings in a string, I need to do ="SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(..." etc, it's annoying. Do for SUBSTITUTE what you did for IF: a SUBSTITUTES version (note the S) that would work like: "=SUBSTITUTES(string, substring1, replacement1, substring2, replacement2, ...)"
- INDENTING in the formula bar. I don't like having to tap space all the time or copypaste from Notepad++. Also: monospace font in the formula bar, pretty please.
- The "trace dependents" / "trace predecessors" thing, when you click on an arrow (hard to click btw, very narrow target), the window that pops up can't be resized, so you can't actually read a long formula. It should be resizable.
- You can merge cells horizontally or vertically, but it's generally discouraged in favor of "center across selection". Problem: you can only "center across selection" horizontally. Would be good to be able to do this vertically as well.
- When using the "check compatibility" feature, it takes a LOT of clicking through menus to find a possible problem, and then when you click "go to" (I forgot the exact name of the button, but whatever it is you click to see the cell where the incompatibility is), the compatibility checker window you came from disappears. So if you want to find another incompatibility, you have to go through all those menus again. Immense pain.
- My excitement of using PowerQuery was matched only by my disappointment of finding out that it doesn't support SQLite databases.
- Meta note: I saw someone from the Excel team post on /r/excel a while ago soliciting feedback. I wanted to give some of my own, but I had to go through some dumb bureaucracy, and the data consent form / NDA said Microsoft would get rights over my biometric data or something preposterous like that. I just wanted to give feedback to the Excel devs but not if there's such dystopian nonsense to deal with. Can I just email you? Or you email me, it's in the "about" part of my profile.
- A way to track down and squish ALL external links. Sometimes a warning pops up about external references but it's not actionable, because they can be lurking in so many dark corners and there's no way to enumerate all of them. It's not as simple as searching through formulae for things like "C:\"; they can be in weird shit like chart axis labels and conditional formatting and god knows what else. I've had cases where I've been working on a single Excel document as part of a team, and somebody unknowingly introduced external links somewhere, the warning came up, and we couldn't find them. Org policy said we couldn't distribute it if there were external links, so we basically had to "declare bankruptcy" and start again, carefully reproducing our work in a blank document, copying stuff over a piece at a time.
- Generally: better tools for understanding a large unfamiliar project. The predecessors / dependents feature is very anemic, but it's about the only thing on the menu right now for understanding macro-scale control flow and data dependence.
- Linting / "code quality" tools? I definitely don't want some kind of clippy-esque flow-breaking "it looks like you're using vlookup, did you know xlookup is better?" popup, but maybe some kind of tab or button to highlight formula antipatterns and suggest autofixes. E.g. it could detect nested IF and suggest an equivalent using IFS (flat is better than nested). One thing I've noticed is that experienced Excel users get kind of stuck in their ways and don't know about new features that can simplify things, but if they got used to consulting this system, it would alert them to new features in a natural, non-annoying way. You could put this into the "check for problems" system, people are already used to checking that for version incompatibility and accessibility.
.. this is more than a few things, I kept thinking of more stuff as I was writing.
All concerning Excel Online:
- Ctrl+F3 to bring up the name manager (unless its already there somehow?)
- the full functionality of Conditional formatting, f.e. not all formatting rules can be used
Both are requiring me to open files stored on Sharepoint via the desktop app frequently.
Both are requiring me to open files stored on Sharepoint via the desktop app frequently.
Oh, and:
- The name manager is relatively cumbersome to use. F.e. at least some copy/clone functionality would be nice.
- Excel table columns cannot be directly used for DVL lists, you need to create additional named ranges pointing to them.
- Once Excel tables have been created, afaik it is not yet possible to extend them by additional columns, respective edit their defined ranges?
- RegExp support for DVLs, w/o having to rely on VBA (desktop only) or OfficeScript (Online only)
As someone with a perhaps well deserved reputation as a Microsoft hater (ha, check my history..)
Microsoft Excel is perhaps the greatest program-slash-programming language ever invented. Nothing has much come close in terms of giving regular folks the power of general-purpose computing (sadly, further and further removed from what we're doing today.)
Microsoft Excel is perhaps the greatest program-slash-programming language ever invented. Nothing has much come close in terms of giving regular folks the power of general-purpose computing (sadly, further and further removed from what we're doing today.)
It does the job and it enables "regular folks" to solve business problems. This creates value. Most business problems don't require sophisticated IT systems.
Sometimes I catch myself thinking about a sophisticated solution involving commercial software packages, databases, webservers, cloud services etc. and then I remember "If I bend the problem just a little bit it fits into an Excel table with a bit of VBA glue and the problem is solved in a much less complicated way. Our scale is small so it will stay solved for years to come. My coworkers can handle and maintain the file so it doesn't fall back to me when problems arise. So yeah, stupid as it may sound, Excel is the best solution for this problem."
Sometimes I catch myself thinking about a sophisticated solution involving commercial software packages, databases, webservers, cloud services etc. and then I remember "If I bend the problem just a little bit it fits into an Excel table with a bit of VBA glue and the problem is solved in a much less complicated way. Our scale is small so it will stay solved for years to come. My coworkers can handle and maintain the file so it doesn't fall back to me when problems arise. So yeah, stupid as it may sound, Excel is the best solution for this problem."
Avoiding any custom software development and purchasing is the number one priority for small to medium businesses that want to survive if you ask me. I've seen a couple of them killed by software. At this point I'd rather work for a company that is held together with Excel than "domain specific enterprise software" having worked in that sector for years.
Actually the most fun I ever had was being the "IT guy" as a secondary function in a small engineering business in 2001. That was amazing. I didn't have to do a lot and could concentrate on my primary role with enough distractions to make it and my secondary one interesting. We just ran a Windows 2000 domain with 9 workstations on a switch with a 512K ADSL router and it basically just worked flawlessly. Excel 2000 featured heavily.
I occasionally get the urge to build out a Windows 2000 and Office 2000 box just because it was the last windows release that I enjoyed.
Actually the most fun I ever had was being the "IT guy" as a secondary function in a small engineering business in 2001. That was amazing. I didn't have to do a lot and could concentrate on my primary role with enough distractions to make it and my secondary one interesting. We just ran a Windows 2000 domain with 9 workstations on a switch with a 512K ADSL router and it basically just worked flawlessly. Excel 2000 featured heavily.
I occasionally get the urge to build out a Windows 2000 and Office 2000 box just because it was the last windows release that I enjoyed.
I've seen enough cases where the opposite is the case. Businesses running off Excel spreadsheets are often very inefficient, drowning in more problems than solutions and the day to day workflows for employees are awful. I'm not trying to say a small shop should go all in with Oracle or SAP, however a healthy investment in specialised software often pays off extremely well.
Specialized software is a market for lemons. Most businesses running off Excel spreadsheets are ill-equipped to judge the vendors in question, and more often than not the net result is worse than the Excel mess.
If you are an average business with no internal expertise to evaluate vendors: well of course everyone's going to spin you whilst the money keeps flying out of your pockets. Smarter companies would at least hire people that could advise on such things.
If you had the organizational expertise to hire such a person, and the leadership to prioritize it enough to happen - you already would probably not need them that much.
People like that get hired through recommendations. I interacted with some companies where nobody would know shit about anything tech related but the senior managers were fairly well connected in the business world, so getting someone impartial who could advise on how to proceed wasn't too bad.
It cuts both ways, doesn't it? Excel is no replacement for a dedicated ERP system, as soon as you need one of those get one. Excel can replace a lot of analytics and KPI software packages, and fill in gaps in any ERP system. It is cheaper to, cautiously, fill those gaps with an Excel solution (if well documented) than developing a customized ERP solution.
I currebtly see people using Palantir as the default for anything and everything, only to export stuff from Palantir to Excel anyways.
I currebtly see people using Palantir as the default for anything and everything, only to export stuff from Palantir to Excel anyways.
You waste it on Excel or you waste it on subscriptions, opex and consultants.
Or you hire someone who can shape the business processes towards efficiency with the tools you already have...
Or you hire someone who can shape the business processes towards efficiency with the tools you already have...
> Avoiding any custom software development and purchasing is the number one priority for small to medium businesses that want to survive if you ask me.
Yes and no. I think it depends on the problem being solved. Many small companies solve the same problem. For them a working commercial solution should exist. Many other small companies exist because they can flexibly solve special problems. Special problems on which the big companies fail. Mainly because commercial software solutions don't exist and the problems often change quickly. In that case in-house-IT can be THE deciding advantage that makes them move faster than the competition. Yeah, it depends on the kind of problem being solved (or not solved) which is better and sometimes things change unexpectedly making the other choice better for some years.
Yes and no. I think it depends on the problem being solved. Many small companies solve the same problem. For them a working commercial solution should exist. Many other small companies exist because they can flexibly solve special problems. Special problems on which the big companies fail. Mainly because commercial software solutions don't exist and the problems often change quickly. In that case in-house-IT can be THE deciding advantage that makes them move faster than the competition. Yeah, it depends on the kind of problem being solved (or not solved) which is better and sometimes things change unexpectedly making the other choice better for some years.
I share that thought which is one of the reasons I'm so excited about the platform I'm building. While I'm trying really hard to focus on being a "vertically integrated board game company", there is the siren song to go beyond board games and make a platform for business.
I wrote a programming language which is like a better structured Excel with a reactive database. It's super fun to play with at the moment, but it's immature: https://www.adama-platform.com/
I wrote a programming language which is like a better structured Excel with a reactive database. It's super fun to play with at the moment, but it's immature: https://www.adama-platform.com/
Second time I'm seeing your platform. I still don't quite understand what it does exactly, but I'm intrigued nonetheless
That makes two of us!
I'm reworking the marketing and landing page, and I'm working on different stabs.
There are two things to consider.
First, I built a reactive document engine. Imagine Excel except instead of many very large grids, I have tables, objects, and single variables. Formulas can be attached any aspect to compute things reactivly,
Second, I put that document within a NoSQL style platform such that spreadsheets are now serverless.
In a way, it's like a headless Excel 365. Mutations of the documents happen by sending messages to the documents (and now HTTP puts).
When you read the document, you download a privacy checked version and filtered versioned (via a gossip'd view state).
My new marketing copy is going to start with "Unlock multiplayer super powers" - "Whether building a collaborative applicication or competitive game, the Adama platform will connect your people to global state and logic using a low latency edge network"
I'm reworking the marketing and landing page, and I'm working on different stabs.
There are two things to consider.
First, I built a reactive document engine. Imagine Excel except instead of many very large grids, I have tables, objects, and single variables. Formulas can be attached any aspect to compute things reactivly,
Second, I put that document within a NoSQL style platform such that spreadsheets are now serverless.
In a way, it's like a headless Excel 365. Mutations of the documents happen by sending messages to the documents (and now HTTP puts).
When you read the document, you download a privacy checked version and filtered versioned (via a gossip'd view state).
My new marketing copy is going to start with "Unlock multiplayer super powers" - "Whether building a collaborative applicication or competitive game, the Adama platform will connect your people to global state and logic using a low latency edge network"
>My new marketing copy is going to start with "Unlock multiplayer super powers" - "Whether building a collaborative applicication or competitive game, the Adama platform will connect your people to global state and logic using a low latency edge network"
Not that it matters, but, personally, I cannot understand anything in that sentence, nor most of what you have on your site.
Maybe, just maybe, you could provide a couple of real-life examples, if you cannot find a way to explain in a simpler manner what the platform does.
In your FAQ's : >For a more detailed answer, there is an entire chapter outline what Adama is within the book. The book also has examples of some of these use-cases in action.
I cannot find "the book".
Not that it matters, but, personally, I cannot understand anything in that sentence, nor most of what you have on your site.
Maybe, just maybe, you could provide a couple of real-life examples, if you cannot find a way to explain in a simpler manner what the platform does.
In your FAQ's : >For a more detailed answer, there is an entire chapter outline what Adama is within the book. The book also has examples of some of these use-cases in action.
I cannot find "the book".
Howdy, thanks. I rewrote it. I intend to launch once I get some illustrations.
The big text: Unlock multiplayer superpowers in your software.
The small text: "Serverless" game hosting with enterprise ambitions to change the world. Adama is an open-source vertical web stack designed for board games, and the platform is a nuclear warhead of productivity when compared to traditional web stacks. Let's get shit done, today.
This is all above the fold.
The book was linked, and I just tested it. I'll make this more apparent. The link goes to https://book.adama-platform.com/what/post-hoc.html
Although, it seems like surge.sh has been having some issues lately.
The big text: Unlock multiplayer superpowers in your software.
The small text: "Serverless" game hosting with enterprise ambitions to change the world. Adama is an open-source vertical web stack designed for board games, and the platform is a nuclear warhead of productivity when compared to traditional web stacks. Let's get shit done, today.
This is all above the fold.
The book was linked, and I just tested it. I'll make this more apparent. The link goes to https://book.adama-platform.com/what/post-hoc.html
Although, it seems like surge.sh has been having some issues lately.
I would agree. I'm an Apple Numbers user now but I have yet to find something even remotely as useful as the spreadsheet abstraction. I use them for everything from solving engineering problems, forecasting, statistics, decision analysis to personal finance and project planning. I could solve all of the problems with a programming language but it'd take 10x as long.
As a developer, when I worked in VBA it was just way more rewarding and appreciated. I was way happier with the work I was doing and having a much bigger (and more visible) impact... Sadly I got paid roughly 1/4 of what I get paid now to muck around in package.json files all day. I know it's not fashionable to say,but yeah I feel like Excel is underrated and scoffed at by the "serious tech" community when it should be lauded as an example to strive towards.
It's actually pretty technically awesome too. When I was studying for an MSc in CS I focused on program synthesis and one of the key papers of applied program synthesis is Flashfill in Excel (https://www.microsoft.com/en-us/research/wp-content/uploads/...). It might seem boring but there's a lot of really cool stuff in the backend of it!
I've seen Excel trive in no places more so than those commited to the "one true ERP" religeon.
And it is not subversion. It is people trying to do their job despite draconian topdown unworcable policies and systems shoved down their thoat, keeping the operations side afloat in the face of debilitating managerial ignorance.
And it is not subversion. It is people trying to do their job despite draconian topdown unworcable policies and systems shoved down their thoat, keeping the operations side afloat in the face of debilitating managerial ignorance.
From experience, most Excel solutions are due to people refusing to use whatever solution their ERP system is offering. Often with disasterous results, results that nobody really sees because it is just the status quo. Nothing wrong to use Excel for reporting and number crunching, ERP systems are not designed to do that.
I hate it when people take short cuts and corners because they refuse to accept that an ERP is there for a reason. I also hate it when the people setting uo an ERP ignore business needs. UX so is, rightly so, taking a back seat in all of these discusions.
I hate it when people take short cuts and corners because they refuse to accept that an ERP is there for a reason. I also hate it when the people setting uo an ERP ignore business needs. UX so is, rightly so, taking a back seat in all of these discusions.
> From experience, most Excel solutions are due to people refusing to use whatever solution their ERP system is offering.
From experience, most Excel solutions are because people get rebuffed (or have learned by experience that they will expend lots of effort and the be rebuffed or given something not fit for purpose) by the bureaucratic processes necessary to get anything provided to them by their enterprise systems (ERP or otherwise) by the high priesthood that centrally administers those systems.
From experience, most Excel solutions are because people get rebuffed (or have learned by experience that they will expend lots of effort and the be rebuffed or given something not fit for purpose) by the bureaucratic processes necessary to get anything provided to them by their enterprise systems (ERP or otherwise) by the high priesthood that centrally administers those systems.
As part of that priesthood, Excel is neither SOX nor otherwise compliant. If users get rebuffed, that is the overall system working as intended.
As someone who has worked on both sides, if you don't want people making crappy excel-based, local-to-their-shop solutions, but they are because of the unresponsiveness and inutility of your central IT bureaucracy, the system is not, in fact, working as intended.
I think we are actually agreeing here. If users are left alone, they cannot be blamed for trying whatever they can to make things work. ERP only works when IT and business works together.
Hypercard.
The difference, of course, is that Excel wasn't abandoned by its owners like Hypercard was.
The difference, of course, is that Excel wasn't abandoned by its owners like Hypercard was.
Dammit I miss HyperCard. HyperCard got me in to programming by making it accessible to someone who didn't even realize they were doing it - my Hebrew teacher in third grade. There haven't been many tools that can make anything from a simple card file to Myst. I wish there was something like that today, or that I was a good enough engineer to contribute to a modern replacement.
There are quite a few options, including emulators, clones and HC inspired projects.
https://hypercard.org/
https://hypercard.org/
It's not a replacement per se, but tiddlywiki is the closest I have seen of a very flexible yet easy-to-use framework for people who have limited programming skills https://tiddlywiki.com/ There is a long list of tools built in tiddlywiki: https://dynalist.io/d/zUP-nIWu2FFoXH-oM7L7d9DM
Check out Power Apps. It is functionally Hypercard on top of any Office 365 data sources you want.
I spent about half an hour poking around Microsoft's documentation for PowerApps and then their Youtube channel trying to find a concise demonstration of the tool, and it was all totally impenetrable. Everything was either too high-level- speaking aspirationally about the potential business value without really showing anything being made or used- or too low-level, off in the weeds tutorializing a very specific esoteric integration with a bunch of other tools, writing reams of backend code in some other language and environment.
I feel confident in my evaluation that PowerApps is in a completely different universe of accessibility and complexity from Hypercard.
I feel confident in my evaluation that PowerApps is in a completely different universe of accessibility and complexity from Hypercard.
I feel like I'm stuck in a timeloop. I swear I've read almost this exact comment as the top comment of a different (monthly) "Hacker News debates Excel" thread
Honestly, I think that speaks to the massively huge gap in this space. There's no technical reason we couldn't have a Hypercard-esque development platform that e.g. any professor of any discipline could use to make an app. (I'm in higher-ed, that's where my brain wen)
Agree with this entirely. Excel is the most successful and most productive "low code" platform out there.
And that's exactly the problem.
It's great if the person using it knows what he is doing.
I don't want to know how many decisions are based on incorrect excel sheets or exel files no one understands anymore because the original developer already left the company and the next added some "improvement" without really understanding the existing code.
I don't want to know how many decisions are based on incorrect excel sheets or exel files no one understands anymore because the original developer already left the company and the next added some "improvement" without really understanding the existing code.
There are a few things reactive formulaic spreadsheets need to do before reaching peak. Normalization like concepts. Abstractions.
It will come.
It will come.
I'm unfortunately pessimistic. Not because they couldn't, but because in today's IT environment, "empowering the user" is perhaps the lowest priority.
It already arrived in the form of Lotus Improv.
improv never caught up sadly.. but hey, it's reboot decade so time to dust it off in react
Yes, now if I kind Microsoft could fix copy and paste from the desktop to online versions…
Yes, now if only Microsoft could fix copy and paste from the desktop to online versions…
Is it really easier to learn excel in depth than a programming language?
> ever invented
That would be visicalc then.
That would be visicalc then.
Obligatory BillyG Review story from Joel on Software:
https://www.joelonsoftware.com/2006/06/16/my-first-billg-rev...
It provides a glimpse into the complexity and backwards compatibility (to Lotus 1-2-3 no less!) that I find interesting.
https://www.joelonsoftware.com/2006/06/16/my-first-billg-rev...
It provides a glimpse into the complexity and backwards compatibility (to Lotus 1-2-3 no less!) that I find interesting.
> Microsoft Excel is perhaps the greatest program-slash-programming language ever invented
I would argue the opposite: it is the single most harmful piece of software ever invented. It allows people to reduce everything to numbers and then expect reality to match with the numbers in the spreadsheet without any regard for the actual human beings it affects.
It goes from “if we just change this number from 40 hrs/week to 50 hrs/week, we’ll make our deadline” all the way to “look, if we reduce the number or cancer treatments we authorize by X% then our profits go up by Y%”
I would argue the opposite: it is the single most harmful piece of software ever invented. It allows people to reduce everything to numbers and then expect reality to match with the numbers in the spreadsheet without any regard for the actual human beings it affects.
It goes from “if we just change this number from 40 hrs/week to 50 hrs/week, we’ll make our deadline” all the way to “look, if we reduce the number or cancer treatments we authorize by X% then our profits go up by Y%”
But this way of thinking has nothing to do with excel in particular.
Is this a criticism of Excel specifically, or is the problem that it’s making basic arithmetic more accessible to institutions with negative externalities?
It’s a criticism of spreadsheets in general.
Does this criticism extend to graphing calculators?
This criticism extends to pretty much math in general.
No. Spreadsheets allow for easy manipulation of large tables of numbers and immediately see the results. It’s this ability to ‘tweak’ the numbers quickly that makes it so dangerous.
Ah, so making assholes effective is the sin?
I hate to break it to you, but that applies to everything from bricks to aircraft carriers.
I hate to break it to you, but that applies to everything from bricks to aircraft carriers.
I wrote a piece of code that was front-ended by Excel 3, in 1991 that talked to multiple, heterogeneous databases. There was some UUCP action, some FTP, DB2 on AS/400, some AT&T Unix, Wang, OS/2, and so on. It showed inventory and sales charts on a projector for executives. That thing ran till 2019 because the company called me and wanted to know how it worked. They were planning to replace it but could not figure it out.
Excel never dies. (My first spreadsheet was SuperCalc. It was amazing, for that time, but no one seems to be talking about their SuperCalc sheets any more.)
Excel never dies. (My first spreadsheet was SuperCalc. It was amazing, for that time, but no one seems to be talking about their SuperCalc sheets any more.)
302 comments last year: https://news.ycombinator.com/item?id=26386419
If all you have is this hammer, check out https://github.com/PerditionC/VBAChromeDevProtocol to automate workflows in the browser with Excel VBA.
If all you have is this hammer, check out https://github.com/PerditionC/VBAChromeDevProtocol to automate workflows in the browser with Excel VBA.
"Microsoft launched Excel in 1985 exclusively on the Macintosh. It was that counterintuitive decision to launch on its competitor’s computer while Lotus 1-2-3 was stuck on its own MS-DOS" - crazy that microsoft recognised its own deficiency (difficult UI) and took a big bet with their competitor. Microsoft and Apple's relationship has been complicated from the start
My dad, a retired accountant, still refuses to switch from Lotus 1-2-3 to Excel.
Tell him about my Linux port, I'm updating it regularly and fixing bugs :)
https://github.com/taviso/123elf/wiki/Getting-Started
https://github.com/taviso/123elf/wiki/Getting-Started
I had read somewhere about how you ported it, impressive work!
https://lock.cmpxchg8b.com/linux123.html
https://lock.cmpxchg8b.com/linux123.html
Had to rebuild all of my dad’s Lotus 1-2-3 sheets in Excel to finally force the switch!
My dad did that too for awhile but eventually gave in.
Wow. Legendary dude.
I can open a 20 year old excel file on a random pc and it will run perfectly.
I will be able to reproduce results and inspect the exact equations generating these.
Try that with 20yo code (python,c++ whatever)
As a fresh grad I used to hate excel, but now I can totally see why it will never die.
I will be able to reproduce results and inspect the exact equations generating these.
Try that with 20yo code (python,c++ whatever)
As a fresh grad I used to hate excel, but now I can totally see why it will never die.
>Try that with 20yo code (python,c++ whatever)
I regularly compile 35 year old C code.
Standards are great like that.
I regularly compile 35 year old C code.
Standards are great like that.
I fail to compile 1 year old code if I don’t have the exact compiler and library versions that the program required at the time of development.
You should write better code.
Excel breaks between versions silently. One of my first jobs out of university was to build a pipeline of excel spreadsheet to ensure they were run in the version of excel and windows they were created in. You could not guarantee that you would get the same results otherwise. This was a slight problem for the brokerage I was working at since it had lost them a few million dollars recently.
Excel breaks between versions silently. One of my first jobs out of university was to build a pipeline of excel spreadsheet to ensure they were run in the version of excel and windows they were created in. You could not guarantee that you would get the same results otherwise. This was a slight problem for the brokerage I was working at since it had lost them a few million dollars recently.
Parent said run perfectly not gives the same result.
If you consider 1+1 = 3 to be running perfectly excel sounds like the right tool for any job you have.
Not to mention "bugs" like this
https://docs.microsoft.com/en-us/office/troubleshoot/excel/w...
https://docs.microsoft.com/en-us/office/troubleshoot/excel/w...
I find this statement quite fascinating because despite my limited experience working with excel I've seen countless compatibility issues, caused either by change of version of excel or change of locale. Perhaps this is caused by user error, but then it sure seems easy to make these mistakes.
Massive companies are run off dodgy Excel spreadsheets. I make my living from 3 completely different items of software (seating planner, task planner, data transformation). I'm sure Excel is the main competitor for all 3. The whole of western civilization might collapse if Excel were to suddenly disappear.
At least 50% of my day job could be described as "replace some guy's Excel file that got out of hand". I'm not even mad, I'll die before excel does.
I'm curious, how do you find a job like that? It sounds kinda fun with clear requirements.
Isn't 50% of IT replacing creaky, lashed-together spreadsheets with code?
Trading IT
True. In my last job I was unofficial HR, timekeeper, purchaser, schedule maker, everything.
My precessor used to do everything on paper. He had files of leave forms, monthly attendance timesheets, rotation scheudles etc.
I started using a excel sheet. One sheet named db, listing the employee number, names, start date, position, entitled leave days, manager name, work pattern (day only, any shift).
Then over time I added other sheets like leave details, which pulls employee data from db, and adds more data like their aprooved vacation slots etc.
A sheet listing every day of the year in columns, and rows as employee names & numbers, and I will manually tyoe N for Night, D for Day, F for Friday OFF, H for Holiday, V for Vacation.
Then another sheets pulls data & shows me for my chosen month a printed & formatted schedule. It also lists the percentage of workforce, and their divisions.
Another sheet pulls vacation data for one employee for my chosen year.
It was fun creating all those formulas.
My precessor used to do everything on paper. He had files of leave forms, monthly attendance timesheets, rotation scheudles etc.
I started using a excel sheet. One sheet named db, listing the employee number, names, start date, position, entitled leave days, manager name, work pattern (day only, any shift).
Then over time I added other sheets like leave details, which pulls employee data from db, and adds more data like their aprooved vacation slots etc.
A sheet listing every day of the year in columns, and rows as employee names & numbers, and I will manually tyoe N for Night, D for Day, F for Friday OFF, H for Holiday, V for Vacation.
Then another sheets pulls data & shows me for my chosen month a printed & formatted schedule. It also lists the percentage of workforce, and their divisions.
Another sheet pulls vacation data for one employee for my chosen year.
It was fun creating all those formulas.
I am currently writing Python with Pandas - reading in genormous XLS spreadsheets to wrangle them into yet another XLS spreadsheet which is then fed to a legacy system to read in.
Excel truely is the universal data programming tool.
Excel truely is the universal data programming tool.
> SaaS has replaced discrete sales as the go-to business model for software because it’s better for the customer, ...
What.
Does the author actually believe this? Every single example of a company that switched from something I could buy to making me rent it instead has ended up costing me more money and given me a worse product that is constantly at risk of >poof< disappearing if the company goes under or just decides they feel like EOLing it.
I'll totally buy the rest of the quoted sentence (e.g. about recurring revenue) but the part I quoted above is absolute arrant nonsense.
What.
Does the author actually believe this? Every single example of a company that switched from something I could buy to making me rent it instead has ended up costing me more money and given me a worse product that is constantly at risk of >poof< disappearing if the company goes under or just decides they feel like EOLing it.
I'll totally buy the rest of the quoted sentence (e.g. about recurring revenue) but the part I quoted above is absolute arrant nonsense.
I started my career in one of the most profitable teams within Big 4 accounting because they leveraged software when the other firms were focused on spreadsheets. Our team had > 90% market share of accounting business with our sector of public companies for this reason alone. Most of our clients had millions of transactions and assets, and, while another firm had an excel model for this, there was no understanding of the model outputs because it was a black box of incremental computations. While I was there we landed one of the 20 or so remaining public co clients simply because we could show them every step math along the way - we had accountants and engineers that understood the logic in and out.
That said, most businesses can run on spreadsheets.
That said, most businesses can run on spreadsheets.
Excel is transformational. If you're reading this indoors, there's a good chance that significant portions of the building around you were engineered using excel.
"Excel" became a synonym for spreadsheets. There are millions of SMEs, today, running on spreadsheets that aren't Excel. My wife runs her little SME with her paid-for Google Workspace / G Suite: she and her employees are happily filling 'em little cells from the browser in their Google world (for better or worse). There are millions of SMEs like these.
I should know better and run some spreadsheet locally (or maybe use Emacs, which of course can do simple spreadsheet stuff) but I don't bother: I use Google spreadsheets for my VAT, tax, fuel/car mileage computation etc.
Many people around me do just that: they never used any advanced spreadsheet feature and Google spreadsheet is sufficient.
It's as if anyone using GMail who eventually discovered the "Google Drive" then realized he had "Excel" there too (it's Google spreadsheet but they don't care, they still call it Excel). How many people are using GMail?
I also know, shocker, one Apple afficionado using Apple numbers although the Apple users I know typically have GMail / Google spreadsheet.
Now here's the funny kicker: besides during that one gig in a 50 K employees boat, I don't know anyone still using Excel. And, oh the irony, at that gig I was tasked with porting a spreadsheet to a dedicated app.
Of all the Mac laptop users who don't bother having a desktop anymore... Certainly there are some (like my wife) who need a spreadsheet right? Are these people actually all buying Excel to run it on their Mac? Or are Mac users not spreadsheet users!?
I'm confused.
To me a more correct article title would be:
"Spreadsheet never dies" (and "Excel" became a synonym for "spreadsheet").
I should know better and run some spreadsheet locally (or maybe use Emacs, which of course can do simple spreadsheet stuff) but I don't bother: I use Google spreadsheets for my VAT, tax, fuel/car mileage computation etc.
Many people around me do just that: they never used any advanced spreadsheet feature and Google spreadsheet is sufficient.
It's as if anyone using GMail who eventually discovered the "Google Drive" then realized he had "Excel" there too (it's Google spreadsheet but they don't care, they still call it Excel). How many people are using GMail?
I also know, shocker, one Apple afficionado using Apple numbers although the Apple users I know typically have GMail / Google spreadsheet.
Now here's the funny kicker: besides during that one gig in a 50 K employees boat, I don't know anyone still using Excel. And, oh the irony, at that gig I was tasked with porting a spreadsheet to a dedicated app.
Of all the Mac laptop users who don't bother having a desktop anymore... Certainly there are some (like my wife) who need a spreadsheet right? Are these people actually all buying Excel to run it on their Mac? Or are Mac users not spreadsheet users!?
I'm confused.
To me a more correct article title would be:
"Spreadsheet never dies" (and "Excel" became a synonym for "spreadsheet").
> Are these people actually all buying Excel to run it on their Mac? Or are Mac users not spreadsheet users!?
I was able to avoid actual Excel for years until I became responsible for my divisions budget. Accounting people are all Excel that I can see.
Until then I used Google Sheets and Numbers just fine on my mac. And when I was only viewing the budget, Numbers did fine converting or I could use Excel in read only mode.
I was able to avoid actual Excel for years until I became responsible for my divisions budget. Accounting people are all Excel that I can see.
Until then I used Google Sheets and Numbers just fine on my mac. And when I was only viewing the budget, Numbers did fine converting or I could use Excel in read only mode.
> I don't know anyone still using Excel.
I suspect that is very much just the crowds you move in. Every vaguely large enterprise I know is still 100% Microsoft. A huge part of Microsoft's profit these days is their Cloud department, which is really just printing money selling Office 365 licenses to enterprise.
I suspect that is very much just the crowds you move in. Every vaguely large enterprise I know is still 100% Microsoft. A huge part of Microsoft's profit these days is their Cloud department, which is really just printing money selling Office 365 licenses to enterprise.
Their comment said it -- SME's (small- and medium-sized enterprises).
There's a gigantic cultural division between SME's where the decision to go Google is a no-brainer (whether starting from in the first place, or switching to at some point in the past 10 years), and large enterprises where the inertia of MS Office is just too great to switch.
What's funny is just how deeply unaware both sides often are of the other -- as both of your comments demonstrate. :)
There's a gigantic cultural division between SME's where the decision to go Google is a no-brainer (whether starting from in the first place, or switching to at some point in the past 10 years), and large enterprises where the inertia of MS Office is just too great to switch.
What's funny is just how deeply unaware both sides often are of the other -- as both of your comments demonstrate. :)
:) I'm completely aware that smaller companies are using Google. My last two companies used it! (Right up until we were acquired by a large enterprise and got moved to MS).
This is true although Google is slowly getting more of that market. There are various very large companies that are switching.
Yes, I bought Excel to use it on my Mac.
This piece is worth reading, especially if you have said ever heard anyone utter a statement along the lines of “Libre Office does everything but Microsoft office can do“. I use and regularly contribute to Libre Office and haven’t used Microsoft office for years, but I am under no illusion that the former can replace the ladder in any corporation over 10 people or so. There are millions of business processes depending on its exact behavior in ways you would never dream of, and until competitors can emulate that behavior exactly they simply don’t have a chance in government or a big business settings
* latter not ladder. Thanks, Siri
Somewhat tangential, but for unusual uses of Excel I like this video where Michael Shackleford derives blackjack basic strategy from scratch using Excel in about half an hour: https://www.youtube.com/watch?v=jCF-Btu5ZCk
Incredible video.
My brother teaches Decision Support Systems using Excel+VBA at the university because he found that students became productive fastest with it. Spreadsheet model introduced by VisiCalc is probably one of the greatest paradigms introduced in computing making certain programming scenarios extremely accessible. I wish we had more innovations on par with spreadsheets down the road but I don't think we did.
Excel is great, but sometimes it can be abused. I have seen some horrendously complicated VBA sheets that are so much simpler to program in any procedural language.
If you want to create or read Excel spreadsheets programmatically, I recommend LibXL, a simple and powerful C++ library. https://www.libxl.com/
(I am a customer, am not associated with them).
If you want to create or read Excel spreadsheets programmatically, I recommend LibXL, a simple and powerful C++ library. https://www.libxl.com/
(I am a customer, am not associated with them).
Can you expand on that? My view of VBAis that it is a dated, but pretty much standard procedural language.
I have seen ungodly contortions to bend Excel to the will of the creator of the spreadsheet, when a few lines of C++ or Python or whatever code would do the trick easily. Intermediate results are stored in columns with no possibility of comments or explanations. You can't get an overview of the logic, as you would by looking at a page of code: it's just cryptic Excel formulae in isolated cells. The constraint of the single-line make formulas opaque and inscrutable. In the end, the whole thing is a mess, impossible to maintain or update.
It's not the VBA that is the problem. It's the disparate scattering of business logic.
Don't get me wrong: Excel is brilliant for some things. But it has been extended beyond reason by wannabe programmers in the accounting department who discover they chose the wrong career.
It's not the VBA that is the problem. It's the disparate scattering of business logic.
Don't get me wrong: Excel is brilliant for some things. But it has been extended beyond reason by wannabe programmers in the accounting department who discover they chose the wrong career.
> Most software we use at work exists in one of two categories:
> 1. It’s new and we love it for now.
> 2. It’s old but we have to use it and we hate it.
> [... Microsoft Excel] inhabits its own category: it’s old, but we love it
There's a fourth quadrant missing in that diagram: "It's new and we hate it", aka "the Microsoft Teams quadrant".
> 1. It’s new and we love it for now.
> 2. It’s old but we have to use it and we hate it.
> [... Microsoft Excel] inhabits its own category: it’s old, but we love it
There's a fourth quadrant missing in that diagram: "It's new and we hate it", aka "the Microsoft Teams quadrant".
The spreadsheet UI is the one interface design that will not disappear in millions of years. Hard to imagine any UI that’ll last longer. This is the very pinnacle
There are so many technologies Microsoft has tried to go all in on over the years...Internet Explorer, Edge, and .Net to name a few.
Why they've never gone all in on the idea of Excel as a fundamental part of the operating system on top of which more sophisticated apps operate baffles me.
Why they've never gone all in on the idea of Excel as a fundamental part of the operating system on top of which more sophisticated apps operate baffles me.
The language in Microsoft Power Apps is basically the formula language from Excel, with cell references (A1) replaced with properties (TextBox.Text) -- only thing I'm aware of that comes close though.
It is a smart decision and a point where many companies fail.
Excel works perfectly as it is, it is a product that sells well, there is no reason to change it. They modernize it from time to time, but they follow what may be the most important thing in production: if it ain't broke, don't fix it.
Internet Explorer? It was already broken to begin with. Edge? No one wants it. .Net? Made to compete against Java, now facing competition from browser engines. These products have room for improvement, they need to be worked on. Excel just needs to keep being Excel.
Excel works perfectly as it is, it is a product that sells well, there is no reason to change it. They modernize it from time to time, but they follow what may be the most important thing in production: if it ain't broke, don't fix it.
Internet Explorer? It was already broken to begin with. Edge? No one wants it. .Net? Made to compete against Java, now facing competition from browser engines. These products have room for improvement, they need to be worked on. Excel just needs to keep being Excel.
1. Excel never had a viable competitor that Microsoft needed to use their OS monopoly to exclude.
2. Excel sells for a good amount of money and they wouldn't want to cut that off.
2. Excel sells for a good amount of money and they wouldn't want to cut that off.
One of my about 10 year old accomplishment is creating a single sheet to convert numbers to words (123 to One Hundred Twenty Three) in excel without using VBA or Macro. Our IT didn't allow that pesky dialogue saying Enable Macro or something.
Its a two part sheet. One table sis a simple two column lookup where 1 2,3,...9,11,12....19,30,40,....90,100,1000,100000... etc are in front of One, Two, Three etc. All the vocabulary required to write a number.
Second table is a 9 row table, each row working on a digit of maximum number of 9 digits. First row works on 1st number of 9 long number. If its a smaller number like 12657, the first 4 rows return blank string. 5th row works on 1 of 12657. Kind of like loop. Every third from left gets Hundred, Thousand, Million word etc. Every 2-3 etc gets translated if its 11 to 19 or 20+1.
Since 10 years it has worked nicely, and everytime I use it to convert numbers to words for cheque printing, I feel good.
I wrote a bit about it here https://davinder.net/excel-numbers-to-words-no-VBA-part-01/
Its a two part sheet. One table sis a simple two column lookup where 1 2,3,...9,11,12....19,30,40,....90,100,1000,100000... etc are in front of One, Two, Three etc. All the vocabulary required to write a number.
Second table is a 9 row table, each row working on a digit of maximum number of 9 digits. First row works on 1st number of 9 long number. If its a smaller number like 12657, the first 4 rows return blank string. 5th row works on 1 of 12657. Kind of like loop. Every third from left gets Hundred, Thousand, Million word etc. Every 2-3 etc gets translated if its 11 to 19 or 20+1.
Since 10 years it has worked nicely, and everytime I use it to convert numbers to words for cheque printing, I feel good.
I wrote a bit about it here https://davinder.net/excel-numbers-to-words-no-VBA-part-01/
In the early 2000s, I built wonderfully complicated 'business intelligence dashboards' for a company, which pulled together large sets of data into charty views that could be configured with drop-down selections. It was user-friendly, and easy to maintain. They also used it for end-of-year reports which were more-or-less automatic based a few simple drop-down settings. Nice on the eyes and everything.
They're still using it!
They're still using it!
I worked at several companies where the main competition is Excel and competing against it sucks because you have to change how people work to get them to use their product and people don't like that. They come up with "deal breakers" about why the software won't work for them and then imply that if you fix those things, they will adopt it. This is always false. I'm hoping my next job has another company as its main competitor instead.
You have to have a compelling reason for changing away from an excel spreadsheet from a users perspective.
If you give users their exact spreadsheet but in a web interface, you have probably given them zero benefit and removed all flexibility (i.e. what would have been a new column in three seconds is now a feature request to IT).
If you give users their exact spreadsheet but in a web interface, you have probably given them zero benefit and removed all flexibility (i.e. what would have been a new column in three seconds is now a feature request to IT).
Yes, I've sat through all the meetings and customer calls where we talk about uncovering pain, addressing pain to deliver value. The customers are thrilled. Everyone is high-fiving.
Then they go live and no one uses the service. Then it turns out there are "deal breakers" that mean they can't use the app. It's the same story over and over.
Then they go live and no one uses the service. Then it turns out there are "deal breakers" that mean they can't use the app. It's the same story over and over.
Also, nobody got fired for buying IBM. Or Microsoft, these days.
I was on a trading floor 2 weeks ago and every trader workstation I saw had an Excel session running. Of course they were also running bank approved production systems, but every one of those have an Export to Excel option. Traders like control and don't like to be held hostage to IT groups that "take too long" to write code.
Microsoft has been piling tons of money into Excel in recent years to make it even more useful. Dynamic arrays are a game changer and they have recently released a slew of new array functions to leverage off of that.
The ancient C SDK is still supported to enable adding functions that can call C/C++/Fortran code. I wrote a library to make that simple: https://github.com/xlladdins/xll.
Every non-trivial enterprise software project eventually need to add support for upload and download of Excel files.
I would argue that CSVs never die and most people simply do not know how to use `grep`,`sed`, or `awk`
Links to learn? (awk specifically - I know some sed because of vim, and grep is easy)
The creators of awk wrote an excellent book on it [0]. Also, the man page is good for quick reference.
[0] https://archive.org/details/pdfy-MgN0H1joIoDVoIC7/page/n3/mo...
[0] https://archive.org/details/pdfy-MgN0H1joIoDVoIC7/page/n3/mo...
I use Excel every day. I just wish it had native support for Python or similar when it comes to scripting. VBA is OK - but it would be nice to get a more programming/script-driven Excel with support of languages you use for other things too.
Right now the way we go about this, is kinda hacky. But I have non-tech collogues that often request some methods or similar that I've developed, and that usually involves importing / manipulating / exporting excel files in Python. Would be much easier if they could just run the code within Excel, and get the desired results.
Right now the way we go about this, is kinda hacky. But I have non-tech collogues that often request some methods or similar that I've developed, and that usually involves importing / manipulating / exporting excel files in Python. Would be much easier if they could just run the code within Excel, and get the desired results.
In my experience, no matter how fancy or automated the data pipeline is, all decision makers want the data in front of them in Excel eventually. Excel is king.
As an accountant, I can confidently say that excel has at least another five years, more than likely more of being essential in the business world. It's one of our most used and basic apps in accounting. Gusto, paychex, ADP, netsuite, QuickBooks, haven't really come close to replacing it yet in terms of market share I speculate.
>> Klunder’s innovation meant that instead of having to recalculate (recalc) every cell every time a cell changed, Odyssey only recalculated the affected cells.
I don't believe programmers were that bad in 1985 to recalculate entire sheet if one number changed. We had LISP and Prolog and a lot of other smart stuff by that time.
I don't believe programmers were that bad in 1985 to recalculate entire sheet if one number changed. We had LISP and Prolog and a lot of other smart stuff by that time.
I wrote payroll software for my dad’s company when I was 14 that did paychecks, attendance, indemnity, advances, headcount etc. He says it’s the best software he’s ever used, still using it after 15 years. He has tried a bunch of alternatives but nothing is even remotely as accessible.
I've always hated excel. I'd much rather use R or Python. My first job used an Excel macro to automate tasks in a DOS emulator for some legacy software. It would take days to complete and would regularly crash. As they say, if all you have is a hammer everything looks like a nail.
Give me a slow, brittle, complicated, poorly documented app written in X by a bunch of people who have long since left the company, and I'll show you a way to make a person hate X.
It doesn't matter what X is.
And it's a shame, because improving or replacing those legacy X's can be a big opportunity, if the person is set up to seize it in the right way.
It doesn't matter what X is.
And it's a shame, because improving or replacing those legacy X's can be a big opportunity, if the person is set up to seize it in the right way.
I mean, but that same measure, you're advocating using R/Python as a hammer for everything. Python/R are great languages and tools, so is Excel, all three can be used in fantastic or awful ways.
I never said it sohuld be used for everyhting. Excel it great for some simple tasks. But since the learning curve for those simple tasks which Excel does well is so low, people stick with it and start doing all kinds of stuff it isn't well suited for.
If important data for a business is in Excel, that company has a data problem.
There's no playback to previous states, there's no control over the flow (because there isn't any flow), versioning has to be bolted on and security is managed by the user.
Excel is a liability.
There's no playback to previous states, there's no control over the flow (because there isn't any flow), versioning has to be bolted on and security is managed by the user.
Excel is a liability.
[deleted]
> Excel may be the most influential software ever built.
I believe this to be true in business. Other tech may be more widely used eg “email” but the software is created by a variety of different companies (Google, Microsoft).
I believe this to be true in business. Other tech may be more widely used eg “email” but the software is created by a variety of different companies (Google, Microsoft).
Is it Excel that never dies, or is it the concept of spreadsheets in general?
Hmm. Well there's been a spreadsheet program called Excel around for quite a while. But I guess like the rest of Microsoft's programs, it has been updated over time. Maybe this is a Ship of Theseus sort of thing.
"updated". I tried their subscription brand new Excel and compared to my "pay once, use forever" Excel copy form 2007 and apart from the UI because more squarish and you having to pay forever, I have no idea what they changed.
Excel is driving whole computing evolution - if you could count sum of all CEO quarterly bonuses and draw a neat rising graph quick enough, corporations will never upgrade their laptops and workstations.
Bit of Excel humor seems appropriate: https://www.youtube.com/watch?v=xubbVvKbUfY (Krazam)
Krazam is how my 3-minute bathroom break turns into an obscure comedy deep-dive
Excel is great but it needs better version control and diffs. It's so hard to maintain compared to plain text source code.
The video linked in the article has been set to private, anyone know of a public version? Or a brief summary of the contents?
I would argue Excel is the ultimate example of sunk cost fallacy but w/e if people like it, you do you.
Excel lead me to macros, which lead me to VB, to python and eventually to be a software developer. Thanks Excel!
I am loving using coda.io rather than Excel for simple data and documentation needs.
What? No one has shared this link yet?
I was sure the whole content of the article and of the discussion here would boil down to this:
https://m.xkcd.com/1667/
I was sure the whole content of the article and of the discussion here would boil down to this:
https://m.xkcd.com/1667/
And, "white collar" created Excel!
... just multiplies.
Right now I'm doing a project where I'm taking these XML files that store all the information for an MMO. Stuff like the stats for items. Quest strings. Item strings. Just literally everything. I'm taking those files and I have to first do a bit of conversion on them. I found an online converter that goes XML to YAML. Now follow me here, it sounds dumb, but then I take that output and use a different website that goes from YAML to XML. This formats things in these schema lacking files way better. Then I do some find and replace in VS Code to put quotes around the values and also find any semicolons and replaces them with "" since in the original XML there would be semicolon delimited values for things but they don't make new columns because the game doesn't read the values like that. So you end up with something like this.
Excel rules when you have just completely undefined data. Like, the devs just kind of did whatever with these XML files. You can't just assume every "row" will have every "column". Sometimes in the XML files that are a series, like, ItemData-1, ItemData-2, etc, just all of a sudden the "columns" will be a different order from the previous. Sometimes in the same file even. But the weird conversion stuff I did at the start somehow fixes this issue or at least prepares the XML better for Excel to import it better.
By the time I'm done this will be multiple gigs of sheets but I'm fairly certain Excel and Access can handle it.
Edit: The quotes allow me to do a neat thing when I encounter a cell that has multiple entries in it. I have a series of cells that do some math and text parsing to find out how many quotes there are and leave a number behind for another cell to do math with and basically in the end you get, for example, the correct amount of secondary item rolls, each with their ID numbers, which another cell does a lookup to get the string of the roll and then applies the value for the roll, example "Does $Value extra damage from behind", it will replace $Value with the correct number and then have the corrected string. Yes, many things are broken out into multiple cells instead of doing them all in one cell. I don't know yet if I'll need those intermediate steps for something else down the line so I'll be doing it a bit messy like that.
<ItemData>
<Item>
<Id>"1"</Id>
<Attributes>"1234""1235""1236"</Attributes>
<OtherThing>"Stuff"</OtherThing>
</Item>
</ItemData>
Excel rules when you have just completely undefined data. Like, the devs just kind of did whatever with these XML files. You can't just assume every "row" will have every "column". Sometimes in the XML files that are a series, like, ItemData-1, ItemData-2, etc, just all of a sudden the "columns" will be a different order from the previous. Sometimes in the same file even. But the weird conversion stuff I did at the start somehow fixes this issue or at least prepares the XML better for Excel to import it better.
By the time I'm done this will be multiple gigs of sheets but I'm fairly certain Excel and Access can handle it.
Edit: The quotes allow me to do a neat thing when I encounter a cell that has multiple entries in it. I have a series of cells that do some math and text parsing to find out how many quotes there are and leave a number behind for another cell to do math with and basically in the end you get, for example, the correct amount of secondary item rolls, each with their ID numbers, which another cell does a lookup to get the string of the roll and then applies the value for the roll, example "Does $Value extra damage from behind", it will replace $Value with the correct number and then have the corrected string. Yes, many things are broken out into multiple cells instead of doing them all in one cell. I don't know yet if I'll need those intermediate steps for something else down the line so I'll be doing it a bit messy like that.
I was horrified to find that even with the supporting scripting capabilities, the entire paradigm revolves around knowing the shape of your data in advance. (I was using Google Sheets, but I don't think Excel would have been much different). For example, it is very non-intuitive to write a formula that retrieves all the rows in another sheet that match this rule, and once you do that, since it's a variable number of rows returned, it is difficult to then operate on that data without filling your formulas down for some indeterminate number of rows.
I realize most people don't have the luxury or skills, but I quickly realized that I could spin up a whole CRUD webapp for this problem faster than I, someone who understands indexing and windowing and such, could build it in a spreadsheet.
After this experience, I can't help but wonder if Excel and spreadsheets largely exist due to pre-existing knowledge about how to use them, or if this is _actually_ the best way for non-programming minded people to solve these problems.