Locked lesson.
About this lesson
We learn about prepping data for a table, converting the data to a table, and the benefits of a table.
Quick reference
Tables in Excel
Learn about prepping data for a table, converting the data to a table and the benefits of a table.
When to use
Tables are especially useful when working with large datasets since tables add automated functionality to a range.
Instructions
- Data must comply with the basic requirements of good data:
- One row headings,
- No blank rows or columns
- Consistent data types in a column
- To convert to a table, select a cell inside the data range, Home tab, Styles grouping, Format as table.
- Check the data range
- If conversion successful, the contextual tab for tables will appear on the menu bar if you click inside the table
- At the bottom right-hand side of the table, a sizing handle will indicate the edge of the data in the table
- Table benefits:
- Headers remain visible
- Totals added to the bottom of the table by the click of an option
- Auto filter and sort is automatically added
- If you add a formula to a column, the formula is copied to all the column, the same goes for updates
- Formulas use named ranges – structured referencing – which makes creating formulas a little easier and the need for $$ mostly obsolete
- Dynamic ranges – it is easy to update a table with new data
- 00:05 This lesson is a quick introduction to Excel tables.
- 00:08 Now you've worked with data in tabular format before,
- 00:12 that doesn't necessarily make it a table.
- 00:14 Excel has two different types of tables, we've got one that sits on
- 00:19 the Home tab under the styles grouping, and it says format as a table.
- 00:24 The purpose of this button isn't just to make your data look pretty,
- 00:28 it has certain functionality attached to it.
- 00:31 And it's not to be confused with the data table that sits on the data tab
- 00:35 under the what if analysis.
- 00:38 They are two very different things.
- 00:40 We're going to look at the one on the Home tab, format as table.
- 00:47 Before you can format a set of data as table,
- 00:50 you need to take into account three different things.
- 00:55 Number one, there should only be one row of headings, and no merged cells.
- 01:00 Number two, there should be no blank columns, and no blank rows.
- 01:06 And number three, you need a consistent set of data in a column.
- 01:11 You've got text in this column, you've got a date in this column all the way down,
- 01:15 and you have amounts in this column.
- 01:18 Those are consistent, and
- 01:20 if you're satisfied that all three conditions are true,
- 01:23 you can click anywhere in your table, you don't have to highlight the whole thing.
- 01:28 And then you go up to the Home tab, styles,
- 01:32 format as table and you can select a style.
- 01:36 I would like to use this blue one here, but before I even select that,
- 01:40 there's a shortcut to make this whole thing much quicker.
- 01:43 The shortcut is Ctrl T, that brings up this dialogue box.
- 01:47 The same dialogue box that you would have got if you clicked on format as table and
- 01:53 then selected your table format style.
- 01:56 Okay, the dialog box gives you a range that is selected for
- 02:00 you, indicates the range with the set of dotted lines all around.
- 02:05 It also locks the range with dollar signs.
- 02:08 And you can select my table has headers, and then hit OK.
- 02:12 And it's as simple as that, you have a pretty format.
- 02:17 If the whole conversion worked, you'll notice two things.
- 02:20 You'll have a table design tab at the top, contextual tab.
- 02:25 And if I click outside my table, the contextual tab will disappear.
- 02:29 And then if I click back in my table it reappears.
- 02:35 And then on the bottom right corner of my table, you'll see this little sizing
- 02:40 handle that indicates the end on the bottom right hand corner of my data.
- 02:46 So first things first, let's rename our table.
- 02:50 We'll go up to table name, and rename it to sales.
- 02:54 This is similar as it would be for naming a range that you can use in a formula.
- 02:59 If you click on the name box, you'll see your name as an option.
- 03:03 And when you select it, it highlights the data in your table.
- 03:08 So what's the advantage of a table?
- 03:11 The big advantage is that there's quite a bit of functionality that's automated,
- 03:15 that you would otherwise have to do by hand.
- 03:18 For example, let's say this is a very large data set and
- 03:21 you have to scroll down to the bottom of the data set to see everything.
- 03:25 The moment I scroll down, my headings will disappear, but not so with a table.
- 03:31 Watch what happens to the headings.
- 03:34 It jumps on to the column headers.
- 03:37 And it does so until you scroll back down, and then the headers are visible again.
- 03:42 So, no need to do any freeze panes with a table.
- 03:47 Then it's real easy to add a total to the bottom without searching for
- 03:52 the proper functionality.
- 03:54 On the table design contextual tab under table style options,
- 03:58 you can click on table row and it adds totals to the bottom.
- 04:03 If you don't like the total it shows you, click on the drop down, and change it.
- 04:09 If there isn't one, you can add one, if you want an average, there you go.
- 04:15 If you want to count the text fields above, do that.
- 04:19 So those are really nice.
- 04:22 Now let's look at auto filters.
- 04:24 If I look at my items and I choose headlamps and sleeping bags,
- 04:28 watch my total.
- 04:31 It automatically changes to only show the visible cells,
- 04:34 it's actually a subtotal formula and not a sum.
- 04:39 You also have access to some pretty cool shortcuts.
- 04:42 If you click in your table, Shift and space highlights a row.
- 04:50 And then Ctrl and space highlights an entire column.
- 04:56 But the nicest thing for me about a table is what it does with new data.
- 05:01 Let's say I have new sales data over here on the right that I would like to
- 05:05 add to my new table.
- 05:08 I can just drag and add this to the data table, and
- 05:11 it will apply all the formulas and all the formats and
- 05:14 everything that we've done to the new data and you won't have to redo any of it.
- 05:20 Now because I have a total row at the bottom, I would have to insert blank
- 05:24 rows to do this, but there's no need to go through all that trouble.
- 05:29 Just go up to the data table, contextual tab untick the total row just for
- 05:35 a moment, and now watch what happens with my data.
- 05:40 We're going to highlight all the data, and then we're going to click and
- 05:43 drag it to the bottom.
- 05:44 And as soon as I let go, Excel applies the formatting that you see.
- 05:50 Now the table handle, is in the lower corner, and
- 05:53 it shows that the new rows have been incorporated into that table.
- 05:57 And if I want my total rows back, go back up and click it off.
- 06:01 And now my totals are all back and it takes those new rows into account.
- 06:06 And if you need to type in a new row at the bottom, click on the last value and
- 06:10 then press tab and it will give you a new row where you can type in new data.
- 06:15 But when it comes times to use some formulas within this new table,
- 06:18 they're going to look slightly different.
- 06:21 So we'll cover that, in the next lesson.
Lesson notes are only available for subscribers.