Join Dennis Taylor for an in-depth discussion in this video Creating and using tables, part of Excel 2016 Essential Training.
- If you work with lists of data, maybe a personnel, an HR kind of list, like we're seeing here, or an inventory list or a transaction list, something like that. As lists grow or shrink, you can facilitate the management of them with a feature called a Table. On the Home tab, you might see Format as Table in the Styles group. Now the description there doesn't exactly give you a rationale for using this feature. It says, Quickly convert a range of cells to a table with its own style. On the Insert tab you'll see the same feature, with a much better description: Create a table to organize and analyze related data.
Tables make it easy to sort, filter and format data within a sheet. Now the term I've used is Entity. We want to treat this data as an entity. Let's convert it to a table. There are two keystroke shortcuts we could use, either Ctrl +T, think of T for Table, Ctrl + L, think of L for List, or on the Insert or Home tab, I'm going to use Insert tab here. Now, before doing this though, you want to make sure that the data you want to deal with, as a unit, has no empty rows in it, empty columns.
One quick way to determine that is click within the data, press Ctrl + A, and then press Ctrl + . four or five times. This will move the active cell around the corners of the range. Ctrl + . upper left, Ctrl + . upper right, again, Ctrl + . Now, if you haven't seen the data in a while, is that really the last row? Scroll a little bit beyond it, looks like it is. Ctrl + . a few more times, we're back up top. You don't need to do that often when you're working with data but occasionally it's handy. So, this data is ready to be converted into a table.
So, click on any cell, Insert tab, Table. Excel makes an educated guess as to the extent of your data, usually gets it right. From A1 in the upper left down to I742 lower right. Looks good, click OK. The first obvious visual difference, banded rows. A certain look there. And notice in the ribbon, we've got a Design tab. A contextual tab related to features that we might be using as we work with the table. Click outside the table, over in Column K for example, you see how that contextual tab disappears.
Click back within the data, it reappears. What might catch your eye at first are Table Styles. We could click here and you just needed 61 different ways to display this, didn't you. Slide over with the mouse, take a look at this, look at that. Maybe I'll just pick this one for the time being. Might change my mind later. The characteristic look here is referred to as Banded Rows. Notice, in that contextual tab, there's another option called Banded Columns. If you'd like to use that, uncheck Banded Rows, check Banded Columns.
If you like that look, fine. And the Table Styles that you might be considering also, at least for the moment, are showing the Banded Column look. Everybody probably tries this at least once: Banded Rows, Banded Columns, not so great together. One or the other, I'll stick with the Banded Rows. You can give special emphasis to the first column if you wish. Usually, what this means is, it makes the data Bold and also, in some cases, gives it a different color background. You can do that with the last column if you wish, either or both or none.
If the Filter arrows here are something you don't use that often. If you find them annoying or if they're overlapping some of your titles and you prefer that not to be the case, there's a button up here for turning off the Filter. This suggests though, that your data's being treated as a unit. So, what happens if we add a new record at the bottom? I'm going to double-click the bottom edge of this cell, which is active, double-click, down to the bottom, and start to type a new record. Press Tab, and immediately, that row becomes part of the table. Any formulas that are here automatically get copied downwards so, there's a formula here that picks up years of service based on a date, but I haven't put in the date yet, but the formula's still really doing its job.
It's coming up with an answer here. I'll add another row and it'll be a little bit more obvious because this row is going to be a darker color than the previous one. And we see what happens there. The same thing will happen on the right side of the data as well. So, any time you add data at the bottom of a table or on the right side. I'm going to add a new column here and we're going to be calculating a new compensation amount, so I'll type New Comp. Abbreviate it so the column's not going to be as wide. Like that. Watch what happens when I press Enter.
That column becomes part of the table as well. I'm going to write a formula here, =h2+2000. Watch what happens when I press Enter. Automatically gets copied down the column and we see a 2000 dollar increase for everybody. Now, here's something that you might find a little confusing. Instead of using h2, what if I actually had clicked on the cell? I'm going to click cell H2. That looks quite a bit different. Now Compensation is the range name that is automatically set up in tables for the different columns.
I'll press Enter here and that gets copied all the way down the column. Any one of these says that. So, when you first encounter these, it might be a little unsettling, you got to get used to that idea. If somehow that's too unusual, you could do as I did earlier, simply use the cell addresses. We also could have written this formula, by the way, with an external amount. Suppose everybody's going to get a 3.1 percent increase. I could have written the formula this way. Instead of +2000, how about * and I'll use this cell out here.
And you may or may not have reached this level of Excel, but we need to make that an absolute address by pressing F4 or manually putting in dollar signs. And then + this so, the formulas look a little weird at first possibly. I'll press Enter now and this will be copied automatically down the column. Now, something we don't have time to go into but it's worth noting. If you work with charts and you've got charts where the source data is continually being added to with new information on the bottom, if you want your charts to grow automatically, without having to redesign them each time, the source data for the chart can be converted into a table and then any chart based on it will automatically grow as you add new records to the bottom.
So that's something worth considering for those of you who work with charts and want to see them presented in a dynamic way. A couple other features here, available on the Design tab as well. We can add a Total Row. This is going to be on the bottom. And right away you might be thinking, well what happens if we want to add a new record? So, I've got a Total here on the right, and that, by choosing the down arrow, could be an Average, it could be a Max or a Min. I think, Total Sum here makes sense. But we could also take that information and drag it leftward, possibly into these columns here.
Now, probably want a Total there but for Job Rating, I'd probably want an Average. So I'll click the drop-arrow, choose Average. Average Job Rating. And we could add a decimal there if we wanted to. For the moment, don't worry about that. So the question comes up, what happens if we want to add a new record? Turn off the Total Row. We'll put in a new name out here. Fill in the other data, maybe now or later. But then, go back to the Design tab and simply bring back the Total Row. And if we'd added some numbers here, they would be included in the new total, so these are automatically set up.
So, you might want to work with a Total Row. Makes it handy to get to that data there. Another feature here too, you can also use what are called Slicers. Now, slicers were introduced in Excel 2010 but only for pivot tables. Starting in Excel 2013 and thereafter, you can now use slicers on tables. Now this is not a pivot table but it's a table. Slicers allow you to filter data in a more dynamic way. Let's say we want to add slicers here to have control over the display of Departments, possibly Status as well too.
Now, you don't necessarily have to know much about filtering to do this. But on the Design tab here, we do have the ability to Insert a Slicer. I'm going to put a slicer in for Department and maybe for Status, and click OK. And because these take up a considerable amount of screen space, I'm going to move these around a little bit and make this a bit smaller so we can see it there, like that, and you have to experiment with that a little bit. We can also change its color and all that sort of thing. Department, we've got quite a few of these and we might want to display these in three Columns.
We've got about 20 of them out there. Many times, the problem with slicers that you encounter is that they take up more screen space than you might like. So, I'm going to leave these right here. Now, as we scroll leftward, to see the Departments, we might have to, so we can see things a little bit better, zoom back a little bit. I think you can still see that reasonably but here's the idea. And we still might have to make that slicer panel for Departments a bit wider there. There we go. So, let's start first with the idea, in our list there, we only want to see Full-time people.
Now again, if you're adept with filtering, you would know how to do that but with Slicer, all we got to do is check Full Time, and our list is only showing Full Time people. Lower-left corner says we've got 393 out of 744 records. If we would also like to include Half-Time people here, we use the Ctrl key as we click Half-Time, there we are. To not filter at all by Status, clear the filter. Now, for the moment, I'm going to expand our slicer list up here.
Suppose we just want to view people in Environmental, right here, and that full name there I can't even see, there it is, Environmental Compliance. Our other Environmental group is right here. We'll hold down Ctrl and click that. So now we're seeing 23 records. So, we've got some other features here as well too. Let's say we want to view the Professional Training Group right here. Do I have another Training Group in there? Yeah, another one down here, just called Training. Ctrl, so on. In other words, we are controlling, by way of a Slicer, the filtering.
In other words, we're just looking at the records that we want to by way of these slicers. And you can imagine how we could also use other fields here too. The problem at times, as we're seeing here, it gets a bit crowded. If we also wanted the ability here to just view people based on their Benefit Package combinations, we could do that too. Or which Building they work in. So, let's add one more, just for the sake of argument here, to show how that would work. So, clicking within the table, back on the Design tab there, let's Insert a Slicer and this time, we'll add a slicer for Building.
And there's Building. So, let's just look at people in the Main Building. There they are. Now, the other filters are in place too. Let's clear the Department filter and clear the Status filter as well. So, we've got 199 people in the so-called Main Building. That sort of thing. So you can begin to see some real advantages here. Remember, you cannot use these slicers in other kinds of lists. Well, you can in pivot tables, but you can't use them in any other kind of list that has not been converted to a table. So, there are lots of features regarding tables that you're going to find beneficial, so at least consider the option.
You might want to experiment with it on a smaller list. Get the feel of it and then decide how you're going to work with it, but I see more and more people using this feature. It was introduced in Excel 2007, again it's referred to as a Table.
- Working with the Excel interface
- Entering data
- Creating formulas and functions
- Formatting rows, columns, cells, and data
- Working with alignment and text wrap
- Adjusting rows and columns
- Finding and replacing data
- Printing and sharing worksheets
- Creating charts and PivotTables
- Inserting and deleting sheets
- Using power functions such as IF and VLOOKUP
- Password-protecting worksheets and workbooks
- Sorting data
- Analyzing data with Goal Seek and Solver
- Creating and running macros