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

[snthpy]

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.

[taviso]

> 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