In excel formula the reference can be given to different cell. The reference is just like giving address of the cell. The formula will take the value of the cell mentioned in the reference. The reference can be relative reference or absolute reference. In this article i will explain how to use relative and absolute references in excel.
Cell Address in Excel
There are two different formats to refer the address of the cell in excel.
- Columns are identified with letter and rows are identified with number. Eg. A1, in ths A indicates first column and 1 indicates first row.
- Both column and rows are identified with number with prefix of R for for and C for column. Eg. R1C1. this indicates Row 1 and column 1.
By default excel allows A1 format, this can be changed to R1C1 in excel options. To change this go to Excel Options>Formulas> select R1C1 reference style.
Note: At a time we can use only one format of cell reference in excel. In this article only the A1 format reference are explained .
The cell reference in excel can be given in a formula by typing the address or by clicking the target cell.
The reference can be of following 3 types:
- Reference in same worksheet: The reference in same sheet will contain only the cell address like A1 or B5. By default it will be a relative reference without $ sign.
- Reference in different sheet in same file: In excel you can link or give reference to other sheets in the same file. To do this we can use mouse pointer to select the different workbook and select the cell. We can also use Ctrl+PgUp or Ctrl+Pgdn to move between sheets. The reference will be worksheet name followed by exclamationpoint (!) and cell address. In case if we are giving reference to cell A1 in Sheet1 the the cell reference will look like Sheet1!A1 . If the worksheet name contains name, the worksheet name should be covered with single quotation marks (‘ ‘) . For reference A1 in worksheet name Sales Report , the reference will look like ‘Sales Report’!A1 . By default it will also be a relative reference without $ sign.
- Reference in different worksheet in different file: We can also give reference to different sheet in different file. While entering the formula, we can give reference to different file by selecting the file which is open and clicking the required cell in sheet. The file name in reference will have the file path followed by square bracket [filename] . eg: ‘C:\Users\Excelhub\Downloads[highlight selection colour vba code.xlsm]Sheet1′!$A$1 .
By default it will be a absolute reference with $ sign.
How to use relative and absolute references in excel
Relative and absolute references in excel can be categorized in following 3 different ways for better understanding.
- Relative Reference
- Absolute reference
- Mixed reference
By default the references given in the excel formula are relative references. When we copy and paste the formula, the references also changes based on the relative position of rows and columns. In below example you can see that B6 referred in the formula is relative reference and it moves to B7, B8, B9 when the formula is pasted to C7, C8 , C9. The relative reference will not contain $ sign.
If we copy this formula to right side, that is to column D, the reference will change to C6, C7, C8 , C9.
Absolute references are the references which will not change even if we copy or drag the formula to other cells. This can be used when we want to keep the reference constant even if the formula is copied to other cells. The absolute references are defined by adding $ sign before both column and row reference like $A$1.
In this below example instead of hard coding 10 in every cells, we can give reference to B3 with absolute reference like $B$3. This reference will remain constant even if we copy this formula to C7, C8 , C9.
In a mixed reference only the column or the row is fixed. In case if we fix the column, row reference will move when we copy the formal. Similarly if we fix row, column reference will move when we copy the formula.
In absolute reference, there are 2 $ signs. one is before row reference and second one is before row reference like $A$1. In this if we keep only one $ sign it will be mixed reference.
Absolute column and relative row reference: In case if we keep $ sign only before column reference like $A1 it will lock only column reference and the row reference will move.
Relative column and absolute row reference: In case if we keep $ sign before row index like A$1, it will lock only row reference and column reference will move.
For easy understanding, how to use this mixed reference is explained with the example.
In this example, we have to calculate the Profit margin for sale with different scenarios like, 10% 15% and 20% for each person. In case if we do not use the mixed reference, we will have to enter formula for each cells instead of coping the formula. But with mixed reference we can put formula in one cell and copy for all rows and columns.
In this formula, $C6 only column is fixed and row is relative reference and in E$5 only the row is fixed and column is relative reference.
With this formula if we copy the formula to other columns and rows as below, it will calculate the profit margin for all row and all scenario.
Using F4 key to change the reference type
We can use function key F4 to change the reference type instead of manual entry of $ sign. F4 key can be used while entering the formula or while editing the formula. By default the reference will be relative reference in formula, the shortcut key F4 will change the reference as below:
- First time F4 will change the relative reference to Absolute reference like A1 to $A$1.
- Second time F4 will change the absolute reference to Relative column and absolute row reference like $A$1 to $A1.
- Third time F4 will change the Relative column and absolute row reference to Absolute column and relative row reference like $A1 to A$1.
- Fourth time F4 will change Absolute column and relative row reference to relative reference like A$1 to A1.
Understanding the relative and absolute reference in excel will help us in creating effective and efficient formulas.