Locked lesson.
About this lesson
Learn about creating Excel tables, and their advantages.
Lesson versions
Multiple versions of this lesson are available, choose the appropriate version for you:
Exercise files
Download the ‘before’ and ‘after’ Excel workbooks from the video tutorial and try the lesson yourself.
Excel Tables.xlsx12.2 KB Excel Tables - Completed.xlsx
13.9 KB Excel Tables - Extra Practice.xlsx
19.4 KB
Quick reference
Topic
Creating Excel tables, and their benefits.
When to use
Excel tables are the standard and best way to organize tabular data in Excel today.
Instructions
Benefits of creating Excel tables
- Instant access to alternate row shading
- Auto-expansion when new rows or columns are added
- Auto-complete of identical formulas down the entire column
- Access to structured table reference formula creation
- Automatic application of filters
- Automatic naming of the table
Preparing your data to turn it into an Excel table
- Make certain that your data is in tabular format
- It is essential that there be no blank rows or columns interrupting the data
- Provide a clear header row for your data
Creating Excel tables
- Select any cell in the data range
- Go to the Home tab, choose Format as table, and pick your favorite color scheme
- You may want to then go to the Table Tools > Design tab, and rename the table to something more descriptive than Table1
- 00:04 Excel tables were added in Excel 2007 and they are
- 00:07 in a word: awesome.
- 00:09 They are now the standard way that you would want to organize any of your data if you
- 00:15 want to push it into Excel's power tools like Pivot Tables or Charts and things like that.
- 00:20 The nice thing about tables is that they carry a lot of benefits and they're super super easy to create.
- 00:27 In fact, they're this easy to create. If we click somewhere in our list. remember it needs to be
- 00:31 a contiguous range of data that's the only key that we have to start off with here.
- 00:35 In order to actually create it the most difficult thing that we have to do is go to the Home Tab, click Format as Table
- 00:41 and choose our colour style. That's the hard part.
- 00:46 So for this case right now I'm going to go with this one here it's got a nice dark blue header with some light blue bands. When I click OK
- 00:55 it will come back and says hey I've discovered that this is your contiguous range of data.
- 01:00 Based on the fact that we've got some values over here and they have a text header I think that you have header rows.
- 01:06 Pretty nice. So we're going to say OK we're going to go with that.
- 01:12 And here we are. It immediately turns it into a table for me.
- 01:17 Now notice that it's banded my rows for me,
- 01:21 it's added filters so I can go and actually sort and filter by the different items that are on this list, never have to do anything else with them.
- 01:30 If I scroll down I can see where the table ends. And this little icon
- 01:34 down at the bottom corner, this little black or little blue checkmark down here
- 01:38 will allow me to drag and expand my table should I need to do that
- 01:43 to different areas to make it bigger. Which is kind of nice as well. We're not going to do that right now
- 01:48 because I want to show you some of the other cool features here as well.
- 01:52 Let's say that I wanted to go and add, I wanted to for some reason double the discharges here
- 01:56 so I'm going to add a new column and I'll call this one
- 02:01 double discharge.
- 02:04 If I hit Enter
- 02:06 you can see that my table automatically expands for it. When I go over and say equals discharges times two you can see that it's also
- 02:15 used a different way of referring to the formula. Rather than referring to cell B4 which would still work,
- 02:21 I can actually refer to @ and the column header name and it will now, when I hit Enter
- 02:28 put it all the way down the table for me. Every one of these formulas is identical so just an
- 02:32 immediate snap right into this which is great.
- 02:36 The table also has if I go to the DesignTab a name.
- 02:40 This is just like a Named Range. Though I could actually call this one something better if I like.
- 02:44 Maybe we'll call this one tblDRG. You can just rename it.
- 02:48 And that will actually be something that I can use in formulas as well.
- 02:54 We can also go and do some other things to it as well, we can go and we can add things like a total row. So let's go right here and
- 03:02 click Total Row and you can see down the bottom that I get totals. I even have the choice when I click in the cells of
- 03:09 what to total by. So I could sum it or I could go and count my records
- 03:14 and do all kinds of different things. I can do that for each cell that's actually in this table. So maybe I want to
- 03:20 count the numbers in this one and maybe over here I'm looking for the maximum
- 03:25 and in this particular column I'm looking for the average.
- 03:29 And in this column I'm actually not interested in. I can turn those totals off. So lot of different functionality that we have there.
- 03:36 We can band our columns
- 03:40 and that doesn't look too attractive in this particular case.
- 03:43 We can band our first column.
- 03:45 Or Bold it rather.Or we could do it for the last column. So lots of different options there but the real great thing about this is the
- 03:53 auto expansion and the structure table referencing. The structure table referencing is this
- 03:56 new formula style. This allows us to do lots of different options but
- 04:00 really that is Excel Tables in a nutshell. Easy to create, the most difficult thing to figure out is what color you want them to be.
Lesson notes are only available for subscribers.