Join Dennis Taylor for an in-depth discussion in this video Converting lists to tables, part of Excel 2013: Managing and Analyzing Data.
If you work with data that's dynamic, perhaps this HR list that grows and shrinks frequently, there is some advantage to working with a table. You can convert a list into a table, and thereby achieve some formatting capability as well as some data handling capability that's definitely worth a look. This feature's has been around for awhile. It was introduced in Excel 2007. It hasn't exactly caught on everywhere, but you may see a table, and you might want to know how it works.
Some of the examples that have preceded this particular movie did not use a table, you might want to revisit those movies and think of how the data might look if it's been converted into a table. First of all, if we want to use this feature, we've scoped out our data ahead of time. Let's say we know that the data we're working with has no empty rows, no empty columns. And, every so often, if you do work with large lists, you should make sure, in order for your list to be treated as a unit, whether you use tables or not, make sure it's all together. No empty rows.
No empty columns. If we click with inside the data and we want to convert it into a table there are four ways we can do this. There are two key strokes short cuts, Ctrl+L, perhaps you think of list and Ctrl+T for table. And you will also find it twice in the ribbon menu system. On the Home tab, we see it in the styles grip. As I slide over format as table, if you were curious and hadn't heard much about this feature, I think the description here isn't exactly enticing. It says quickly convert a range of cells to a table with it's own style.
Okay, doesn't say a whole lot. On the Insert tab, perhaps a better rationale. Here's that table option. And the description here, create a table to organize and analyze related data. Tables make it easy to sort, filter and format data within a sheet. That's a greater rationale for wanting to use this. Let's click this option. Excel makes a guess and nearly almost gets it right as to all your contiguous data. In this example, goes down to row 762.
Columns A through M. Click OK, and immediately some colors are applied to this range. If I zoom back a little bit, you'll see that it doesn't include cells to the right and ultimately nor below the data. Those are not highlighted like this. We see table styles, we also see a contextual tab up on the ribbon called Design. With lots of choices here. Let's look at the styles first. Quite a few choices here, as I slide over these you see what's happening in the background, and some are better than others.
And you can begin to see and you could imagine also different times maybe changing some of these. So I'll simply make a slight change here and maybe go with this one right here. This feature's referred to as banded rows. You'll recognize that, when the active cell is within a table, we've got this Design tab. You might not prefer banded rows, maybe you'd prefer banded columns, so un-band the rows, and then choose banded columns. I can imagine in some situations, maybe you would prefer this, maybe not. When you try them both together, it seems kind of ridiculous.
I'm going to go with banded rows. No matter which of these you choose, while it is, in effect, your table style options here, do reflect some of those choices. So right now, if it's banded columns. These previews are in banded columns. If it's banded rows, the previews are in banded rows, as you might expect. So that's one aspect of it. We also could give special emphasis to the first column. Now, often this means that simply we'll make it bold. In some cases, it'll apply different colors as well, too. So depending upon the format, and you might slide over some others.
It might use different color backgrounds, and some of these of course, seem inappropriate but you can imagine how that might look too. So some variation on that. Not terribly critical. Now, anytime we add data to the bottom of the table, it automatically becomes part of the table. So, if I press Ctrl+ Down arrow to go to the bottom of this. Now, I'm not going to type in a whole new entry here. But, if I just type in an entry and press Enter, immediately that row becomes part of the table. And any formulas that are in the row above it automatically get copied downward.
Well here's a formula that actually calculates years of service. But since I have not yet put in a higher date, it's coming up with a funny answer. But it will put in the correct answer. In other words it copied a formula from here down into here. And you could imagine how that could be valuable at different times with certain kinds of data. I'm going to undo this, I don't really want to add that, but it just emphasizes the idea if we add rows to the bottom of the table, they automatically become part of the table. And as I press Ctrl+Home, and then move off to the right. Similarly, if we add columns to the right the same thing would happen.
Now, for the moment I don't want to add a column. But, I do want to put in a formula here, and this too has an advantage. I'm going to write this in two different ways. Imagine that everybody's going to get a new salary, or compensation, here. We got existing compensations in column J. We want the new ones here, everybody is going to get a 2.3% increase. Now as I type the formula equals, look what happens when I press J:2. It comes up, certainly doesn't look like J:2 but it's referring to a field called comp which is within this table.
Asterisk, M1. We're going to multiply it by M1, and look at all that. Now, I think a lot of people are intimidated by this, and they immediately say, I don't want to work with these tables. That looks kind of strange. Now, in order for this formula to be complete, we need to add to it the existing compensation. So it really does look long. Look what happens when we press Enter. It puts in the formula all the way down the column. And furthermore, it used absolute reference there. First order of business here might be to clean up the format, so we can click column L, hit the Comma button.
We might or might not want to show the pennies. For the moment, let's just hide those by decreasing the decimal. But let's, again, look at this formula here. And I'll zoom in on it and then press F2 so we can see it a bit more clearly. I'll also scroll to the right so we have a better opportunity to see it clearly. Again, that can be a little bit intimidating. I would say give it a chance, work with it a little bit. But another option here could be, and I'll highlight all this and do it a different way, we could certainly have typed in j2 times m1. I want that to be an absolute reference, so I'll press F4 and then plus J2.
This is one of a number of different ways we could write the formula. And as I press Enter, you'll see that it's copied into all the other cells. So, you can continue to work with standard formulas, but when you're building formulas like this, you can't click on the cells to have the data come in with these kinds of cell references. So that could be an adjustment you want to use there. Now if we wanted to add other data to the right, let's say I'm not quite ready to do that, but I'm going to get it set up anyway. We might want to put in a special code number out here. I'll just call it Code for now. But look what happens when I type Code.
It's an adjacent column to the right, it immediately becomes part of the table as well. And there's another aspect to this that you might want to consider. On the Design tab, remember, that's present only when the active cell is within the table, you have a choice called total row. And immediately we go to the bottom here. Now, for some of these fields, we might want to have a total. For example, compensation. We might not want to add those, although we certainly could. But we have the option of choosing an average or count or some others. We might want to know the average salary. So, let's put that in right there.
For job rating, maybe an average would make sense there as well, too. User service. Same idea, although you might want to know the total user service. So, we could possibly do that, too. We put a sum in there. Now, something else you might have noticed here. And when we went to the bottom, it's real obvious. Where are the column letters? When you work with a table, as soon as you scroll downward, the column letters disappear and the field names take their place. Let me press Ctrl + Home or back up top here, we do see the column letters.
I'm going to use the mouse. We all know how to scroll downward. You see how the column letters disappear. And, when you think about it, it is more important to see the field names. So, this automatically takes the place of freezing Windows. And that's another thing to get used to. And as I zoom back with Ctrl and the mouse wheel. We can see that appearance. Recognize also, that filter errors are automatically in place. Many times, as you're working with data like this, you would want to use a filter. If you find them obtrusive or in the way, we can simply unclick the Filter button.
Now, a question comes up. What if we want to add a new record at the bottom? As you're going to the bottom hit Ctrl+down arrow. What if we want to add a record? Well let's temporarily disable the total row. Uncheck the box for total row, add a new record here. I'll just again type in a name. I'm not interested in doing anything else for the moment. Let's bring back the total row. Then clicking inside the data, back to the Design tab. Here's total row. And it will be placed below the new record that we've added.
And for the moment, that information is misleading. So, we will put in a higher date, suppose it's February 3rd, of 2014. And that will adjust this date, and the average. So we can make that total row appear and disappear. And we'd want to do that every time we add new records. So working with data that's been converted in the table has some merit to it, and you might want to explore what are primarily visual features but also this total rows we just saw. And also this ability to handle formulas as we saw over in the right most column.
If, at some point, you say, you know, I just don't like this. I, I can't work with it. With the active cell within the data, you can, on the Design tab, convert this back to arrange. Convert this table into a normal range of cells. All of the data is preserved. If you do this, however, the visual features remain. And that's not much of a burden. You want to convert this to a normal range, we possibly will say yes. The Design tab disappears, this is no longer a table. If you don't mind the band of rows, just leave them, but if you added new rows now, they wouldn't change color or anything like that.
So it's a feature definitely worth exploring, it has some merit particularly if you want your data to be treated as a unit. It automatically gives us filtering buttons, and it emphasizes the idea that we want to treat our data as a single entity.
- Multiple key sorting
- Filtering single and multiple columns
- Creating a top-ten list with values or percentages
- Setting up subtotals
- Creating multiple-field criteria filters
- Creating unique lists from repeating field data
- Using the Remove Duplicates command
- Finding duplicate data with specialized arrays
- Counting the number of unique items in a list
- Using SUMIF and COUNTIF functions
- Working with the database functions such as DSUM and DMAX
- Converting lists to tables