Join Dennis Taylor for an in-depth discussion in this video Differences and limitations of converting data to a table, part of Excel Tips Weekly.
- [Instructor] If you convert a list of data into a table, you gain some formatting and data handling features. I've got two different worksheets on this workbook that are identical except for the name. I'm gonna convert one of them into a table. Ahead of time I would have checked out the data to make sure there are no empty rows or columns within this list, and I'll simply press Control + T and Excel figures out the extent of the data, I'll click okay and that certainly has a different look. Notice that there's a contextual Design tab that appears above, click outside the table it disappears.
Now for the most part, this feature is considered a valuable tool because, notice as I scroll here, row one becomes the heading and that every other row look is easier to read, and we can also have some other formatting exploration to do above in the Design tab here. Under Table Style Options. But there are a couple of oddities, and one is the absence of a feature that you might have gotten accustomed to if you work with lists. I'm going to go to the previous list here and I'm going to sort it by Product.
So I'll simply click in column B and in the Data tab do a Quick A-Z Sort, I'd like to have subtotals at the end of each product. So, far right on the Data tab, Subtotal and every time there's a change in product I want to see Subtotals for the column with Items and the column with Amount, click Okay. And we will see the subtotals eventually by scrolling up and down or there's outlining symbols on the left hand side there's the two, click it, and there we are. Double click the boundary between G and H so we can see that better, there we are.
That's a valuable tool, I wanna see this, and we can scroll up and down as I bring back all the data with the number three here, so that's valuable. In a table though, that feature, is not available and if there's any downside to a table, perhaps that's the major one. Off to the right, on the Data tab there's Subtotal, but because the active cells are within a table, this feature is not available. Now there's some other differences with tables too, and I think they're not really errors. Or they're not omissions possibly, but you need to get used to them.
One other aspect of this that's a little bit different is that if you were to try and share this workbook. Now it makes no difference whether the active cell is in the table or whether it's on another sheet even. If you go to the Review tab and click Share Workbook, you get the message This workbook cannot be shared, because it contains Excel tables. In this case only one but that's enough to stop it. And so if that's important, I think you'd consider maybe not having a table within this worksheet. If you're copying one sheet by itself, this is a sheet with a table, I can drag this rightward along with the Control key to make a copy of it.
If I'm trying to copy multiple sheets, and one of them has a table. Suppose I was trying to copy this sheet and the one to the right of it, I'll hold down the Control key and click that sheet, and then as I drag this sheet here, with the Control key, you see this symbol that pops up, blocking me from doing that. I don't think that's a major problem that most people couldn't work around, remember you can drag one sheet that has a table in it with the Control key and you've made a copy of it. Now something else that might throw you a little bit, I wanna go back to the worksheet here, and remove the subtotals data, off to the right Subtotal, Remove All, if I want to move one of these rows up or down like this I'd simply highlight the cells, this way, drag the upper edge with the Shift key and some move and insert.
And that might be something that you use often, but then a table it works differently. So here's a table, I might try the same thing here, highlight the data here like this, I'm gonna drag it upward with the Shift key, as I let go of the mouse here, I'm on the way to actually replacing data. Don't wanna do that. Within a table all you need to do is drag an edge. So if I drag this entry up a few rows, now admittedly this is not something a lot of people need to do with a table. Often you would sort the data.
Still you have the ability to do this but you need only drag an edge, don't need the Shift key and that actually does a move and insert, whereas with other data it's a Shift drag. Merge and Center is probably not a feature you'd be wanting to use anyway within a table, but on the Home tab recognize it's just not available at all if the active cell is within a table. Cannot use Merge and Center. And one minor point here, and not really a deficiency in any way, but just a difference. In regular data that's not a table if you wanna highlight the entire body of contiguous data you press Control + A and to highlight the entire worksheet, press Control + A again, relatively easy.
Within a table, when you press Control + A, you highlight everything in the table except for the heading that row one entry here as we see it. Press Control + A again to highlight that. So minor difference there, now press Control + A a third time if you wanted to highlight the entire worksheet. So there certainly are some differences here as we work with table data, but it's a feature you should keep an eye on. If you haven't worked with it make a copy of some data, experiment with it a little bit and as you click within the data, explore some of the options on the Design tab.
It's a valuable tool despite that one shortcoming that some people encounter, you cannot use the Subtotal feature.
Author
Updated
3/2/2021Released
1/16/2015Note: Because this is an ongoing series, viewers will not receive a certificate of completion.
Skill Level Intermediate
Duration
Views
Q: Why can't I earn a Certificate of Completion for this course?
A: We publish a new tutorial or tutorials for this course on a regular basis. We are unable to offer a Certificate of Completion because it is an ever-evolving course that is not designed to be completed. Check back often for new movies.
Related Courses
-
Excel: PivotTables for Beginners
with Dave Ludwig23m 52s Beginner -
Presentation Tips Weekly
with Jole Simmons3h 32m Intermediate -
Excel: Lookup Functions in Depth
with Dennis Taylor1h 8m Intermediate
-
Excel Tips - New This Week
-
Previous Episodes
-
Quick formatting tips8m 9s
-
Create an organization chart8m 56s
-
Auditing9m 1s
-
Adding comments and shapes7m 35s
-
Creating an Excel template7m 57s
-
Adjust banded-row formatting14m 35s
-
Dealing with circular errors8m 20s
-
Sorting tips and shortcuts8m 40s
-
Freeze Panes and Split5m 30s
-
Calculate % of change5m 41s
-
How to adjust names5m 45s
-
Sorting by moving columns4m 16s
-
Using the Solver Add-in4m 29s
-
Create picture links5m 37s
-
Display large values5m 16s
-
Create a powerful macro4m 40s
- Mark as unwatched
- Mark all as unwatched
Are you sure you want to mark all the videos in this course as unwatched?
This will not affect your course history, your reports, or your certificates of completion for this course.
CancelTake notes with your new membership!
Type in the entry box, then click Enter to save your note.
1:30Press on any video thumbnail to jump immediately to the timecode shown.
Notes are saved with you account but can also be exported as plain text, MS Word, PDF, Google Doc, or Evernote.
Share this video
Embed this video
Video: Differences and limitations of converting data to a table