Excel Tables

Quick reference

• 00:04 In this video, we're gonna look at Excel tables and
• 00:07 I know you've heard me talk about tables in Excel a lot, but
• 00:10 in this particular case, we're actually gonna use the Excel table feature.
• 00:14 What we've been looking at until now is actually tabular data.
• 00:17 In order to create a proper table inside Excel and
• 00:20 you're gonna want to do this once you see how this works.
• 00:23 There is some key characteristics that we need and
• 00:25 it's very much like sorting and filtering.
• 00:28 We need to have a nice tabular looking set of data.
• 00:32 No blank rows.
• 00:33 No blank columns.
• 00:35 Again, a blank cell or two is no big deal but
• 00:37 we gotta have a good header row with no blank row between our actual data as well.
• 00:43 Okay, so no blank rows, no blank columns.
• 00:45 Good header row.
• 00:46 Really important.
• 00:47 Notice we've got some nice names here.
• 00:49 It's very helpful to have consistent data types down your columns as well so
• 00:53 you'll notice that my costs are all values here.
• 00:55 There's no text mixed in with these things.
• 00:57 My dates are all dates and these are good things that are really important to have.
• 01:02 Once you've got that, it's really easy to create a table.
• 01:04 Basically what you do
• 01:05 is you click somewhere inside the contiguous data range.
• 01:08 Excel is gonna try to expand this as far up, left, right, and
• 01:11 down as possible to pick up all the cells.
• 01:14 We go to the home tab.
• 01:16 We go over to this Format as Table button, and
• 01:18 we have to make an agonizing decision over what color we want our table to be.
• 01:22 That's the hardest part about making a table.
• 01:24 So, I'm gonna go with blue to start with here.
• 01:28 You'll notice that it's picked up the entire range.
• 01:30 That's great and it's identified that I have headers.
• 01:32 It may not always if everything's text.
• 01:34 So, you can change that by clicking the button here but we can say OK and
• 01:39 right off the bat you see the immediate benefit of working with tables.
• 01:42 You got this alternate row banding that's showing up on here.
• 01:46 The other thing is you can actually go and change the style of your table very,
• 01:50 very quickly.
• 01:51 If you wanted to say flip it to orange, you could change the color or yellow or
• 01:56 whatever you like.
• 01:56 So, I'm gonna go back to blue but
• 01:58 there's a bunch of different styles that we can actually look at that have
• 02:02 different pre-formated pieces built in to make it show in different ways.
• 02:07 So, this row banding was really hard to get without a table so
• 02:10 that is one incredible benefit from it.
• 02:13 The other thing that I recommend you do right away when you set up your table is
• 02:16 you'll notice that when you click outside your table and you click back in,
• 02:21 you're gonna end up with this table tab that pops up.
• 02:23 Notice it goes away and it comes back in.
• 02:26 This is a contextual tab that only appears when you're inside a table.
• 02:30 One of the things on the very left-hand side here is you have the ability to give
• 02:33 your table a proper name and I really encourage you to do this right away
• 02:37 because when you do that, that name shows up in the name box.
• 02:41 So, tables come with a name.
• 02:44 You'll notice this workbook already has a table and it's called Solution and
• 02:47 if I click there I can jump right into it.
• 02:50 Likewise I can go back to my sales table right away and
• 02:53 it selects all the data in that table.
• 02:55 So, that's one great benefit or another great benefit first you've got bro banding
• 02:59 you've also got a built in named range.
• 03:02 You've also got your built in filter arrows across the top those come right
• 03:05 away with tables so you already know how filters work its just another benefit that
• 03:08 There you can sort on your tables very easily as well.
• 03:12 Something else is very cool though, is auto-expansion.
• 03:17 Now watch this.
• 03:17 I'm gonna write something in a cell immediately beside my table and
• 03:21 when I hit Enter the table expands to pull it in.
• 03:24 So, this is cool because we have name,
• 03:28 we could target this table with a VLOOKUP statement, and now the table,
• 03:32 every time we add new data to it, both going horizontally and
• 03:36 vertically, will expand if we insert our data in the next column or next row.
• 03:41 In addition to this, there's one more benefit here.
• 03:44 Watch this now.
• 03:44 I'm gonna say equals price and that's exactly what I'm going to say.
• 03:49 It's referring to the column header minus the cost, minus the commission.
• 03:57 So, I'm starting to build a formula that seems to have names but
• 04:00 they're all related to the headers of the table.
• 04:03 When I hit enter now it commits the exact same formula down every cell on
• 04:08 the table right away, which is great cause I have now got consistent formulas and
• 04:13 the nice piece is this formula is the same on every single row
• 04:17 because basically what's happening here, this is using a new style of formula
• 04:21 referencing called structured table referencing that says at price.
• 04:26 Well the at means this row in the price column.
• 04:31 So, at means this row.
• 04:33 So look at this row of price, minus this row of cost, minus this row of commission.
• 04:38 We get that same formula all the way down and
• 04:44 again, if we go all the way down to the bottom of the table here as well and
• 04:48 we'll just scroll down and take a look over here.
• 04:51 Come back up a little bit to where my data is.
• 04:54 If I were to go and add a new piece of data to the bottom of this,
• 04:59 we'll say 05-22, as soon as I hit Tab right now, or
• 05:03 hit Enter, it automatically expands that table again, okay?
• 05:06 So, this is again, really, really important stuff,
• 05:10 because if we've built any kind of solution off of this particular table now,
• 05:16 as long as we've used the tables name in a formula,
• 05:19 it's already gonna pick up this new data, some incredible benefits of tables here.
• 05:23 Lots of them.
• 05:24 I highly recommend you use them.

Lesson notes are only available for subscribers.