🥳 GOSKILLS TURNS 10: Get 10 days of free access with code 10YEARS

GoSkills
Help Sign up Share
Back to course

Excel Tables

Compact player layout Large player layout

Locked lesson.

Upgrade

  • Lesson resourcesResources
  • Quick referenceReference
  • Transcript
  • Notes

About this lesson

Learn about creating Excel tables, and their advantages.

Exercise files

Download this lesson’s related exercise files.

Excel Tables.xlsx
18.2 KB
Excel Tables - Completed.xlsx
19.3 KB

Quick reference

Topic

Excel tables.

Description

Creating Excel tables, and their benefits.

Where/when to use the technique

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
Login to download
  • 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.

Naming Ranges
05m:23s
Recording Macros
06m:11s
Share this lesson and earn rewards

Facebook Twitter LinkedIn WhatsApp Email

Gift this course
Give feedback

How is your GoSkills experience?

I need help

Your feedback has been sent

Thank you

Back to the top

© 2023 GoSkills Ltd. Skills for career advancement