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


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:

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

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


Moving a Worksheet to Another Workbook

One of the organizational tools provided by Excel is the ability to move worksheets from one workbook to another. Here's ...

Discover More

Printing Limited Pages from a Range of Worksheets

Need to print just a few pages from a group of worksheets? The easiest way to handle the task may be through a macro, as ...

Discover More

Microsoft Word's Amazing Autos (Special Offer)

Microsoft Word's Amazing Autos can help super-charge how you create your documents. Another way you can increase ...

Discover More

Professional Development Guidance! Four world-class developers offer start-to-finish guidance for building powerful, robust, and secure applications with Excel. The authors show how to consistently make the right design decisions and make the most of Excel's powerful features. Check out Professional Excel Development today!

More ExcelTips (ribbon)

Using Subtotals and Totals

You can insert subtotals and totals in your worksheets by using either a formula or specialized tools. This tip explains ...

Discover More

Using Check Boxes

Check boxes, just like those used in Windows dialog boxes, can be a great addition to a worksheet. Here's how to add them ...

Discover More

Watching Cell Values

Want to know what is happening in certain cells in your worksheet? Using the Watch Window is a great way to keep an eye ...

Discover More

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

View most recent newsletter.


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 two more than 7?

2022-01-03 12:56:14


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


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


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


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

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.