Tables: How to Convert Data into a Table

Locked lesson.

Upgrade

About this lesson

We learn about prepping data for a table, converting the data to a table, and the benefits of a table.

Exercise files

Download this lesson’s exercise file.

123 Tables.xlsx
20.6 KB

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

Gift this course