Mixed Reference in Excel (Table of Contents)
Introduction to Mixed Reference in Excel
Mixed References in Excel is used to fix either column or row at one time. It refers to the only Column or Rows the referred cell. For example, if we want to apply mixed reference in a cell say A1 then we can fix the column of cell A1 by putting dollar(“$”) before the column name $A1 or to fix the row of cell A1 then we can put dollar before the cell number A$1. By this, any of the references can be fixed. In short, when we put $ before anything if locks it.
Examples of Mixed Reference in Excel
Here are the examples of Relative and Absolute Reference work with Mixed Reference in Excel given below.
Example #1 – Relative Reference
By default, the reference in excel will consider it as a Relative reference. Reference is nothing if we input “=A1” in the cell B1 then we are referring to A1 in B1. Let’s see an example to understand the relative reference.
Consider the below screenshot of earnings and expenses from Jan to May.
Now we want to calculate the cash left after the expenses paid from the earnings. We can get that with the simple formula of subtraction of expenses from the earnings. Just input the formula next to expenses to find the remaining cash each month.
Observe the below screenshot I have input the formula next to expenses which you can find from the formula bar.
If we observe the formula in D2 is B2-C2 that means D2 is referring to the combination of B2 and C2. When we drag the formula to the bottom lines it will take similar as D2, which means for D2 took reference from B2 and C2 similarly for D3 it will take reference from B3 and C3, For D4 take reference from B4 and C4, and so on.
We can observe the related screenshots for other months. The below screenshot is D3 related which is referring to B3 and C3.
D4 related screenshot which is referring B4 and C4.
That means the formula will always consider the same reference as same as the first formula as it relates to that. Hope you got an idea of what is a relative reference where we can use it.
Example #2 – Absolute Reference
Consider the same example to understand Absolute reference also. Let us consider from the above screenshot we have earnings and expenses let’s assume we have fixed rent expense, in addition, the expenses available in the table.
In the above excel, we mentioned rent in only one cell that is F2. Now we need to amend the formula in column D to subtract rent from the “cash left after expenses”.
Observe the screenshot we subtracted F2 from the result of B2-C2. Let’s drag the formula to the other cells also.
I dragged the formula but still the results do not change because in the cell D2 we subtracted F2 as explained earlier by default Excel will take relative reference, so it took F3 for the formula in D3 similarly F4 in D4. But we do not have any values in the cells F3, F4, F5, and F6 that is the reason it considers those cells as zero and did not affect the results.
How we can solve this without dragging rent to the cells F3, F4, F5, and F6. Here Absolute reference helps to achieve the correct results without dragging the rent.
It is very simple to create the absolute reference we just need to click on the formula and select the cell F2 and click D2 then the absolute reference will create.
If $ is applied for both the column index F and row index 2 then the absolute reference is at the cell level. Now drag the formula in D3, D4, D5, and D6 and see how the results change.
Observe the formula in cell D3 it is $F$2 only even after we dragged the formula. As it is locked at cell level it will take reference from F2 only. This is the use of absolute reference. This will take the reference from the locked index.
Example #3 – Mixed Reference
As mentioned earlier in mixed reference we will use both relative reference and absolute reference. Let’s see how we can use both using the same example.
Consider the data in the below format as shown in the screenshot.
From the above table, we need to find the percentage of earnings of each month against the “Total” of 5 months earnings similarly, percentages for expenses and cash left after expenses.
If still not clear, we need to find 5000 percentage out of total 5 months earnings 32100.
Now in the below empty table, we need to find the percentage for each cell.
The formula for finding the percentage is divided Jan earnings with total 32100.
If you observe I have used the absolute reference because all month’s earnings should divide with total only. Now we can drag the formula until the month of May.
Now drag the formulas to the bottom also.
If we observe still the formula for expenses is taking a total of Earnings only but actually it should take expenses total of 15200. For that, we need to create reference at column level alone not required at row level.
Observe the formula $ symbol is available at N not at row index 11. Make the changes always to the first formula then drag to the other cells.
Everywhere the formula is picking correctly. If we observe, we performed the absolute reference in row operations and relative reference in column operations. Hope now it is clear how to perform mixed reference and in which areas it will be useful.
Things to Remember
- A mixed reference is a combination of relative reference and absolute reference.
- Relative reference is a default reference in excel while referring to any cells in excel operations. In this other cell will take a similar reference as the first formula.
- An absolute reference is another type where it will take the reference as fixed where ever you paste the formula.
- To create an absolute reference, select the cell reference and click on F4 then the $ symbol will add to the row index and column index.
- Mixed reference is a combination of both hence in this we may apply an absolute reference to column index alone or row index alone.
- $A1 – Means reference created for column index.
- A$1 – Means reference created for row index.
- $A$1 – Means reference created for both row and column indexes.
Recommended Articles
This is a guide to Mixed Reference in Excel. Here we discuss How Relative and Absolute References work with Mixed References in Excel along with examples and a downloadable excel template. You may also look at the following articles to learn more –
