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
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.
I like it more than index match.
INDEX(MATCH()) is clearly a janky hack. But once you get used to it, it works.
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.