[LegitGandalf]

Tables are probably the most overlooked feature of Excel.

Why use tables?

* Each column is uniquely named - no more wondering if you are referencing the right cell, no more thinking about "to $ or not to $"

* The table's rows and columns are reliably discovered by pivot tables - no more wondering if the entire dataset is referenced by the pivot

* New columns that are formulas are automatically applied to every row

* Tables have names, so it is easy to understand which table a pivot is referencing

The true, reliable and sane power of excel lies in Tables + Pivots + Charts. If you drive most of the problem solving into those paradigms you will keep hair!

[tomnipotent]

Most people have no clue these features exist, or that Excel even has advanced data modeling that supports all sorts of goodies including strict types and joins/merges/appends. It's not as accessible as just basic sheets, but I've had no problem crunching millions of rows of data in Excel with sub-second response times.

[piyh]

Powerquery is great, but too bad it breaks with version upgrades.

[jjtheblunt]

isn't excel limited to just over a million rows?

[tomnipotent]

Excel 2013 and later has a columnar database capable of handling millions of rows, but not through the standard sheets interface so it loses a lot of the utility people are used to.

[jodrellblank]

How? Through what interface, or by what feature name to Google?

[easton]

Excel refers to it as the “Data Model”. It allows joining tables/sheets and referencing them from other tables.

https://www.microsoft.com/en-us/microsoft-365/blog/2012/08/2...

[jodrellblank]

Thanks :)

[blowski]

Yes, although you can have multiple sheets each with a million rows. Kind of like sharding.

But I’d be fascinated what kind of system spec is required to get good performance on those kind of numbers. I’ve been on a Mac for years, where Excel is crippled by limitations.

[LegitGandalf]

Working with a Mac for the first time this year, excel on Mac is actually worse than excel 2010 :(

[feoren]

Tables have that all-too-common symptom of something that makes easy things even easier, but hard things way harder. Try making a table with a formula that involves more than the single row in which it's placed. What about tables with multi-row headers (e.g. title and units)? Section breaks in them? Merged parent data? I find I either want standard Excel layout, or a database. Tables don't really sit "between" those two; they're just their own extremely over-simplified universe that doesn't play nicely with anything else.

But hey, to each their own. Good on ya if Excel tables are what you need.

[bradstewart]

This, combined with judicious use of named ranges makes for much more pleasant formulas. Seeing `tax_rate` in the formula instead of `A$7$` is well worth the extra clicks.

I usually end up assigning names to nearly everything, single-cell constants, user input fields, computed lists, etc, etc

[marcellus23]

Apple's Numbers is table-oriented by design and I love it for that reason.

[Zababa]

About tables, do they work when you automate the data entry part? For example, copy from a speadsheet into your spreadsheet with the first macro, and then transform the data with a second? That's 90% of my use case for Excel. (I know that doing this in Python/whatever would be "better" but I have to distribute this to users, and the only thing they have on their computer is Excel, and they're gonna manipulate the data after in Excel anyways).

[correcthorse123]

I think Libreoffice Calc supports python integration. Maybe that way one could have the best of both worlds. Unfortunately I suppose most users are stuck in the MS garden.

[Arrath]

They should...I think.

If anything add a 'ActiveSheet.Calculate' call to the end of your second macro and give it a test run?

[pupppet]

I've been using Excel on and off for years and had no idea that tables were a thing. I just assumed the entire sheet was a "table".

[Akronymus]

Oh excel...

What a love hate relationship I have with it.

On the one hand, it is great for doing a quick and dirty analysis with data not in a DB, on the other, it mangles data and translates the keywords/function names.