"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
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.)
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.
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
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.
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."
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.
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.
> 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.
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'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.
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.
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.
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)
> 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%”
Is this a criticism of Excel specifically, or is the problem that it’s making basic arithmetic more accessible to institutions with negative externalities?
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.
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.
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.
> 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.
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.
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!
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.
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.
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.
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.
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.
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.
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.
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).
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.
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.
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.)
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
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
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.
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.
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.
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
I recently sat down and built something more complicated than simple accounting in a spreadsheet. It's what I considered to be a pretty typical usecase for a non-math related sheet; taking in several tables of data and selectively joining them. You enter an ID, press a button, and it finds all of the data related to that ID and presents it to you.
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.
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 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.
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?
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
> 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.
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.
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.
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.
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.
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).
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.
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 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.
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.
>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
=[@[Amount]] * 0.2
and it will automatically fill down the Tax column with 20% of the value of the Amount columns. Then in a cell outside the table, do
=sum(ExpensesTable[Amount])
to get the total amount of expenses. These are both simple examples; you can do more complex and interesting things involving multiple columns, ranges of columns, joins, etc. The point is the semantic structure that makes your spreadsheet more than just a rectangular soup of cells, so you don't have to claw through endless cryptic "G70:$K100" cell references. If we add a new row or column, we don't have to alter any formulae at all; the bounds are automatically resized on the cell arrays that the column names refer to. Think of it like a mutable resizable dataframe. It's the core data structure of an efficient, scalable, maintainable Excel document.
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?
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 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.
> 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".
> 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
>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.
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).
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.
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.
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.
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 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. :)
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.
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…
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.
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.
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.
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.
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 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.
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.
281 comments
[ 2.7 ms ] story [ 281 ms ] threadhttps://github.com/taviso/123elf/wiki/Getting-Started
https://lock.cmpxchg8b.com/linux123.html
If all you have is this hammer, check out https://github.com/PerditionC/VBAChromeDevProtocol to automate workflows in the browser with Excel VBA.
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.
https://hypercard.org/
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.
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.
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.
Excel truely is the universal data programming tool.
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.
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.)
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.
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.
[1] https://stackoverflow.com/a/1220118
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
and it will automatically fill down the Tax column with 20% of the value of the Amount columns. Then in a cell outside the table, do to get the total amount of expenses. These are both simple examples; you can do more complex and interesting things involving multiple columns, ranges of columns, joins, etc. The point is the semantic structure that makes your spreadsheet more than just a rectangular soup of cells, so you don't have to claw through endless cryptic "G70:$K100" cell references. If we add a new row or column, we don't have to alter any formulae at all; the bounds are automatically resized on the cell arrays that the column names refer to. Think of it like a mutable resizable dataframe. It's the core data structure of an efficient, scalable, maintainable Excel document.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".
https://en.wikipedia.org/wiki/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.
They're still using it!
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.