4. Addressing Cells

Now that we’re done with one cell, we have to do everything again for C and D, right?

No way! Computers are all about making our lives easier and, lo and behold, there is an easier way of doing this. It’s called fill. Fill basically copies the data (it can be a function or just number) from one cell and fills up all the other cells highlighted.

Before we play with fill, just to demonstrate something, in our if statement let’s change E2 to $E$2 and E8 to $E$8 in cell B2. It looks the same right? E2 and E8 are highlighted. We’ll get back to this mystery in a minute.

Now let’s get back to fill. To fill, click on the cell you want to copy and then hover at the bottom right corner. The mouse arrow will turn into a plus sign.

Now, just drag it across to the right to the empty cells.

What happens? They’re all 1s! Why is that? To get an idea – click on each of the cells C2, D2, etc.

[Q 13.4.1]: What formula is in the cell C2? D2?

Hmm… It’s always looking at the E2 cell – which has the information on questions from Clicker1 session. We want it to automatically change so that C2 is calculated based on the data in F2 and D2 (Clicker2 session) is calculated based on the data in G2 (Clicker3 session).

Isn’t there a way for us to make the spreadsheet to automatically update to a new cell? Aren’t computers supposed to be smart?

Actually, not so much smart as hard working. Remember the mystery when we changed the values E2 and E8 to $E$2 and $E$8?

In spreadsheets (like Google’s and Excel), there are two ways one can address a cell: Relative and Absolute. When you have a dollar sign in front of the Column or Row, then you are utilizing absolute addressing, and the column or row is locked when you need to copy a formula. The formula will always reference the same cell column and/or row (depending on which has a dollar sign in front).

Examples:

$B$2 – absolute addressed for both row and column. Neither row nor column changes when (used as part of a formula) the reference is copied to another location in the sheet.

$B2 – absolute addressed ONLY for row. Row doesn’t change when (used as part of a formula) the reference is copied to another location in the sheet. Column (2) would change. (we’ll see this as we go on).

B$2 -- absolute addressed ONLY for column. Column doesn’t change when (used as part of a formula) the reference is copied to another location in the sheet. Row (B) would change. (we’ll see this as we go on).

Absolute Addressing is really important when you have a comparison value, like a Total Max in our case, where that’s a set number that should be compared to for all data. Anytime we would want to utilize those values, keep the row absolute.

Relative Addressing will not lock the formulas when copying. Depending on which way you copy (right to left, left to right, up to down, down to up) the cell references will change. So if you copy left to right, then the cell references in the formula or function will increment left to right. If this seems confusing, it’s much easier to understand when you see it in action – so let’s do that.

First we need to undo our mistake – delete the content in cells C3 and D3 (highlight C3 and D3 and press the delete on the keyboard). Next, change the reference in B2 from $E$3 and $E$8 to E2 and E$8. Now our reference to E$8 is absolute with regards to the row – because the total number of max points is always located in row 8 for each clicker question. We leave the reference to the column E as relative – since we want to be able to copy it left or right and have it update to use the right column.

Then, let’s Fill from left to right. (hover in bottom right corner, to get the “cross” – drag right.

Wonder if our fixes worked? Click on C2 or D2 and look! It updated with the relative cells! All references to E became C in column C and D in column D.

[Q 13.4.2]: What would happen if you fill vertically (e.g. own the B column) from B2? What would happen if B2 had $E$2 and $E$8 instead of E2 and E$8 and you fill vertically? How about if it was E$2 instead of E2 and $E8 instead of E2 and E$8?

<< Back Next >>