MrExcel Publishing
Your One Stop for Excel Tips & Solutions

I Am Not a Fan of Excel Tables


March 31, 2018 - by Bill Jelen

I Am Not a Fan of Excel Tables

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

Define the data as a Table
Define the data as a Table

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.

Beautiful formatting
Beautiful formatting

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.

See headings without freezing panes
See headings without freezing panes

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

Type a formula in a table...
Type a formula in a table...

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

Update

IvenBach from the MrExcel forum notes that you can turn off the alternate table formulas with File, Options, Formulas, Working with formulas, Use Table Names In Formulas and un- checking the option.

...the formula copies to the bottom of the table.
...the formula copies to the bottom of the table.

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 is based on the table
The chart is based on the table

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

Awesome features of Tables
Awesome features of Tables

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.

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.

Excel Thought Of the Day

I've asked my Excel Master friends for their advice about Excel. Today's thought to ponder:

"Know that Number Formatting is a Facade So You Won't Get Tricked : )"

Title Photo: Alexas Fotos / Pixabay


Bill Jelen is the author / co-author of
Microsoft Excel 2019 Inside Out

Dive into Microsoft Excel 2019–and really put your spreadsheet expertise to work. This supremely organized reference packs hundreds of timesaving solutions, tips, and workarounds–all you need to make the most of Excel’s most powerful tools for analyzing data and making better decisions.