Mixed cell reference occurs when we use both relative and absolute references to refer to a cell. Question for you: Can you now suggest why B1 changed to B5 and not B4 or B6? We have already explained the answer :-) Mixed Cell Reference You will see that the absolutely referenced cell $A$1 will remain as it is while the relative reference B1 will change to B5. Now let’s copy this formula and paste it in cell C11. Note that the first cell reference is fully absolute ($A$1) and the second cell reference (B1) is fully relative. This formula multiplies the value of cell A1 with the value of cell B1. Let’s use the same worksheet that we created for the previous example. Let’s understand it with the help of an example. $A$2 = both column and row references are absolute.A$2 = column reference is relative, row reference is absolute.$A2 = column reference is absolute, row reference is relative.A2 = both column and row references are relative.To make a cell reference absolute, just add $ sign before the column number or row number or both of them: For example, if you copy a formula containing absolute cell references and paste it elsewhere, the references will still point to exactly the same cells as they were pointing in formula’s original location. Absolute Cell ReferenceĪbsolute cell reference means that the reference will not change if it is copied and pasted somewhere else. The drawback of relative referencing is that it may throw unexpected results if you don’t know what you’re doing. The benefit of relative referencing is that your formula will automatically change if you need to make several copies of the same formula (for example, through auto fill).

Excel calculate a cell’s location with respect to the location of the formula containing cell. As a result the target cell will change from B1 to B2. That is to say that Excel will stillgo three cells up and one cell to the left. When you copy the formula and paste it in cell C5, even then Excel will follow the same steps for locating the second cell mentioned in the formula. How does it find these values? Well, it searches for B1 like a crossword puzzle… three cells up and one cell to left from the location of formula. While working on the formula in cell C4, Excel will need to find the value in cells A1 and B1. You will see that C5 will show sum result as 7 because moving the formula also automatically changed the cell reference from A1,B1 to A2,B2.Ĭell references like A1 and B1 are relative references and they see the target cell with respect to the location of formula. Select Cell C5 and press Ctrl+V to paste the copied formula. Now select cell C4 and press Ctrl+C to copy the formula Press enter and you will see that C4 will show (7+4 = 11) as sum. Now in cell C4 type the following formula: Open a new worksheet and enter the values in cells as follows:

Relative cell reference indicates that the reference will change if it is copied and pasted elsewhere in the worksheet. In this article we will examine the difference between absolute, relative and mixed cell references in Excel. There are three types of cell references in Excel: For example, cell C7 cell is located at the crossing of column C and row number 7.Ī cell address is also called cell reference because Excel uses this cell address to refer to a cell. Excel addresses each cell with (Column Letter)(Row Number) format. Therefore, every cell can be uniquely addressed using the column and row number. Each cell is created at the crossing of a row and a column. In Excel, a cell reference points to a cell on a worksheet and can be used in a formula so that Microsoft Office Excel can find the values that you want the formula to calculate.Ĭell reference is the format used for addressing a particular cell. If you want to use Excel like a power user, you will need to understand the cell addressing in an Excel workbook.

Relative cell reference excel 2010