Is It Worth Converting Data to a Table?

They have been pushing Tables pretty strongly for a very long time. Since 2007.

And still, anecdotal-based research not intensively done by me shows they are not winning hearts and minds.

And the Spill functionality they are so proud of and for which they provided such little way to avoid if you hated it... it won't work in Tables. To accentuate the importance of that, Spill is supposed to be something no one could reasonably ever not want to use and yet, there's not even the suggestion that it and Tables will be made compatible somehow, or someday. Not even a hint. In fact, the impression is quite the opposite: that they will never be compatible.

So... opinion, either considered, reactionary, or in between, seems to be "Screw this!"

Clearly then, it ought to be a "whichever works best for you" kind of thing. Except... it isn't. There is nothing done to help the non-Table way and plenty done to enhance in small ways the desire to switch over. Using slicers on data is nice. But... just nice. Not like a choice between a leg and no leg, or even a limp or no limp. Banding's nice, but it is messed up, a lot of the time. You cannot insert a partial row even if it is across the entire Table because... well... because. (So the Table is five columns wide. No inserting cells in columns A-E for that sheet in the rows the Table exists on. Whole rows? Sure. But not just in the Table. Strange. Add something right below a Table and it grows. Great. Paste seven cells in the first column right under the Table and it grows down... for that column... including formatting and being able to be referred to with Structured Referencing ("SR" if I mention it again). Except... it doesn't grow to the right of the pasting. Yet it considers those rows part of the Table if you want to insert some cells.

The point then is that while they push it hard, and always, they won't even keep them in tune with the "huge" trends the program is taking nor do they do anything to improve them. It's almost as if they intended them to be a gap-filler between the wild world of feral spreadsheets and upcoming work like Power BI which they clearly hoped to shift people to 'cause mo' money, mo' money... with their everyone pays a subscription with each AND every little task they are ever used for rather than just one to use Power BI. Someone's making up a Super Bowl Pool? Company's paying for 30 subs and maybe they don't get cancelled because hey, gonna do it next year too, right? So 30 subs over 12 months... gotta beat 30 peeps in the pool, but they all already paid for their Excel sub so no extra money.

When they PROPERLY bridge the Spill and Table gap, I'll consider Tables again. Until then, my hammer is Excel, not Tables, and everything keeps successfully looking like nails. Or moles. Either one, hammering seems to keep working.

BTW, I'd like to emphasize something Mr. Wyatt said: Structured references can entail a STEEP, EVEN FREAKINGLY STEEP, learning curve.

They are NOT "English" at all for folks not brought up in the programming world. And all the places they failed to think things out before implementing them... well, those bits of SR's are REALLY not "English"... REALLY NOT. The LET function solves ALL of my needs for referencing.

Of course, I don't do LET naming the way so many seem to. I have terms like SumsToAverage, not "save" or "q" or... etc. They really missed a chance with LET. It could've been a cell-level Named Range and the Named Range concept could've connected into it. Ah well. They miss these things all the time. Every cell could be a formal array, rather than a cell with contents. Formal array ready immediately for use and addressing as such. I know they shoehorn lots of extra we don't see into cells, but supposedly that gets abstracted "out" nowadays with the "new" .xlsX file format so the program ought to be able to differentiate between formatting and... such-like... and actual data. Basically, they've missed opportunities before.

And Tables are one where they blew it. Unless you are new and so why not learn it instead, or at least first, and as your go-to tool, or in an organization that can force it into place and accepts the shortcomings rather than hammering you down for them, why would you bother? All the rest of Excel is still there for you to incrementally make use of. No high hills to climb for initial, basic skill and functiuonality. Oh, and not designed to force you down a subscription-for-every-little-thing-you-write path.

I find structured tables wonderful when row cell formulas depend on multiple other row cells for their value. In a structured table cell formula, the formula will use the column header name in the formula rather than the A1 reference. You will be able to read your formula "in English" rather than as A1 cell references. Your formulas will require less brain power to understand and if you use the Excel evaluate formula feature you will see named fields, rather than A1 references, as the evaluation takes place.

Check out Excel Tips on June 7, 2021 and you will see how to use Conditional Formatting to highlight every second row (banding).
You can insert or copy one row to another etc. and it doesn't change the the row colours.
You can choose the colour and opaqueness to make reading easier.

Actually, the row banding does not always adjust automatically and when it does not, I have had to go so far as to revert to a range and re-Table to fix it.

I have not studied the circumstances, so have no solutions to offer past "whatever works this time" but the problem exists.

However, I have no personal judgment to offer on which situation offers better banding performance, but will say I do not use banding outside Tables even when I would like to because it's usually a pain to put in place. So I'd say Tables win that one, at least for me.

Banding is nice for those of us who do not go color crazy in normal and Conditional formatting. I imagine it's a real paint though for those who love their dark color backgrounds and dark color text that can't be read. And apparently many do so... I wager they vote differently on banding at all.

On the detail of row banding.
With a table, if you insert another row, then the banding adjusts automatically. While it's possible to do row banding with a non-table sheet layout (using more than two colours if desired) I've found that there cam be problems when inserting rows, or copy/cut/pasting data from one row to another - make sure to use "paste values" not simply "paste", which may take the wrong colour with it.

I have worksheets where table format has been very helpful, and others where I don't use it

Allen mentioned not playing nicely with sheet protection, which is one reason why I'm not using it with one set of workbooks where it would otherwise be nice to format at a table

Here's a HUGE HUGE HUGE obnoxiousness to Tables:

You cannot use SPILL functionality with them.

And that's often, now, and becoming more so, " 'NUFF SAID. "

But I'll say one more thing, then roll on out of here for a bit: Those structured references... Jesus people, those things can be MONSTERS to read and so to use!!!! And that's without you having used multi-line headers. Worse by far then. NIGHTMARES. And they'll change over to regular formulas in a heartbeat if you're pasting material in and paste over just one of them in a column with 100,000 rows. No warning either which seems ATROCIOUS after they've been in play for 14-15 years and in MS's hands while writing the functionality for longer.

By the way, ODDLY enough, sometimes it is not really clear how to get to that silly little name box for the Table in which one simply makes a wee little typed edit to change the name. It can be weird and non-straightforward sometimes and make one ache for the programming concepts that Outlook carries to have never entered MS's culture because it feels like that kind of thinking is behind the (sometimes) hiding of that little box. My bet is what is meant when someone says "cumbersome" is that hide-and-seek game we sometimes have.

You know, because the Ribbon MENU system does not include "Table Design" until you have a Table and then at the end. 15" from "Insert" that you might've used to create the table and might be forgiven for thinking is the place to look for it.

By the way, an aspect of the Outlook programming culture is say you want to use the BCC field in lots of emails. So you'd go to the program's master options and set something right? Oh, must've missed that in all the little offerings, better hunt through them again and take three times as long this time. Huh. Well, the internet will tell me how then. Hunt it up. Find out that to change it for EVERY email from that point onward, you have to create an email and change it in that SINGLE email, which will then force it to change for ALL emails that follow. Talk about context-sensitive programming. If it were the old context-sensitive help, it would be context-sensitive help...lessness. The same context-sensitive programming is at FAULT here too. Stupid and idiotic. Freaking stupid and idiotic. Put that MENU tab somewhere close to "Insert" anyway, maybe color it to indicate it is context-created and therefore unusual, maybe needs noticed.

"Changing table names can be cumbersome."
Balony - it's as simple as clicking the Table Name field in the Properties tab of the Table Tools menu, and entering the new name!

Two other significant disadvantages of tables is that:
1. you cannot use formulas to generate the field headings (a technique that is very useful for reflecting dates or other variable components in headings)
2. every field heading in the table must be unique, which can cause problems if using the table for analysis of various options with similar fields.

Table is a great precursor to Pivot table.