Excel Tables

Quick reference

• 00:04 In this video I want to introduce you to an incredibly important feature in Excel.
• 00:10 And honestly I wanted to introduce you to it earlier but there's a lot of things
• 00:14 that I needed to cover before we actually understand why this is so important.
• 00:18 So what we actually have here is we have a range of data
• 00:21 that is in a tabular format, okay?
• 00:23 It looks like a table.
• 00:25 The thing is it's not actually a table, but the data is in good form.
• 00:30 And what I mean by that is it has an identifiable header row, that's important.
• 00:35 It has consistent data down the columns.
• 00:38 So in this one, it's all dates, it's all text here,
• 00:41 it's all text, and it's all numeric going down.
• 00:44 There's no blank rows and there's no blank columns, okay?
• 00:47 Those are the hallmarks of a good tabular dataset.
• 00:51 What I want to do with this right now though is I want to turn this
• 00:56 into an official Excel table.
• 00:58 And if you can take one thing from this course, I hope you do this with your data,
• 01:02 is turn it into a table if it looks like a table.
• 01:04 And I'm going to show you all the reasons why.
• 01:07 This is super super easy to do, okay?
• 01:09 What you're going to do is you're going to click inside your data somewhere,
• 01:12 you're going to come up to this format as table and
• 01:15 this may actually be a gallery on your screen depending on what's going on.
• 01:18 You're going to click on that and you're going to see all these color styles.
• 01:22 And basically the hardest part of making a new table is deciding what
• 01:26 color you want it to be.
• 01:28 Are you feeling blue?
• 01:29 Are you feeling yellow and sunny?
• 01:31 I'm going to go with this one for right now,
• 01:33 we're going to gold table style medium.
• 01:35 And I'm going to click on that.
• 01:36 And you'll notice that it pops up and it says, does your table have headers?
• 01:39 And yes it does, that's great.
• 01:41 If you don't, it will put in headers for you, column one, column two, very boring.
• 01:47 So make sure you've got headers first.
• 01:49 And then we're going to go and say OK.
• 01:52 Now you're going to notice immediately that there's some big changes of things,
• 01:55 some things that have happened here.
• 01:57 But there's more things here than you can actually recognize right off the bat.
• 02:01 Before we go any further though, I need you to do something very, very important.
• 02:06 What you'll notice is up in the very top whenever you create your table,
• 02:10 it gives your table a name.
• 02:11 This one here is called Table1.
• 02:14 That is a terrible name.
• 02:16 We're going to give this a nice name that is representative of the data.
• 02:20 So what I'm going to call this is I'm going to call it Inventory, and
• 02:24 I'm going to hit Enter, all right?
• 02:26 I really wish honestly, and I've asked the Excel team for this for
• 02:29 a long time is that when you go to create a new table.
• 02:32 Whether you click it from the gallery or press Ctrl+T, that's the shortcut for
• 02:37 making a new table.
• 02:38 I wish it would actually prompt you when it's setting up all the data range for
• 02:42 the name.
• 02:42 It's very important, why?
• 02:45 Well, that is a defined name, just like a named range.
• 02:49 Check this out.
• 02:50 If I click inside the cell here, and I go to my drop down,
• 02:53 you can see the name Inventory, it's a defined name.
• 02:56 And when I click on it, it selects all the data.
• 02:59 Now if you've got ten tables in your workbook and they're all called Table1,
• 03:02 Table2, how do you know which one is inventory and which one is sales?
• 03:05 Name them upfront, very important.
• 03:07 You'll also notice visually that you get this alternate row banding going on in
• 03:11 here, and this is awesome.
• 03:13 It's not required, you can actually turn this off.
• 03:15 You can change your color styles and if you don't like the banding at all,
• 03:20 you can go up to the Table Design contextual tab that only shows up
• 03:24 when you're in an official table.
• 03:26 And you can actually start playing around with some options,
• 03:29 turn off the banded rows.
• 03:30 You can turn off the header row if you don't want to see it.
• 03:32 Now, I don't really recommend doing that, you can turn on banded columns.
• 03:36 And all of these things reflect in the Table Styles gallery to see how they're
• 03:40 going to look.
• 03:41 Now personally, I kind of liked the banded rows option.
• 03:45 You'll also notice that you get some drop down filter buttons built into the table.
• 03:49 They just come with the table.
• 03:50 How cool is that?
• 03:52 In addition, we can also go and set up a total row.
• 03:57 Let me just turn that on for a second.
• 03:58 And notice that we've got a total here and it's totaling my commissions.
• 04:02 Well, that's kind of cool, but watch this.
• 04:04 If I click under cost, I get a drop down and I can say,
• 04:09 give me the sum of my cost and give me the average of my price.
• 04:15 And what happens here is it actually runs a subtotal function using 101 for
• 04:20 averages versus a subtotal function using 109 for sum, that's pretty cool.
• 04:26 And yet, there's more.
• 04:28 If we go over here and we say, let's go and type in profit,
• 04:32 I now can go and say, well, what is my profit?
• 04:36 My profit is my price.
• 04:39 And this is interesting because in normal formulas what you would get here
• 04:42 is you get E10.
• 04:43 We have a new style of formula referencing inside a table which is called
• 04:48 structured table referencing.
• 04:50 The @ means this row of the price column.
• 04:55 I'm going to subtract this row of the cost column and
• 04:59 I'm also going to subtract this row of the commission column.
• 05:04 So we get to use named columns instead of actually picking up the individual
• 05:08 cell references.
• 05:09 Now, could I say, E10- D10- F10?
• 05:14 Yes, absolutely.
• 05:15 That would absolutely work.
• 05:16 But why would you when you can get this?
• 05:19 And check this out.
• 05:21 This is awesome.
• 05:22 When you press Enter, the same formula gets written down every row of the table,
• 05:26 boom, just like that.
• 05:28 And they are for reference, totally dynamic,
• 05:31 just like you'd expect a formula should be.
• 05:34 The only thing I don't have here is my sum.
• 05:36 So I'm just going to go and add that really quickly to the bottom of the table.
• 05:40 If I don't like the color, I can always come back up here later and
• 05:43 say, you know what, maybe this is the one that I want after all,
• 05:46 and I can change it to match that.
• 05:48 Tables are phenomenal.
• 05:50 If you have data that looks tabular, turn it into a table, okay?
• 05:54 You're going to find it's going to make your VLOOKUPs easier to write
• 05:57 because the table boundary has a name that you can refer to,
• 06:00 you can refer to individual columns.
• 06:01 It just makes everything better.
• 06:03 So I highly, highly recommend that you get used to using these.
• 06:07 If your data looks tabular, format it as a table.
• 06:10 Believe me, you will not regret it.

Lesson notes are only available for subscribers.