[adepressedthrow]

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.

[Jtsummers]

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.

[tehbeard]

> Excel added tables over a decade ago

I ran into this... last year when trying to do something with excel (I forget what excactly, apart from it needing joins and some analysis between several datasets).

It felt so unintuative with the tables "embedded" into sheets, it feels like they should be a sheet or a table, not both.

Power queries seemed a really neat tool for non-coders to munge data as needed.

[dima_vm]

Not sure what you mean by "power queries", but Google Sheets support SQL queries.

Would be easier to see on an example.

[_dain_]

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.

[adepressedthrow]

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.

[RGamma]

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.

[LasEspuelas]

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.

[zmgsabst]

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.

[hermitcrab]

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).

[A4ET8a8uTh0]

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.

[hermitcrab]

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.

[pete_nic]

In this context I’m interpreting DSL to mean “domain-specific language”

[rafael09ed]

Querying Google sheets SQL doesn't support joins

[hodgesrm]

> 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.

[geysersam]

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.

[dijit]

I really despise Microsoft (from an irrational place) and even I have to agree that Sheets is relatively crippled.

Even basic chart types are not supported, I think it might be due to limits of what's possible in the browser.

Granted though, excel can't backed on bigquery.

[miniBill]

Google Sheets is nowhere close to Excel in terms of features, and not just niche ones. The biggest being tables, IMO.

[actionfromafar]

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.

[apancik]

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.

[adepressedthrow]

Interesting. It definitely seems like that would fix a lot of my issues, which prompts the question of why Google hasn't built this functionality

[ghaff]

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.

[rufus_foreman]

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.

[analog31]

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?

[adepressedthrow]

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.

[_dain_]

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).

[taviso]

> 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.

[briandear]

Do Sheets and LibreOffice have Solver? No. They don’t.

[taviso]

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.

[deknos]

LibreOffice does have an solver. but i have no idea how it compares to others.

[_dain_]

>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.

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.

[taviso]

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.

[_dain_]

>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.

[taviso]

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.

[adepressedthrow]

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.

[_dain_]

I wrote another comment that also answers yours for the most part: https://news.ycombinator.com/item?id=32365128

tldr your problem with Excel is that you don't grok tables. They eliminate the need to know the number of rows when writing your formulae.

I don't actually have Excel installed on the machine I'm using to type this, so I can't put my money where my mouth is like the vim guy did[1]. But I'm fairly sure you can achieve your goal with table references and liberal use of the XLOOKUP and FILTER functions. It'll get a little hairy since you have to go from Set -> Inventories -> Inventory Parts -> Parts, so maybe a bit of nesting. But I think doable. The LET function also helps to reduce formula complexity, it lets you make lexically-scoped variables inside your formulae. Use "data validation" to make a dropdown menu for the set names.

[1] https://stackoverflow.com/a/1220118

[smt88]

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.

[Dyac]

I'm a happy customer of https://exploratory.io/ - it's a very user-friendly interface on top of R and I think you might find it helpful.

[jsmith99]

> it is very non intuitive to write a formula that retrieves all the rows in another sheet that match this rule

You can retrieve an entire range of data with a single formula in either excel or Google sheets. The formula is caller FILTER https://support.microsoft.com/en-us/office/filter-function-f...

[adepressedthrow]

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.

[LasEspuelas]

This is something I faced in both Excel and GSheets (when working with formulae only). That is the need to repeat myself occasionally.

[winphone1974]

"Several tables and selectively joining them" ... "Enter an id and filter"

Sure sounds like your creating a relational database in a spreadsheet, which is possible but not really the intended purpose?

[adepressedthrow]

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.

[spywaregorilla]

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.

[Ruthalas]

This is very accurate in my experience, down to the suggested formulas.

[rexreed]

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.

[eastbound]

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.

[_dain_]

>an Excel-with-SQL-querying product

This is basically what Powerquery is, and it's been built into Excel for years. And it does other things too.

[airstrike]

yeah, but it's really not ergonomic relative to the default Excel-with-formulas experience

[dgudkov]

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.

[otikik]

> the entire paradigm revolves around knowing the shape of your data in advance.

This is true, but you can improve things significantly by using named ranges.

Using `$PAYMENTS` instead of `Sheet2!$B2:$B$21` for your column data and `$TAX_RATE` instead of `$Sheet4!$A$1` clarifies things quite a lot.

You still will need to know how your data is structured (e.g. this is a column that goes down, this is a fixed variable) but it is way more readable.

[jsemrau]

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.

[leobg]

I’d really like to hear more about this. Is there a way I can contact you?

[jsemrau]

I have a SUS profile.

[jerry1979]

>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.

[adepressedthrow]

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).

[spywaregorilla]

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.

[occamrazor]

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.

[ab_testing]

> 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

[bergenty]

I don’t all that much about excel but I remember being blown away as a 17 year old kid at my first job when someone showed me pivot tables.

[collegeburner]

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.

[Jorge1o1]

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…

[potatoicecoffee]

Have you heard of microsoft Access?

[themadturk]

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.

[dav43]

Did you use “Query”?