Structured References in Tables

Quick reference

• 00:04 There are a lot of benefits of using tables in Excel, and
• 00:08 how a table treats formulas is a big one.
• 00:11 In this lesson let's look at how formulas work in an Excel table environment.
• 00:16 Here we have an Excel table, we have an area set up for additional formulas, and
• 00:21 we have a set of extra data that we're going to add to the table in a bit.
• 00:25 But first, we already know that as we add data to the bottom,
• 00:29 our table will expand to include that data, because it has a dynamic array.
• 00:35 But you can also add columns to the right.
• 00:37 I want to insert two columns, because I want to calculate tax and
• 00:41 then add it to sales to show the Total Sales Including Tax.
• 00:46 So watch what happens if I add the Tax heading.
• 00:50 The table handle at the bottom shifts one column to the right,
• 00:53 to include the new column in the table.
• 00:56 And then when we add Total Sales including Tax, again,
• 00:59 the table handle includes that column.
• 01:02 So now let's say I want to calculate tax on each transaction.
• 01:07 For the purposes of this example, we're going to assume a fixed tax rate of 7%.
• 01:13 I'm going to type it in the formula bar, and I'll show you the reason for
• 01:16 that in a second.
• 01:18 My normal formula would be G5 times 0.07.
• 01:23 Well, look at what happens when I hit enter in this table.
• 01:28 The table copies the formula all the way down to fill the entire range.
• 01:33 That's really cool.
• 01:35 Now let's say I go through and halfway through the range,
• 01:39 I decide to make changes, and I want to change that tax rate to 8%.
• 01:43 If I type it in and hit enter, Excel updates the formula all the way up and
• 01:47 down the column.
• 01:50 Now the other way we can enter a formula in Excel is to type it right inside
• 01:54 the cell instead of in the formula bar.
• 01:57 Now watch what happens.
• 01:58 Tax is 7% multiplied by the value in G5.
• 02:02 But because this is in a table, when I click on G5, Excel uses naming.
• 02:09 It changes the name of G5 to @Total Sales excluding Tax.
• 02:16 Now what is that?
• 02:17 This is called a structured reference.
• 02:20 If we tell Excel, hey, make the value of this cell G5 times 0.07,
• 02:25 that's called explicit referencing.
• 02:28 But within tables, it changes the name of G5, and
• 02:31 uses the square brackets in the @ symbol.
• 02:34 So that's called structured referencing.
• 02:37 Let me translate what it says,
• 02:41 =0.07 multiplied by the @Total Sales excluding Tax.
• 02:47 Well, that's referencing the column name, which is column G.
• 02:52 It's indicated in square brackets.
• 02:55 And the @ symbol refers to a specific row reference in that column.
• 03:00 And if I press enter, it updates all my formulas to the structured referencing.
• 03:07 Let me show you another one.
• 03:09 Total Sales including Tax is just the Total Sales excluding Tax, plus the tax.
• 03:16 Look at the references.
• 03:18 @ your Total Sales excluding Tax is the specific row and
• 03:22 column G, plus the specific row and column H for
• 03:26 the tax, and we hit enter, and it fills the range.
• 03:31 Now structured referencing is not limited to inside the table itself.
• 03:35 I can use it outside the table.
• 03:37 But if I refer to values inside the table,
• 03:39 you get those structured references as well.
• 03:42 So watch this, if I want to calculate the Total Sales excluding Tax by location,
• 03:48 the first location is Oregon.
• 03:50 Well, I'm going to say sumif.
• 03:52 What range has to include Oregon, that's my location.
• 03:57 Now watch how I select the range.
• 04:00 Hover the mouse pointer over the heading until it
• 04:03 becomes this black arrow pointing down.
• 04:05 Left click, and that reference will look at the entire column name
• 04:10 location inside the table of sales.
• 04:14 And K5 is an explicit reference.
• 04:18 Then what do I need to sum?
• 04:21 I'm going to sum Total Sales excluding Tax.
• 04:24 Hover the mouse pointer until it becomes the black arrow,
• 04:29 left click once, and then it says your Sales table,
• 04:32 the column Total Sales excluding Tax, close the parentheses and hit Enter.
• 04:39 And I've got over $11,000.
• 04:41 And I can copy that down with the fill handle as per normal, but I don't have to
• 04:45 worry about locking any values with dollar signs because it uses name to ranges.
• 04:52 So now let's update this table with the new data that we have down below, and
• 04:56 you can see what happens.
• 04:58 And remember, I have a total at the bottom.
• 05:01 If I click inside my table, I can go back up and untick the total row for now, so
• 05:05 that I can add my new data at the bottom.
• 05:09 So let's select the data.
• 05:11 And you can either drag the data in or you can cut and paste it.
• 05:15 I'm going to cut and
• 05:15 paste it because I want you to keep an eye on those totals in the upper right.
• 05:20 So I've selected my data, Ctrl+X, cuts, and
• 05:24 I'm going to paste it in the top left hand corner here.
• 05:30 I'll keep an eye on the state totals as I do this.
• 05:33 We hit paste and it updates.
• 05:36 And not only did some ifs update to include my entire new range,
• 05:41 Excel also copied the formulas then added in those new columns, and
• 05:45 it extended it all the way down to include the new data.
• 05:50 So you can see, unfortunately, the table doesn't fix the alignment everywhere
• 05:55 with the new data, but otherwise, everything looks great.
• 05:59 It's nice and neat.
• 06:00 And this is what makes tables so nice to use.

Lesson notes are only available for subscribers.