6 ms·

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

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 5y ago
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 5y ago
  Powerquery is great, but too bad it breaks with version upgrades.
- jjtheblunt 5y ago
  isn't excel limited to just over a million rows?
  - blowski 5y ago
    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 5y ago
      Working with a Mac for the first time this year, excel on Mac is actually worse than excel 2010 :(
  - tomnipotent 5y ago
    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 5y ago
      How? Through what interface, or by what feature name to Google?
      - easton 5y ago
        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/23/introduction-to-the-data-model-and-relationships-in-excel-2013/ https://www.microsoft.com/en-us/microsoft-365/blog/2012/08/2...
        jodrellblank 5y ago
        Thanks :)
Zababa 5y ago
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).
- Arrath 5y ago
  They should...I think. If anything add a 'ActiveSheet.Calculate' call to the end of your second macro and give it a test run?
- correcthorse123 5y ago
  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.
bradstewart 5y ago
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 5y ago
Apple's Numbers is table-oriented by design and I love it for that reason.
pupppet 5y ago
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".
feoren 5y ago
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.
Akronymus 5y ago
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.

tomnipotent 5y ago

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 5y ago
Powerquery is great, but too bad it breaks with version upgrades.
jjtheblunt 5y ago
isn't excel limited to just over a million rows?
- blowski 5y ago
  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 5y ago
    Working with a Mac for the first time this year, excel on Mac is actually worse than excel 2010 :(
- tomnipotent 5y ago
  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 5y ago
    How? Through what interface, or by what feature name to Google?
    - easton 5y ago
      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/23/introduction-to-the-data-model-and-relationships-in-excel-2013/ https://www.microsoft.com/en-us/microsoft-365/blog/2012/08/2...
      - jodrellblank 5y ago
        Thanks :)

Zababa 5y ago

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

Arrath 5y ago
They should...I think. If anything add a 'ActiveSheet.Calculate' call to the end of your second macro and give it a test run?
correcthorse123 5y ago
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.

bradstewart 5y ago

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 5y ago

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

pupppet 5y ago

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

feoren 5y ago

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.

Akronymus 5y ago

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.