Is It Worth Converting Data to a Table?

Written by Allen Wyatt (last updated December 25, 2021)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365


6

Jen has a ton of data in a worksheet. She can format that data as a table (Format as Table from the Home tab of the ribbon) or leave it simply as data. She notes, however, that she can find no clear information as to the pros and cons of converting that data to a table. So, she wonders why she should convert it if she's successfully used the data in non-table format for years.

If you would like to read up on various pros and cons of tables, you will find the following web pages helpful:

https://support.microsoft.com/en-us/office/overview-of-excel-tables-7ab0bb7d-3a9e-4b56-a3c9-6c94334e492c
https://www.intheblack.com/articles/2018/08/01/understand-excel-format-as-table-icon
https://www.powerusersoftwares.com/post/2017/09/11/12-reasons-you-should-use-excel-tables
https://www.bpwebs.com/10-benefits-of-excel-tables/
https://sparrowsolutions.ca/keyadvantagetable/

If you prefer to see the advantages of a table visually, then the following video from Mynda Treacy is very informative:

https://www.youtube.com/watch?v=Du73CPqWGQw

The biggest thing to remember with tables is that they treat your data in a structured manner that is more akin to a simple database than to a plain-vanilla Excel worksheet. The advantages of tables can be summarized as follows:

  • Easy filtering and sorting using the header row.
  • Easy to manually rearrange rows and columns.
  • Easy application of slicers and timelines.
  • The header row is always visible as you scroll through lengthy tables.
  • Easy formatting, including row "banding."
  • Easy application of formulas in a column.
  • Easy application of totals in a row.
  • Tables automatically expand as you add rows and columns.
  • Easy resizing of the entire table.
  • Availability of structured references.
  • Easy application of data validation tools.
  • Easy exporting to a SharePoint site.
  • Easy to use with the Power Query and PowerPivot tools.
  • Tables work better (dynamically) with charts, PivotTables, and PivotCharts.

There are disadvantages to tables, as well, though they aren't as numerous as the advantages"

  • The term "tables" can be confusing if you already think of data in a worksheet as a table.
  • Structured references can entail a steep learning curve.
  • Changing table names can be cumbersome.
  • Tables don't always play nicely with sheet protection.
  • Tables use the SUBTOTAL function for totals, so you cannot use subtotals as you would traditionally use them in a worksheet.
  • You cannot use tables with custom views.
  • Tables can, in some circumstances, affect how worksheets are copied.

It should be mentioned that tables may not be appropriate for all data that you may work with, but the only way you are going to discover that is if you try to convert your data to a table and then do some actual work.

Now, as to whether Jen should convert her data to a table, the answer is a definite "maybe." Excel provides multiple ways to accomplish tasks, and if Jen is completely satisfied with the way she works with her data, there is probably very little reason to go the conversion route. If, however, Jen finds the "pros" described above to be advantageous to the way she does her work, then conversion may be the way to go.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (12721) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Changing Above-the-Line Fonts

Word maintains a list of the fonts most recently used in the program. You can't modify the list, but you can turn it on ...

Discover More

Formatting Footnote and Endnote References

Depending on whom you are writing for, you may want your footnote and endnote references to appear a specific way. Word ...

Discover More

Taking the Time into Account in a Formula

Need to check the current time in a formula you are putting together? It can sometimes be tricky to remember what Excel ...

Discover More

Excel Smarts for Beginners! Featuring the friendly and trusted For Dummies style, this popular guide shows beginners how to get up and running with Excel while also helping more experienced users get comfortable with the newest features. Check out Excel 2013 For Dummies today!

More ExcelTips (ribbon)

Fixed-Width Settings when Converting Text to Columns

The Convert Text to Columns capabilities of Excel are very helpful when pulling apart information. When working with ...

Discover More

Accessing Dependent and Precedent Information

The auditing tools provided in Excel can provide some very helpful information about how your formulas and data are ...

Discover More

Using Custom Add-Ins

Once you've created your custom add-in, you need to know how you or other people can use it. Here are the simple steps to ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] (all 7 characters, in the sequence shown) in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is 8 - 5?

2022-01-03 12:56:14

Osprey

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.


2022-01-03 09:37:54

Roy

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.


2022-01-01 15:45:26

John Mann

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


2021-12-27 00:12:33

Roy

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.


2021-12-25 06:12:27

Col Delane

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


2021-12-25 05:10:11

Sandeep

Table is a great precursor to Pivot table.


This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.