Formula reference in Excel
Formulas are the significant and most used concept in Microsoft Excel worksheets. Though working with formula, the most important concept is cell reference. As easy as it seems, Formula reference confuses many Excel users. This tutorial will cover the definition of formula cell and range reference, types of cell references in Excel, steps to implement cell references, and many more!
What is a Formula reference?
Excel Formulas that you create mostly contain references to either cells or ranges. These references are important because they allow your formulas to operate dynamically with the data included in those cells or ranges. Let's understand the difference between a cell reference and a range reference.
A cell reference (also commonly known as cell address) is defined as an intersection of a column (typed in a letter) and a row (entered in a number) that specifies a particular cell in your Excel worksheet.
Example: The cell reference B2 refers to the cell at the intersection of column B and row 2; B3 refers as a combination of cell in column B and row 3, and so on.
A range reference is defined as the cell address of the upper-left cell and the lower right cell divided with a colon symbol.
Example: The cell range B3:B6 includes 4 cells from B1 through B6.
How to create a reference in Excel
To create a cell reference in your Excel worksheet, follow the below given steps:
You will have the following output.
To create a range reference in your Excel worksheet, follow the below given steps:
Types of Formula reference
While using a cell (or range) reference in a formula, you can use three types of references - relative, absolute, and mixed references. When you are writing a formula for a single Excel cell, you can choose any reference type. But sometimes, we don't type the formula again, and instead, we copy and paste it to other cells. We must apply a suitable address type to incorporate this because relative and absolute cell references act differently when copied to other cells.
Relative Cell References
A relative reference is defined as the cell or range reference mentioned without the $ symbol in the row and column coordinates, such as B1 or B2:B12. By default, all cell references existing in Excel your Excel worksheet are relative.
When you copy the formula carrying data with relative cell references to different cells, the row, and column references also change because the references are offset from the current row and column. By default, Excel creates relative cell references in formulas.
When repositioned or copied across different Excel cells, relative references change based on the relative position of the current rows and columns. Sometimes, we want to repeat the same formula across different rows or columns, and we simply drag the formula to different cells. In those cases, you need to use relative cell references.
Let's suppose you have the given below formula in cell C3:
Next, when we copy the formula in cell C3 to another row in the same column. In the below image, we have copied it to cell C4, because we have used relation reference in cell C3, while copying the formula it will automatically adjust for row 4 (B4*10).
If you copy the same formula further down the Excel in the same column will keep changing the column reference accordingly and give you the output
If you copy the formula containing the relative cell reference to another row and column, you will notice that Excel will change both the row and column coordinates.
Relative cell references are the most commonly used cell references while working with Excel formulas. It is because it is very convenient and helps to easily perform the same calculations across the entire worksheet. To understand it better, let's discuss a real-life example that will be solver using relative reference.
Example using relative reference in Excel
Question: In the below column you are given USD prices for different products. You want to convert the prices to INR so you can pursue the further calculation and do the analysis. It is given that the USD - INR conversion rate is 74.45 at the moment. Using the Excel relative cell reference, convert the USD prices to INR.
To convert USD prices to EUR using Excel relative cell reference, follow the below given steps:
That's it! You will notice the relative cell reference formula is replicated to other cells where are cell values are automatically adjusted for each individual cell. To verify that a formula in each cell is calculated accurately, select any of the cells and look at the formula in the formula bar. For instance, here we have selected cell D7, and view that the cell reference in the formula is relative to row 7, exactly as it should be:
Absolute Cell References
"An absolute reference is defined as the cell or range reference that remains unaffected while copying the same formula to other cells. Absolute addresses are extremely helpful when you want to achieve multiple calculations with a value in a particular Excel cell or when you require to copy a formula to different cells without modifying references."
The advantage of using an absolute reference is that the row and column coordinates do not alter when you copy the formula because the reference is an actual cell address. To indicate an absolute cell reference, always use two dollar symbols before column coordinate and row coordinate.
For instance, absolute references $B$6 implies both the column and row coordinates are fixed.
For example, if you want to add the additional marks (in cell C1) with the total marks obtained (given in column B). We will start the formula with equal sign (=),select the cell reference of marks field, and add the additional marks to it, where we will pass an absolute reference for cell C1. We will have the following formula
Copy the formula down column C by dragging the small thin black cross. The relative cell reference changes for all the cells, whereas the Absolute reference is constant across all the cells and will always be locked on the same cell ($C$1).
To understand it better, let's discuss a real-life example that can be easily solved using the concept of absolute cell reference.
Example using relative reference in Excel
Question: Calculate the remaining dates to achieve the sales target in a single formula using the concept of Relative and absolute cell references in Excel worksheet.
Calculating dates in Excel is one of the common use of absolute and relative cell references. Follow the below steps to calculate today's date in a single formula in Excel:
1. In the above example we have a list of Target sales dates in column C, and the current date in entered in cell C1. Add a helper column in Column D where we will enter the formula to convert find the remaining days to achieve the sales target.
2. You can calculate the number of days left by each account manager by using the following formula.
In the above formula, we have used a combination of relative cell reference and absolute cell reference. Each formula reference fulfills the given task:
3. Select the cell where you have typed the formula. Take your mouse cursor towards the end of the rectangular box. You will notice the cursor will change into a small thin black cross. Drag it over the cells you want to auto-fill.
Mixed Cell References
"A mixed cell reference is defined as a cell or range reference in Excel wherein either the column or the row coordinate is fixed."
NOTE: To incorporate mixed reference press the F4 button.
Excel users often get confused between Excel absolute reference, and Excel missed reference and mistakenly uses both of them interchangeably. But both are different.
Excel absolute reference contains the dollar signs ($) with both the row and column. Therefore it freezes both the column and the row coordinate, whereas in the case of a mixed cell reference, at a time only a single coordinate is fixed (absolute) and the other (relative) will change based on a relative position of the row or column:
In other words, the Mixed cell reference is a combination of Absolute column and relative row. For instance, $A1 and A$1 are mixed references. But what is the difference between these two mixed references, since both contains cell A1?
Formula Example using a mixed reference in Excel
Example 1: Using the currency conversion table, convert the dollar currency to different currencies with the help of only one single formula!
Since USD is the standard currency worldwide, we have taken the prices of services in USD. When dealing with international business, frequently you are asked to convert the prices. Let's enter the conversion rates in some row, say row 2, as shown in the screenshot below. Then follow the below steps to convert the USD currency to another:
The above formula was easy, but most of you might have been confused and thought about how exactly Excel knows which price value to take and which exchange rate to multiply it by. Yes, you guessed it right. It is all because of the mixed cell references. Let' understand the implementation of the above-used formula (=$C5*C$2):
Switch between Formula cell references (absolute, relative, and mixed)
Microsoft Excel provides different ways of doing the same thing. While working with the formula, you can manually type the $ and implement various cell references or convert a relative reference to absolute or absolute to mixed. But the shortest and easiest way to incorporate the $ sign is by using the F4 key. Though it will speed things up for you, but there is a condition for the F4 shortcut to work, i.e., it is mandatory to have it in formula edit mode: