Cell Referencing7:37 with Michael Watson
You can calculate data by making references to specific cells — for example, you can sum all the values in cells 2 through 30 in a specific column. Spreadsheets let you reference cells by their row number and column letter using several different methods. Learn how in this video.
- you can continue to use the spreadsheet from the last video, or
- open a copy of this spreadsheet to catch up to this video
Types of References
Relative: identifies a cell in relation to its row or column. For example
A2means the cell in column A, row 2. Relative references update when you copy or move them.
- Absolute: identifies a cell in a specific row, column or both. Use absolute references when you don't want a row reference or column reference to update when you move of copy the reference. You can specify a specific cell by providing an absolute reference to a specific row and column. This is useful if you want to use a cell in multiple calculations throughout a sheet — for example, a cell that contains a sales tax rate that's used in multiple places to calculate sales tax for multiple items.
Examples of Absolute References
$A$2refers to the specific cell in column
2. Copying or moving this reference will always point to cell
A$2refers to the cells specifically in row
2. The row number,
2in this example, won't update when copying or moving this reference. However, the column reference might update.
$A2refers to the cells specifically in column
A. The column letter,
Ain this example, won't update when copying or moving this reference. However, the row reference might update.
One of the most powerful features of a spreadsheet is the ability to 0:00 use the value in one cell to calculate the value for another cell. 0:04 This is called cell referencing, where one cell references another cell. 0:09 For instance, I might hard code data in one place on the spreadsheet. 0:15 And then in another place use a function or 0:19 formula to reference that hard coded data. 0:21 You saw an example of that earlier, when I showed you how to calculate sums. 0:24 In that case, one cell referenced a bunch of other cells to calculate the sum total. 0:28 We did the same when calculating averages and median values. 0:34 That said, there are two different ways to reference a cell, relative and absolute. 0:38 Let's take a look at those now, 0:43 relative referencing is the most basic type of reference. 0:46 A relative reference lets you identify a cell in relation to its row or column. 0:49 This is really handy if you want to use the same formula for 0:54 multiple rows of data. 0:57 Also, if you add a row above a row with a formula, 0:59 relative references update to match the new row order. 1:02 Okay, that may sound a little confusing, but let me show you an example. 1:06 Here I have created a formula that calculates the volume of 1:10 different pieces of wood. 1:13 In columns A through C, rows 6 through 19, 1:15 we have hard coded data about pieces of wood. 1:19 In any event, 1:22 we want to calculate the volume of the wood in column D with a formula. 1:23 Volume is calculated as height times width times length. 1:27 In row 5, for example, the value in column D 1:30 should be the A, B, and C values multiplied together, 1:35 A6 times B6 times C6 equals 480. 1:40 In a spreadsheet, we refer to a cell by its column letter and row number. 1:45 So this is cell A6, this is cell B6, and this is cell C6. 1:51 The formula for cell D6 should be A6 times B6 times C6. 2:00 I can both enter text or select the cells like so, see? 2:07 I can type =A6*, then I can click on this cell and 2:11 B6 shows up and then I'm gonna enter in C6 myself and we get 480 inches. 2:16 Note that I can copy this formula from cell D6 to cell 2:24 D7 by clicking on the little square in the bottom right 2:29 of the cell called the fill handle, holding it down and 2:35 dragging it along the cells I wanna copy to. 2:41 Notice that the formulas in cell 2:46 D7 automatically update to reflect the new row. 2:48 So what I've entered here is A6 times B6 times C6. 2:52 When I copy the formula down, 2:56 automatically updates to read A7 times B7 times C7. 2:57 That's what makes this a relative reference. 3:03 The cell reference is relative to the row and 3:05 can change when the formula is applied to a different row. 3:08 So I copy this formula and paste it and 3:12 it stays relative to the row that it's referencing. 3:16 Absolute referencing means that your formula refers to a specific cell, row or 3:29 column and will never change as you copy it across different cells. 3:34 This can come in handy if you want to quickly test out different 3:38 values to see how they affect multiple formulas. 3:41 For example, say I wanted to see how changes to overall sales volume would 3:44 affect sales in each region. 3:48 I could have a single cell that let me plug in different values like 10%, 20% or 3:49 30%. 3:54 And see how those values affect regional sales calculations across multiple rows. 3:54 Let's walk through a simple example with our wood spreadsheet. 3:59 Say I wanted to know how the volumes change if every piece of wood had the same 4:03 height as the piece of wood in row 6. 4:07 That's easy to do using absolute reference cells, we can do that here in column E. 4:09 I enter the same formulas as I did in D6, but 4:18 this time I'm gonna put a dollar sign in front of the A. 4:20 So A6 times B6 times C6, =A6*B6*C6. 4:24 And then I'm gonna put a dollar 4:30 sign in front of the A and in front of the 6. 4:36 And this is how you create an absolute reference for 4:43 that particular cell, and only this cell. 4:46 The dollar sign A and dollar sign 6 mean I'm absolutely referencing A6. 4:52 Then, as I copy this formula down, you'll see that 4:59 the volumes in each row don't match the values in column D. 5:04 You can see that unlike D10, E10 still has A6 5:12 in the formula, because we put the dollar signs in. 5:17 In other words, in this cell, the first value is an absolute reference to cell A6. 5:23 And the other two are relative to cells B and C in row 10, 5:29 you can and often will use both types of cell references in a single formula. 5:33 With absolute references you don't always have to 5:39 reference absolutely the column and the row. 5:42 You can do either or in what is known as a mixed reference. 5:45 So you can absolute reference just the row, or just the column. 5:48 And I will walk through an example of this right now. 5:52 To illustrate this, I'm going to show you the volumes for 5:55 each piece of wood, calculated as a percent of total volume. 6:01 So first I'm gonna show what is the total volume. 6:05 Sum up all the different cells here and 6:11 get to 2,780 inches, that's the total volume. 6:13 Now, I can do this mixed reference by going this cell, 6:19 D6, divided by the total volume and absolute referencing it. 6:26 Then I can copy this down, and 6:36 I'm going to turn this into a percent by using the formatting toolbar. 6:40 So, if I select all of these then look down, 6:47 if this is correct, it should show 100%. 6:51 That is correct, but I've just used an absolute reference again, 6:55 and I wanted to show you how to use a mixed reference. 7:00 So what's another way I can do this? 7:04 I can do this by divided by the sum of all of these. 7:08 And I can do the absolute reference, but I don't necessarily need to here. 7:18 I'll do a mixed reference. 7:23
You need to sign up for Treehouse in order to download course files.Sign up