Excel Tables

Quick reference

• 00:04 We're now gonna look at Excel tables.
• 00:07 And Excel tables are bar none the best way to store data in Excel today for
• 00:12 a variety of reasons, some of which you'll see here.
• 00:16 Now, before we can get started with this there are some
• 00:19 really important things you need to know about your data.
• 00:22 Number one and again you've heard this before but
• 00:24 I'm going to hammer this home again.
• 00:26 It needs to be set up in a tabular format for a table.
• 00:30 It also is imperative that there are no blank rows or columns inside your data.
• 00:35 Because if there are it's gonna impact the ability to create that table so
• 00:39 you don't want that.
• 00:40 Especially no blank row between the header row and the data.
• 00:43 That's bad news.
• 00:44 If you want that Try and make one of the rows taller,
• 00:48 in order to make it look like there's blanks there.
• 00:50 But we don't want to have a blank header row, or
• 00:52 a blank row between the headers and the data itself.
• 00:55 It's also important to have consistent data types down your columns.
• 00:58 So you'll notice here, this is a column of dates.
• 01:00 We got column of text, column of text.
• 01:02 Column's of values here.
• 01:03 So, consistency in those columns is important.
• 01:07 So now we're ready to turn this into a table and
• 01:11 let me show you just how hard this is.
• 01:14 What we're gonna do is we're gonna select any cell at all.
• 01:17 We're gonna go on the Home Tab to Format as Table.
• 01:21 Now, we're gonna make the agonizing choice over what
• 01:24 color we want this table to actually be.
• 01:27 So for me, I'm gonna pick blue.
• 01:31 And you'll notice that because we have no blank rows or columns,
• 01:34 it picks up the entire data range, and also it recognizes my table has headers.
• 01:39 This is because I have a row of text across the top and
• 01:42 down some of these columns I have numbers so Excel is able to
• 01:46 recognize that the data type is different and there's only one row with text.
• 01:50 Therefore it must be the header row.
• 01:52 If it doesn't get it right, you can click this box here.
• 01:55 If your data doesn't have headers, the table will automatically create them for
• 02:00 you using exciting column header names like Column 1 and Column 2 and Column 3.
• 02:05 That's why it's better to provide good column headers to begin with.
• 02:08 So we'll say OK.
• 02:09 And your table's done, that's it.
• 02:11 That's all it takes to create a table.
• 02:13 See, it's really difficult.
• 02:15 One of the things I do wanna get you in the habit of right away though.
• 02:18 As soon as you create your table, the table tools design tab will pop up.
• 02:23 And on the very left-hand side of it, there is a table name of Table1.
• 02:28 I'd like you to change this right away.
• 02:30 We're gonna call this table Inventory because this is a pet store inventory.
• 02:34 We'll hit Enter.
• 02:35 Why do I do that?
• 02:36 Several reasons, but the most important is, when you click on the drop down box,
• 02:40 you'll notice that here is my inventory table and I can select it right away.
• 02:45 You'll notice there's also one called Solution that comes from the practice set
• 02:48 that's in the file.
• 02:50 Now this is cool because no matter where I am,
• 02:53 let's say I jumped over the the practice table and I went and
• 02:57 clicked on Inventory, it's now gonna take me back to my inventory table.
• 03:01 You'll notice that there's some other Incredible benefits that happen with
• 03:04 the table right away.
• 03:05 Number one, we've got alternate row banding.
• 03:07 This was incredibly difficult to do inside previous versions of Excel that
• 03:12 didn't have tables, so this is a real nice piece.
• 03:15 It's got that built in table name with it, which is also good.
• 03:20 Just as a quick point of interest,
• 03:21 a way to recognize a table Is by the presence of the Table Tools Design tab.
• 03:26 Color is not enough cuz somebody could color rows to make it look like this.
• 03:29 But when I click outside, you see how that tab went away?
• 03:32 I'm gonna click back inside, it shows up.
• 03:35 If your table is not an official Excel table, this tab will not show.
• 03:39 If it does or it is then it will.
• 03:42 Should also show you that you are not stuck with the color you chose.
• 03:45 You can come to the styles tab inside or the design tab inside table tools and
• 03:51 you get live preview as you mouse over all of the different options
• 03:54 to see what's actually, which table style would be best for you.
• 03:58 There's also a few different configuration options you could change here as well.
• 04:01 But the more important things I wanna show
• 04:04 you are some of the other benefits of tables.
• 04:06 Let's look at this an add a column here for, profit.
• 04:09 And as soon as I click Enter,
• 04:11 you'll notice that the table automatically expands.
• 04:14 I also when I say profit is equal to price minus cost and
• 04:21 minus commission, notice that I have a new style of referencing here.
• 04:26 It's not using D-8 and E-8, although those would work.
• 04:31 The thing here is looking at at price, so what's its looking at,
• 04:34 the at sign says this row in the price column.
• 04:38 And at is this row in the cost column.
• 04:41 When I hit Enter on this,
• 04:43 it actually runs that formula consistently down every row in the table.
• 04:46 I don't even have to copy it.
• 04:48 So as soon as I actually go and
• 04:49 insert anything immediately adjacent to the table It's gonna automatically expand.
• 04:55 This new table referencing style is called structured table referencing.
• 04:58 Don't mix it with the classic A1 style referencing.
• 05:02 But you can use A1 if you like instead.
• 05:05 Some other things about tables is that when you scroll down the page notice
• 05:09 that my column headers snap in place.
• 05:11 So if I'm in a massive table way down in a bottom right hand corner
• 05:15 I don't lose context.
• 05:16 Which column was M?
• 05:17 Was M in cost?
• 05:18 No, column C is cost, so you don't have to worry about that.
• 05:23 In addition, as we go and
• 05:24 take a look here, if I were to add a new record immediately below, not only
• 05:29 does my table automatically expand, but the formulas have expanded with them.
• 05:34 So a huge amount of benefits to tables.
• 05:37 It also comes with default filters across the top.
• 05:40 They're already built in as well.
• 05:42 So some great things that you wanna be able to use.
• 05:45 This is by far the best way to store data today and I highly recommend
• 05:49 if your data's in a tabular format, turn it in to an official Excel table.

Lesson notes are only available for subscribers.