Excel Tables

Quick reference

• 00:04 In this video, I wanna show you one of the greatest ways you can store date in Excel
• 00:09 and that is in a table.
• 00:11 Now not a table that looks like this.
• 00:13 This isn't actually a table.
• 00:15 This is just a tabular range of data.
• 00:17 And if I scroll down here a little bit you can see a little bit more of it.
• 00:21 But the key things I want you to recognize here is it has a tabular structure.
• 00:25 Once again, it has a good descriptive header row.
• 00:28 No blank row between the actual data and the column headers, and
• 00:32 consistent data types all the way down the data.
• 00:35 And as we scroll down, you can see that it doesn't go very long.
• 00:38 But there's a good set of data here that's in a consistent format.
• 00:42 But it's not a table, it just looks tabular.
• 00:45 So let's turn this into a table.
• 00:47 Now you gotta bear with me, this is really difficult.
• 00:49 We're gonna click one cell anywhere inside,
• 00:51 and then we're gonna go to the Home tab.
• 00:53 To format this table,
• 00:55 I'm not gonna make the agonizing decision of what color we want our table to be.
• 00:59 This is the hardest part about making a table.
• 01:02 I'm gonna choose blue.
• 01:03 And at this point it picks up the entire range of data.
• 01:07 And it says my table has headers.
• 01:09 Because I have data that is actually numeric underneath a text based header,
• 01:14 it gets this box right.
• 01:16 And if it doesn't, you can just check it, no big deal.
• 01:19 At this point, I'm going to say okay, and
• 01:21 you'll notice it puts the nice table style right on top of my data for me.
• 01:26 And that's really cool, but
• 01:27 there's something that's really important that we have to do now.
• 01:31 I wish you could actually do this right in the table creation dialog, but
• 01:34 unfortunately we can't.
• 01:36 As soon as you actually create your table you will be on the table tools design tab.
• 01:41 You'll notice that if I click outside, that goes away.
• 01:44 If I click back inside, here it comes.
• 01:46 But right away when you create that table, it will be on this tab.
• 01:49 And this is really important because, way on the left hand side,
• 01:52 you can see where it's called table one.
• 01:54 And that's not a really good name.
• 01:56 I need you to change this name immediately as soon as you create a table.
• 01:59 Just get into this habit, right away, of naming it immediately.
• 02:03 I'm gonna call this one inventory, because it's the inventory from my pet store.
• 02:06 I'm gonna hit Enter.
• 02:08 Now why would I do this, well there is a few reasons, if I click outside the table,
• 02:12 I can actually select this data by going here,
• 02:15 because it actually get a name range, so that's pretty cool.
• 02:20 The other thing is, if you have multiple tables in your workbook,
• 02:23 this will rightly distinguish between them.
• 02:25 Now let's talk about some of the cool things that happen with tables.
• 02:28 Number one, you'll notice that we have our drop down filters for doing sorting and
• 02:32 for filtering.
• 02:33 Those come right away.
• 02:35 You'll also notice the alternate row banning that shows up,
• 02:38 and that's pretty cool.
• 02:39 But you know what?
• 02:40 If we don't like the style that we have,
• 02:42 we can always go back to table tools design and we could change it.
• 02:45 To any other style as well along the way.
• 02:48 The key thing to remember here when you click outside the table,
• 02:52 Table Tools Design Tab goes away.
• 02:53 It's what we call a contextual tab.
• 02:56 It will only show up when you're inside a real Excel table.
• 03:01 Any user can paint stuff to look like this, but
• 03:04 you can't get that table tools design tab to pop up unless it's a proper table.
• 03:09 Some of the other benefits of tables.
• 03:11 Let's say that we wanted to add a new column here called profit.
• 03:15 Notice the table expands automatically.
• 03:17 So that's pretty neat.
• 03:19 What if that profit was equal to, say, the price in E10?
• 03:23 Wait that doesn't say E10.
• 03:25 How about that?
• 03:26 It now says at price.
• 03:28 That means the at is this row of the price column.
• 03:33 I can now subtract the cost and subtract the commission,
• 03:39 that should return my profit.
• 03:43 Now, could I say, E10 minus D10 minus F10?
• 03:47 Yes, that would absolutely still work.
• 03:49 But here's the really cool thing about tables.
• 03:51 When I hit Enter, it copies the formula down every single row, immediately.
• 03:55 I can use whichever style of formula referencing I want.
• 03:58 This is called structured table referencing versus the A1 notation that we
• 04:03 usually use.
• 04:04 But the key thing that I want you to remember here is never mix them,
• 04:07 because tables don't update properly if you mix.
• 04:09 So if you put an A1 reference with a structured table reference that's
• 04:13 bad news.
• 04:13 Now the really cool thing here, let's go and scroll down for a second.
• 04:16 I want you to watch my column headers, A, B, C, D, E, F, G.
• 04:20 As I scroll down, the headers for the table snap in place.
• 04:23 And this is magic because if you've got a 90-column wide table and
• 04:27 you're trying to remember which column am I in?
• 04:31 There's no need to actually do anything special here.
• 04:33 The table headers snap right up, in top, which is pretty awesome.
• 04:37 So tables expand to the right.
• 04:39 They expand, also, down.
• 04:41 So if I come in and say, I've got a new record, here, that I want to add for
• 04:47 05 02, or 05 03, let's see what happens here.
• 04:49 Look at this. The table expands right away.
• 04:52 So I could put in a new Tubby Turtle, sold by John.
• 04:55 And I could go and actually put more pieces in here.
• 04:57 Now, I'm gonna get rid of this.
• 04:59 I don't really need that row.
• 05:00 And I wanna show you one more thing,
• 05:02 with some little, helpful pieces that have under our Design tab.
• 05:06 I can even add a Total row directly to my table.
• 05:10 And you'll notice that it gives me the ability to choose,
• 05:13 what would I like to sum?
• 05:15 Don't wanna sum the profit, it doesn't really make sense to go and sum the cost.
• 05:19 But maybe I wanna put an average cost on, or an average price.
• 05:23 I can do that just by clicking and dragging and dropping.
• 05:26 If I need to add new rows now,
• 05:28 I would need to insert them, I wouldn't be able to add them to the bottom.
• 05:31 But this is pretty cool because it allows us to go back and, actually,
• 05:35 very easily add formulas to our table and have it update for
• 05:39 us automatically with all of the great features that tables have.

Lesson notes are only available for subscribers.