For VLOOKUP to work properly, though, it needs to be pointed at a 'phone book' of data. I covered the tremendously useful VLOOKUP function in a previous article. Now look what happens when I copy this new, improved formula down the column:Īccurate tax calculations! Notice how, even at the bottom of my column, the last cell is still pointing at the correct sales tax rate, the 6% entered in cell C1. I like it, I want to keep it, I don't want it to change." Just remember it this way: d ollar signs tell Sheets that your cell reference is money and it shouldn't change. You can do this by hand, or by putting your cursor somewhere in the C1 cell address and then hitting the F4 key on your keyboard. To make sure my formula stays glued to that Sales Tax Factor in C1, I can add some dollar signs to my formula before the C and the 1. You can use an Absolute Reference to send this message to Sheets (or Excel they handle cell referencing the same way).
![excel absolute vs relative reference excel absolute vs relative reference](https://i.ytimg.com/vi/iDg9s7BJ2m4/maxresdefault.jpg)
I need to tell Sheets: "No, I want you to multiply all of my sale prices by cell C1, every time." I'm not saying, "Bring me the contents of cell A2", I'm really saying, "Bring me the contents of the cell that's two cells to the left." In this example, Google Sheets makes the assumption that when I copy my formula down the column, I want it to change each formula relative to the cell's position. I actually want the cell reference to change as I copy it down the column, like this:įortunately, this is what spreadsheets actually do, thanks to a feature called Relative Referencing. When copying a formula, cell addresses change relative to the cell they're written in. What I really want is the value of the cell directly across from my formula. If I did really put the formula "=A2" into all of my cells, then I would just get a column cells that all return the same thing In other words, Sheets/Excel knows that if I copy and paste my formula (=A2) into the cells below (cells C3-C7), I probably don't actually want all of these cells to point to cell A2. Most folks also know that when you copy and paste a formula that contains cell addresses, Sheets or Excel gives you the relative reference of your target cell. This isn't news to anyone except an absolute spreadsheet beginner. By extension, the formula =A2 will return whatever's sitting in your target cell: So if your target cell is in column A and Row 2, its address is A2. A cell's address consists of its column (a letter), followed by its row (a number). Most folks who use spreadsheets are used to this convention.
![excel absolute vs relative reference excel absolute vs relative reference](https://media.cheggcdn.com/study/b9d/b9d4cf08-73c7-4284-83e1-6e1f41aca624/image.png)
Both Google Sheets and Microsoft Excel refer to cells by their addresses using a system called 'A1 notation'.