Excel Tables

Quick reference

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