Locked lesson.
About this lesson
Learn about creating Excel tables and their advantages.
Exercise files
Download this lesson’s related exercise files.
Excel Tables - Begin.xlsx28.3 KB Tables - Completed.xlsx
27.7 KB
Quick reference
Excel Tables
Creating Excel tables and their benefits.
When to use
Excel tables are the standard and best way to organize tabular data in Excel today.
Instructions
Benefits of creating Excel tables
- Instant access to alternate row shading
- Auto-expansion when new rows or columns are added
- Auto-complete of identical formulas down the entire column
- Access to structured table reference formula creation
- Automatic application of filters
- Automatic naming of the table
Preparing your data to turn it into an Excel table
- Make certain that your data is in tabular format
- It is essential that there be no blank rows or columns interrupting the data
- Provide a clear header row for your data
Creating Excel tables
- Select any cell in the data range
- Go to the Home tab, choose Format as table, and pick your favorite color scheme
- You may want to then go to the Table Tools > Design tab, and rename the table to something more descriptive than Table1
- 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.