Excel never dies (2021)
notboring.co
notboring.co
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.
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?
This is basically what Powerquery is, and it's been built into Excel for years. And it does other things too.
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.
Would be easier to see on an example.
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.
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 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.
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.
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 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.
Tables can also be joined and queried using Power Query.
Excel is still 100x more powerful and sophisticated than Sheets is.
>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).
> 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.
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.
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.
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.
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.
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.
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 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.
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.
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".
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.
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).
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.
Sure sounds like your creating a relational database in a spreadsheet, which is possible but not really the intended purpose?
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...
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 because excel was "low/no code" before it was a tech meme with vc money.
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…
Also, Access isn't available with all Microsoft Office licenses. Excel is.
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.
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.
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.
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 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
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.
Except for the back-end, which is COBOL.
P.S we actually do read all the feedback people leave in the feedback box - it goes mostly straight to the devs.
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.
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?
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!
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))`.
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.
Both are requiring me to open files stored on Sharepoint via the desktop app frequently.
- 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.
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
)
)
Expand the bar (or drag the vertical resize handle at its bottom edge), and then you can use alt+enter to insert a newline.
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.
=LET(
foo, LongExpression,
bar, OtherLongExpression,
baz, AnotherLongExpression,
bax, YetAnotherLongExpression
IFS(
AND(foo > 3, bar < 5), baz,
AND(foo > 5, bar < 10), bax,
TRUE, 0))
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?
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.
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.
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?
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...
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.
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!
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.
You would be a hero to thousands and thousands of junior bankers if you made this change lol.
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.
- 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.
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.)
The difference, of course, is that Excel wasn't abandoned by its owners like Hypercard was.
I feel confident in my evaluation that PowerApps is in a completely different universe of accessibility and complexity from Hypercard.
It will come.
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."
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.
Or you hire someone who can shape the business processes towards efficiency with the tools you already have...
I currebtly see people using Palantir as the default for anything and everything, only to export stuff from Palantir to Excel anyways.
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 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'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"
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".
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.
That would be visicalc then.
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 hate to break it to you, but that applies to everything from bricks to aircraft carriers.
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.
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.
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 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.
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.
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.)
If all you have is this hammer, check out https://github.com/PerditionC/VBAChromeDevProtocol to automate workflows in the browser with Excel VBA.
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 regularly compile 35 year old C code.
Standards are great like that.
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.
https://docs.microsoft.com/en-us/office/troubleshoot/excel/w...
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.
Excel truely is the universal data programming tool.
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.
That said, most businesses can run on spreadsheets.
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 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 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.
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. :)
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).
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.
> 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".
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.
2. Excel sells for a good amount of money and they wouldn't want to cut that off.
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.
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/
They're still using 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).
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.
[0] https://archive.org/details/pdfy-MgN0H1joIoDVoIC7/page/n3/mo...
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.
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.
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.
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.
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 was sure the whole content of the article and of the discussion here would boil down to this:
<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.