Fair warning, this topic is a little dry. But understanding how cell references work will help you create good formulas and useful spreadsheets.
Let’s start with some sample data. In this sample, we want to calculate extended price for each order (in the purple region), a grand total (in the green region) and a column that flags all orders less than the minimum sales order amount (stored in the pink region). We will use the column and row using the cell references (found in yellow).
First, let’s add the extended price. We won’t be going into details about how to create the formula (yet!), so type this into cell E4. If you know other ways to add a formula, feel free to use them, too. We want to multiply C4 times D4.
When you hit [Enter], you should have $396.00. Notice how the cell shows the formula results, while the formula bar still contains the formula. Also notice that the cell references are there.
If we copy and paste that cell to E5, the results are still correct and the formula automatically changed for us. Instead of using the fourth row, not it references the fifth row. These are relative references and will change as you copy the formula to other cells.
In our example, this is just what we want. Copy the formula to all of the E column cells with sales orders. Your spreadsheet should look like this:
Now add the grand total to the bottom of the extended prices. Notice that again, the cell references are relative. If we insert a row, the formula would change from =SUM(E4:E9) to =SUM(E4:E10).
We have one task left and that is to add a column that will display a word if the order is below the minimum order amount, as defined in cell F2. Our first formula, in cell F4, can look like this. Don’t be distracted by what the formula is, just be aware that I have still used relative references. Our first formula worked great! But now copy it to F5. What happened? It still looks like the results are good, but the formula is now broken. Copy it to the remaining cells in row F. Now it is easy to see that the formula is broken.
The reason it is broken is because each time we copied the formula, the relative reference changed so we were no longer comparing the extended price to the value in F2. We needed to use an absolute reference. No matter where we copy and paste this, we do not want the F2 reference to change. But we do want the E column reference to be the current row. Using “$”, we can force references to be absolute. Change the formula in F4 to this. Notice I added a dollar sign in front of both the column and the row reference because I don’t want either to change. Copy the new formula to the remaining cells in column F and now see how the results are correct.
Bonus tip for those who haven’t fallen asleep…
The dollar sign still applies, absolute versus relative, but you can also use cells from other worksheets and even workbooks in your formulas. Here is the syntax, which will be populated for you if you use the expression builder. The example below is referencing E1, on a worksheet called “WorksheetName”, in a workbook called “OtherWorkbookName.xls”.