A worksheet also known as the spreadsheet is composed of a box-like structure called a cell. Every cell has an address location called cell reference. A cell reference consists of a column letter and row number (e.g. A1, D63, F120), this is where the row and column intersect.
Cell reference is used in Excel commands like formulas, functions and charts. In this lesson you will understand the three types of cell references, Relative, Absolute and Mixed. Let’s take a look one by one.
Relative reference means that when you change the location the cell address also changes or the cell reference is relative to its location, by default Excel uses relative reference.
Take a look at the example below, cell C3 formula points to cell A3 and B3, cell references are relative.
Now if you drag down and copy the formula in cell C3 up to C7 the cell reference also change. C4 references cell A4 and B4, C5 references cell A5 and B5 and so on. This means that cell C3 up to C7 are relative to the two cells on the left.
Absolute reference means the cell reference will be fixed to it’s location address when you move or copy the cell to any location. This can be done by adding a $ symbol in front of the column letter and row number.
Take a look at the example below in cell E3, we put a $ symbol on a reference cell G3 (conversion rate) to make it absolute.
So when we drag the formula down to E7, the G3 cell reference will not move.
Mixed reference is the combination of relative and absolute, in the example below reference column B which is the price and reference row 3 the % off price are fixed. So when we drag the formula in Cell F4 to the right, the price reference will be fixed and the % Off price reference will be relative.
When we drag the formula down, the % Off price reference will be fixed and the price reference will be relative.
I do have a question. I am not sure if this is possible, but I have this data set, and I have some formulas on another worksheet (in the same file) that point to values in this data set. However, the data set is going to be constantly resorted, making the formulas that point to the values obsolete.
Is there any way to change the formulas so I can sort and resort the data (so that I can find and update the values that the formula is using) but the formula will still point to the correct data.
For example: I have a formula that compares the number on the Inventory Worksheet in cell G3 to a number on the same worksheet as the formula. I would like to make it so that when the Inventory worksheet is sorted and the data I want that formula to use is now in Cell G10, the formula points to cell G10 without having to go back and manually change it.
Is there anyway to do that? I have a very old copy of excel (97 – but hey it still works for most of what I want it to). Any help would be appreciated.
Thanks