I Am Not a Fan of Excel Tables

March 31, 2018 - by Bill Jelen

Press Ctrl+T on a data set and all sorts of wonderful happens. While I can spend an hour telling you how wonderful Excel Tables are, a few annoying incompletions make me avoid them. Learn why in today's article.

How many times do you have a tiny database table in Excel? Headings (field names) in row 1, each row in the table represents a record. Select that data and press Ctrl + T to invoke the Format as Table command. (Note that Tables debuted in 2003 as a List, so Ctrl + L also works).

Look at the beautiful 1970's era every-other-row formatting! I love that formatting.

And look... Excel assumes I am a moron who can't turn on the Filter when I need it, so they turn it on for me. How nice.

If I would happen to scroll row 1 off the page, those useless column headings of A B C D E F are replaced with actual field names. Again, Excel is assuming I am too busy to be able to Freeze Panes so they sort of did it for me.

I seriously respect that if I (a) think ahead enough to format the blank H2 as a percentage with 1 decimal place, (b) type a GP% formula in H2, and press Enter ...

...the formula automatically copies. I really love this one. I don't like that the formula is [@Profit]/[@Revenue] instead of =G2/F2. But I like that I don't have to double-click the fill handle to copy the formula down.

But here is the absolute best feature about Tables. Let's say you have a table and you've used that table for any of these:

• the source data for a chart
• the source data for a pivot table
• a VLOOKUP or HLOOKUP table (or the second argument in MATCH for you INDEX/MATCH fans)
• the source for a defined name

The best feature in a table is the tiny end of table marker shown in H21 below. If you copy new data and paste that data in the first blank row below the table...

... the chart automatically updates. The new data is formatted. The formula in column H is copied down. This is a beautiful feature.

Other great reasons to use Tables:

• Pivot tables based on the Data Model require your data to be in Tables
• Power Map (now known as 3D Maps) require tables, although the Power Map team nicely will convert your data behind the scenes if you don't

So - why don't I use tables?

Tables were a massive undertaking during the development cycle for Excel 2007. That was the year that Excel moved from menus to the Ribbon. There was too much to do. Legend has it that the Excel team decided, "Tables will never work. We don't have time to finish them. Let's take them out of the product."

As that news was communicated throughout the development team, word came back: "No! We are too far in. It would take more time to remove Tables at this point than we have."

So, Tables are in Excel. And all of the goodness that I described above is in Excel. All of that is good and I will use Tables when I know that I need a chart or a pivot table to expand with growing data. But it is the things that aren't finished that cause me to not use Tables every day. I understand that the items in this list are obscure. Many people don't know about them and don't use them. But I use them. I use them a lot. These are enough to prevent me from using Tables all of the time.

• Custom Views won't work if you have a table. I use custom views to unhide 20 worksheets in one command.
• Subtotals won't work with a table. I meet a lot of people who hate Subtotals. I love Subtotals.

If you never use subtotals and don't want to learn why Custom Views can help you, then Ctrl + T tables are awesome.

Every Saturday, an admission of guilt. I will reveal my bad habits in Excel and discuss why you should do what I say instead of do what I do.

Title Photo: Alexas Fotos / Pixabay