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. Look closely at the formula in cell C4 in the example below:-īy making the first cell reference $B4, you keep the column from changing when copied across, but allow the row to change when copying down to accommodate the prices of the different items going down.If you want to use Excel like a power user, you will need to understand the cell addressing in an Excel workbook. Lets say that there are two types of discount for our widgets Discount Plan 1 and Discount Plan 2 and we want to know the pricing for the different widgets with the two plans. Ok let’s turn up the volume on this and really make it work for us. See how the B26 reference remains the same even after auto fill. Nice huh? In the example below, the cell reference B26 will always be used for the % sales commission even if you drag the formula using Auto fill. So you do not want the cell to change when you auto fill your formula to the rest of the sales team. We know that the commission is 5% of their sales- ALWAYS. Lets say you are calculating commissions for sales staff of the Widgets as in the above example. If you copy the formula across rows or down columns, the absolute cell reference remains the same unlike the example above. If the position of the cell that contains the formula changes, the absolute cell reference remains the same. That’s all pretty straightforward, and once you get used to absolute cell referencing that will be too, let’s jump in.Īn Absolute cell reference is indicated in your spreadsheet by a $ sign around the cell, for example =$A$1 means that when referring to this cell is is ALWAYS in that location. The cell reference of the above formula =SUM(B4:B7) when dragged across to column C adjusts itself to then sum the results of C4 to C7. For Example if the position of the cell that contains the formula changes, the reference is changed. These are basic cell references that adjust and change when copied or using the Auto fill function in Excel. Just what is the difference between an absolute and a relative cell reference in Excel?.Ī relative cell reference is the most widely used cell reference in formulas in Excel.