Comment by feoren | Hacker News Reader

Comment by feoren | Hacker News Reader

Parent Full thread

> they should be creating tables

Tables are a wart on Excel. They don't fit the established idioms at all. There's a very long list of common & simple things that either break or get very clunky with tables, including:

- Multi-row headers - Merged-cell headers - Headers with the same name (sometimes useful) - Formulas that cross rows (e.g. iteratively refer to the previous row) - Different table sections (e.g. a table-width merged row with one header) - Merged rows

The benefit of tables is ... what? Slightly simpler formulas when all operands are in the same row? More automatic (and annoying) formatting? Almost everything people try to do with Tables is actually easier without them, and if it's not, you really just want a database.

Merged cells and multi row headers make data processing very difficult. These are best avoided in any sheet doing any computation. Only for reporting they are useful, especially when auto-generated as part if a pivot table. But I have never seen a legitimate use of merged cells in a computation.

Row referencing formulas work fine in tables, but there might be better ways to achieve your goals if you need that a lot.

Other benefits are input data type checking, auto "freeze panes" for header row, much easier plot and pivot tables, niver formatting, summary rows if needed. Best is of course referencing columns by name

Oh hard disagree, friend.

I know “Excel is not a database”, but at work it often has to be. Using table notation to reach across to other tables is massively easier than trying to remember which column your data is in. You can INDEX(MATCH()) that without moving from the cell you’re in.

I guess it depends on the type of data you’re dealing with because all those things you list are things I’ve never wanted.

Have you tried XLOOKUP?

I like it more than index match.

Well I work in an industry which still uses an archaic version of Excel. 2016 for Windows I guess? No XLOOKUP for me, yet. But yeah I’ll switch when it’s available.

INDEX(MATCH()) is clearly a janky hack. But once you get used to it, it works.

Reply on news.ycombinator.com