Tables to Make Life Easier

Quick reference

• 00:04 If you find yourself working with large datasets often,
• 00:08 then you might consider using an Excel table.
• 00:12 That might seem like quite a basic tool, but there's more to it than meets the eye.
• 00:16 Excel effectively turns your data into an interactive database, allowing you
• 00:21 to sort, filter, and summarize data, so you can make actionable decisions.
• 00:26 In this video, I'm going to show you how to make the most of the tables function
• 00:30 using some Google Analytics data.
• 00:33 Now, an Excel table sits on the Home tab on the Styles Grouping,
• 00:37 and all you have to do is click Format as Table.
• 00:41 And some might think that the Format as Table button is just to make your data
• 00:45 look pretty, but it's not.
• 00:46 It comes with a lot of added functionality and so many advantages,
• 00:50 which I'd like to show you here.
• 00:52 Now, before we click on the button, though,
• 00:55 it's important to confirm that your data is good data first.
• 00:59 Now, what does good data mean exactly?
• 01:01 Well, first thing is that, there should only be one row of headings.
• 01:06 Your headings cannot be over two rows, only one, and
• 01:10 then there shouldn't be any breaks in your data either.
• 01:13 Note in the columns, there shouldn't be a blank column in between,
• 01:17 and there should also not be any blank rows.
• 01:20 One way to confirm where my data ends, is to hit Cntrl + the down arrow,
• 01:25 and the cursor will stop at the first break.
• 01:29 Now looking at the data, my first break is at the end of my data, so
• 01:33 I know that there are no blank rows.
• 01:36 Now I've got my data for August, up above and down below, I've got September's data,
• 01:40 but I'll get to the September data later on.
• 01:43 My August range looks good and there are no breaks.
• 01:47 But lastly,
• 01:48 I want to confirm that I've got consistent data types across every column.
• 01:52 And if you look at all the records, the numbers, the percentages, the text,
• 01:57 the dates, I want to see consistent types of data up and down those columns.
• 02:01 And at a glance, it looks like I do.
• 02:04 So I'm confident that my data is good, and if the data is good, well,
• 02:08 how do we make this a table?
• 02:10 Well, you can click anywhere within the data range,
• 02:13 and you go up to the Home tab, the Styles Grouping, Format as Table.
• 02:18 Or the shortcut is Cntrl + T on Windows and on Mac.
• 02:24 You should double check if your range is good, but
• 02:26 if you don't have any blank rows, then your range should be fine.
• 02:30 And this table has headers, so I'm going to check off,
• 02:33 My Table has Headers, hit OK.
• 02:35 And this is what a default table looks like in Excel.
• 02:39 So you'll know that this was successful if you click inside the table and out, and
• 02:44 the table design Contextual tab up top appears and disappears.
• 02:51 Also, if you go back to the data, and then you hit Ctrl + down arrow,
• 02:55 you'll see there's a little blue shape in the bottom right-hand corner.
• 03:00 This is called a sizing handle, and
• 03:02 that indicates the edge of your data that's included in the table.
• 03:07 If you don't like the default format on the Contextual tab,
• 03:11 you can go up and change it back to this format.
• 03:15 This just changes the appearance of the cells,
• 03:17 the range is still formatted as a table.
• 03:19 Before we look at what advantages the table holds,
• 03:23 let's look at a good practice first, let's rename the table.
• 03:27 On the table design tab, I have Google analytics data, so
• 03:32 we're going to type that in, GA _data, no spaces.
• 03:37 Now, here's the first advantage, look at my headings.
• 03:41 Often when you scroll down to the bottom of your data, the headings disappear, so
• 03:44 that it's no longer visible.
• 03:46 But look at the table headings now.
• 03:48 As I scroll one more row down, the table headings jumps onto the column headings
• 03:54 and it's available the whole way down until you click outside the table.
• 04:00 Headings are gone, now they're back.
• 04:04 Now, if I go to the top of the data again, it has auto filter buttons,
• 04:08 which you can also use to sort.
• 04:10 The big advantage is that, Excel sees this as one unit of data.
• 04:14 And there's no risk of you sorting only the one column and
• 04:17 corrupting your data that way.
• 04:19 Another nice advantage, inside the table,
• 04:22 on the table design tab, you have a total row that you can tick.
• 04:27 So let's click Ctrl + Down again, to get to the bottom of the data.
• 04:33 And if I want to add up total revenue, for example,
• 04:37 then I'll go up and I'll click on Total Row to get a total.
• 04:42 But look at what happens it actually adds up the dates.
• 04:45 And actually I don't want that, so let's just change that to nothing.
• 04:49 If we go over to the revenue column, we can get exactly what we want.
• 04:53 Let's go right down the Sum, it will add the total revenue.
• 04:57 Now we can do it for the other columns too,
• 04:59 if we like we can get total transactions.
• 05:02 We can get the average conversion rate, and you can pick them as you go along.
• 05:07 Now, the next benefit is a little weird, so stick with me.
• 05:10 If you're used to normal Excel formulas it gets a little intimidating, but
• 05:13 let's try this out.
• 05:14 Let's say I want to add a formula to my table.
• 05:17 In my data I've got a column for total users, and one for
• 05:21 new users, but I know if I subtract the new users from total users,
• 05:26 I'll get the number of existing users.
• 05:30 So let's say I want to add a column for that, right, so let's insert a column,
• 05:35 add a header, and it immediately recognizes existing users as a header, so
• 05:39 that's nice.
• 05:41 But now look at this, if I click in the middle of the column, here we are at E16.
• 05:47 I add a formula, I'm going to add it with normal Excel formulas for now,
• 05:50 and to do that, you've gotta go up to the formula bar.
• 05:54 So we type =, and we know we want C16- D16, right?
• 05:59 So we've got that in the formula, but watch what happens when I click Enter.
• 06:03 When I do, the formula gets copied to all the cells above and below in the range.
• 06:10 Now that's pretty cool, but let's use E16 as an example again.
• 06:15 If I type in the cell equal sign, and I click on the reference,
• 06:19 look at what happens.
• 06:20 Instead of saying C16- D16, now, it says @Users- @New users.
• 06:28 Now what is that?
• 06:29 Now, this is what's known as structured referencing.
• 06:33 The benefit of that, is that you don't have to
• 06:36 worry about adding dollar signs to lock cells when you copy formulas.
• 06:41 Because that @ symbol refers to the current row in the data.
• 06:45 So if you've got @Users- @New users, Excel will look for those two columns by name,
• 06:51 and reference the cells in the current row to complete the formula.
• 06:55 And if I press Enter, my new formula, once again,
• 06:59 it copies all the way up and all the way down the column.
• 07:02 All right, now, tables have what you call a dynamic range that can extend your
• 07:08 table range, and it copies the formulas that you've added to the new range.
• 07:14 Now, let's go back down to the bottom of the sheet to demonstrate this.
• 07:18 For now, we need to untick the Total Row option, so let's do that.
• 07:23 And now I'm going to select my September data,
• 07:25 that's all this data down at the bottom.
• 07:27 I'm going to go to the bottom.
• 07:29 To do that it's Cntrl+down, and then I'm going to go over to the right,
• 07:34 Shift+Cntrl+right, and then select all my data going up, shift+Cntrl+up.
• 07:40 And I'm going to deselect that one row because I don't want the headers,
• 07:45 as the headers are just the same for both sets of data.
• 07:49 Now I'm going to grab my data, I'm going to drag it, and
• 07:53 let it go just below my other table.
• 07:56 The warning says, there's already data here, and do you want to replace it?
• 08:00 It's because the headers remained, I only grabbed the data below the headers, and
• 08:05 it wants to know if I am okay with getting rid of the headers.
• 08:08 I am totally happy with that.
• 08:09 And there it is, my new formula copied all the way down,
• 08:13 my September data has been added.
• 08:16 And how do I know, because the formatting is consistent.
• 08:20 And I could check my sizing handle, the bottom right hand corner,
• 08:24 it now includes the September data.
• 08:26 And if I want to get my totals back on the table design tab,
• 08:30 go to Style Options, check off Total Rows.
• 08:33 And all the formulas that I had set up before, are now back,
• 08:38 and that is how you use tables.
• 08:41 Tables give your data an organized home to live in,
• 08:44 allowing you to easily sort, filter, and add up totals.
• 08:48 And the best part is that, you can use the same formatting for repeated tasks,
• 08:52 such as monthly marketing reports or lead prospecting.

Lesson notes are only available for subscribers.