Differences and limitations of converting data to a table

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