Understanding cell reference is one of the important things to master excel and make the most out of spreadsheets. Cell References combined with auto fill feature of excel help you to reduce/ eliminate the dirty manual work that needs to be done. To begin with cell references, we need to understand what a cell is and the notation/ language used in excel to identify a cell. Read more about the terminology used in Excel.
What Is a Cell Reference
A cell reference refers to a cell/ range of cells that refers/ tells excel where to look for the data. In short cell reference means the cell to which another cell refers to or points to. Cell references can be in a particular cell of a work sheet, range in a worksheet, a cell/ range in a different worksheet or even to a cell/ range in a different workbook.
To create a cell reference,
- Select the cell and place an = or + sign and select the cell you want to refer to.
- Select the cell and place an = or + sign and type the cell name which you want to refer. (Ex: A2, b2 etc.)
- Using =C2 or +C2 in a cell will reflect the value present in cell C2 in the current cell.
- Similarly =C2+D2 returns the sum of values in cells in C2 and D2 in the current cell. The same can be used with other operators like – , * and / as well.
See the below image to understand how to refer to different cells.
Excel provides two types of cell references. Relative and Absolute.
Relative Reference: A reference which changes upon copying to a different cell
using the autofill/ Manual copy is known as relative reference.
For Example: if you have a formula in Cell D1 as =B1+C1, upon copying it to row 2 i.e Cell D2 it changes to =B2+C2
By default all references in excel are Relative. Relative References come in handy when doing calculations or operations that are dynamic in nature especially when you need to repeat same calculation across multiple rows and columns.
See the below image to understand relative reference. The below example shows the total price a customer needs to pay by multiplying the number of items with unit price of respective item.
Absolute Reference: There might be situations in which the reference should not change upon copying to a different cell
using the autofill/ Manual copy. In these cases absolute reference come in handy. Absolute reference is used to keep a row/ column constant.
To make a Cell reference Absolute you need to use the $ sign. It can be either for a column/ row or both. See below example to understand Absolute references.
- $A$2 – Keeps both Column and Row constant
- $A2 – Keeps the Column Constant. (Also termed as Mixed Reference)
A$2 – Keeps the row constant. (Also termed as Mixed Reference)
Shortcut: Use F4 key, To Switch between Absolute and Relative References
See the below image to understand Absolute reference. Continuation to the example above, we need to calculate the total price a customer needs to pay by multiplying the number of items with unit price of respective item along with the discount and sales tax (which are constant % ages ) to be applied for all items.
Mixed Reference: If either or the row or column are made constant. See case 2 and 3 in the above example.
Circular Reference: When a formula refers to the same cell. These are generally iterative in nature which excel doesn’t support (while using formulae). A warning message pops up when circular references are used in a formula. Excels also tells you the same on the status bar as well.
Challenge: Using a Single Formula Create a Multiplication table (1 to 10) using Relative and Absolute References as shown in the Sheet 3 (Challenge) of Practice Workbook
Do share this post with your friends if you find it useful.