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:
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:
There are disadvantages to tables, as well, though they aren't as numerous as the advantages"
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.
Create Custom Apps with VBA! Discover how to extend the capabilities of Office 2013 (Word, Excel, PowerPoint, Outlook, and Access) with VBA programming, using it for writing macros, automating Office applications, and creating custom applications. Check out Mastering VBA for Office 2013 today!
The Analysis ToolPak is used to add some very handy capabilities to Excel. If you don't have it installed, and you can't ...
Discover MoreExcel normally allows you to undo actions you take so that you can "step back" through what you may have been doing. ...
Discover MoreDon't want your form controls to print out with your worksheet? Here's how to make sure that Excel excludes them from ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
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.
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.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2024 Sharon Parq Associates, Inc.
Comments