What Is Mixed References In Excel?
A mixed reference is a form of cell reference in which one of the reference rows or columns is absolute while the other is relative. It, thus, is a mix of absolute and relative references. The ‘$‘ symbol put before the row or column denotes it is the fixed component in the mixed reference. It helps users copy formulas across the worksheet without making any changes manually.
For example, $C3 is a mixed cell reference to cell C3. Suppose you use this cell reference in a formula and copy it into the cells further down in the column. Then the reference to column C remains unchanged, while the row keeps changing with every cell where the command gets applied. The below table shows the tax calculation for a list of stationery items, and the output gets stored in cells F3:F6 and G3:G6.
Let us apply the formula in cell F3, which uses mixed references.
When you copy the formula in cells F4:F6 and G3:G6, the absolute cell references remain static, while the relative references vary, as shown in the image below.
Therefore, you do not need to edit the formula while copying it in the required cell range. Instead, you have to drag the cursor to select the cell range and get the result using the formula you entered in cell F3.
Table of contents
Key Takeaways
- Mixed references in Excel have either the cell row or the column as an absolute value when referencing a specific cell in a formula.
- It is a mix of absolute and relative references that allows users to replicate formulas across a worksheet without manually making any adjustments.
- The shortcut to applying mixed reference in a formula is to select the cell reference and press the F4 key to add the ‘$’ symbol ahead of the reference row or column.
- You can use mixed references in a formula in multiple worksheets and even across different workbooks.
Excel VBA – All in One Courses Bundle (35+ Hours of Video Tutorials)
If you want to learn Excel and VBA professionally, then Excel VBA All in One Courses Bundle (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.
Understanding Mixed Cell References
You may find it complex when comparing mixed references in Excel with absolute and relative cell references. However, they save precious time and reduce errors when you have to perform calculations across several rows and columns using specific values.
You need to ensure that the part in the cell reference, i.e., row or column, you want to maintain as a static value has the ‘$’ symbol before it.
Consider the illustration shown in the previous section. Let us see how the mixed references help in simplifying calculations while avoiding errors.
Step 1: Select cell F3, in this case, to get the output. Enter ‘=’, ‘$’, B, 3, ‘*’, ‘$’, C, 3, ‘*’, and select cell F2.
Step 2: Press the Enter key to display the result.
Step 3: Drag the cursor downwards from cell F3 to cell F4 to copy the formula.
You will observe the third term in the formula changing from F2 to F3. But as per the tax calculation requirement, the row part of the third term has to remain fixed, i.e., F column and row 2. Thus, the cell reference to the row has to be absolute, while the column reference can remain relative.
Step 4: Make the row reference for cell F2 absolute. Select cell F2 in the formula you entered in cell F3. Press the F4 key twice to introduce the ‘$‘ symbol before row 2. It is the shortcut for applying mixed references in Excel. The formula in cell F3 becomes:
=$B3*$C3*F$2
Step 5: Drag the cursor downwards from F3 to cell F4 to copy the formula. Now, the new command in the cell F4 becomes:
=$B4*$C4*F$2
Step 6: Press the Enter key to view the result.
Please Note: Mixed references allow you to create the absolute reference of the required cell row or column at a time. You can then use it as a constant when copying a formula, with the mixed reference of the specific cell, to another cell in the worksheet.
Thus, you can ensure calculations are correct across all the specific rows and columns.
Mixed Reference Syntax
Assume you wish to use a mixed reference of cell B2 in another cell, with absolute reference for column B and relative reference for row 2. The syntax in the target cell will be:
On the other hand, if you require the row as a fixed part and column to vary for the cell B2, the syntax becomes:
The ‘$‘ symbol denotes absolute reference, and the part of the cell coordinate without the ‘$’ symbol has a relative reference.
How To Use Mixed References In Excel?
You can use mixed references in the following ways:
1. Manually Enter ‘$’ Symbol Before the Required Cell Row or Column
- In the cell where you wish to type the formula with mixed references, enter the ‘=‘ sign, and start typing the formula.
- When you have to enter a cell reference, with its row or column having absolute, enter the ‘$’ symbol in front of the row or column.
- Ensure the ‘$’ symbol precedes every row or column reference for the cells you wish to keep mixed references in the formula.
- Finally, press the Enter key to get the output.
2. Using the Mixed References in Excel shortcut
Below are the steps to use Mixed References in Excel
- Enter the required formula in the specific cell.
- Now, select the cell you want to have a mixed reference for in the formula.
- Press the F4 key, the shortcut to using mixed references in Excel. You can add the ‘$’ symbol before the row or column by pressing the key.
- Complete the command and press Enter to view the result.
Please Note: Suppose the required cell initially has a relative reference. Then, you need to press the F4 key twice to add the ‘$‘ symbol before the row and thrice to append it before the column to create mixed references.
Let us look at a more straightforward illustration before moving to advanced examples of mixed references in Excel.
The below image shows two tables.
The first table contains a list of employees and their salaries between 2015 and 2017. And the second table shows the bonus they received each year.
The aim is to calculate the total compensation after bonus for every employee for 2015, 2016, and 2017 and display the results in columns E, F, and G.
Below are the steps to perform the calculations:
Step 1: Select cell E2 in this case to get the output. Enter ‘=’ and select cell B2. Then type ‘+’, ‘A’, ‘$’, and ‘11’. The formula will be as below:
=B2+A$11
Step 2: Press Enter. You will get the result of $6,000 in cell E2.
Step 3: Next, drag the cursor through cells E2:E6 to copy the formula with the mixed reference in the E column cells:
Step 4: Place the cursor on cell E2 and drag it through cells F2 and G2 to copy the formula and then through cells F2:F6 and G2:G6 to get the result.
Since each year has a fixed bonus value in the above example, using mixed references enables you to add the appropriate bonus to the respective years’ salary for each employee without making any errors.
Please Note: The second term in the expression has a mixed reference. Using mixed references in Excel ensures you use static rows or columns wherever required during your calculations.
Examples
Let us look at a few more examples of mixed references in Excel to understand the concept better.
Example #1
The below image shows two tables. The first one is for Tile Types and their respective Cost in $. And the second table is for calculating the price difference in the United States and Australia based on a certain percentage.
You can use mixed references in Excel to calculate the rate difference between the two countries.
Below are the steps to follow:
Step 1: First, select cell D3 to enter the formula. Enter ‘=’, ‘$’, B, 3, ‘*’, ‘(‘, 1, ‘+’, D, ‘$’, 2, and ‘)’ to complete the expression. So, the formula becomes:
=$B3*(1+D$2)
Step 2: Press the Enter key to view the output.
Please Note: The expression in cell D3 references cells B3 and D2 as mixed references. Cell B3 has its column fixed while its row value varies as you copy the formula in other cells. Likewise, for cell D2, the row remains static while the column keeps changing when using the formula in other cells. Therefore, the mixed cell references assure you add 10% to the costs for the U.S. and deduct 5% from the prices for Australia, adhering to the requirement.
Step 3: Drag the cursor downwards through cells D3:D7 to copy the formula.
Step 4: Place the cursor on cell D3 and drag it to cell E3 to copy the formula and then through cells E3:E7 to get the result.
As column B is static and the row varies in the formula, the first term gets updated from B3 to $B7 when copying the formula from cells D3:D7 to cells E3:E7. And the second term, i.e., row 2, remains unchanged, while column E has a relative reference. Hence, the reference changes to E$2 when applying the formula (used in cells D3:D7) in cells E3:E7.
Example #2
The following illustration shows how to use mixed references in Excel when working with multiple worksheets.
Suppose you have to calculate the compound interest (CI) based on different interest rates using two worksheets – CI_Principal&Time and Compound Interest.
In the first worksheet, CI_Principal&Time:
- Column A shows the Principal in $
- Column B lists the Years
- Column C shows the Compounding Periods Per Year
The second worksheet, Compound Interest, is where you calculate the CI for various interest rates. You can use the FV function to find the CI. FV, or future value, is a financial function that returns the future value of an investment depending on a constant interest rate.
The steps to calculate compound interest using the FV function with mixed references in Excel are:
Step 1: In the worksheet Compound Interest, select cell B3 and enter the formula.
Enter ‘=’, FV and ‘(‘, B, ‘$’, 2, and ‘/’. Then go to worksheet, CI_Prinicpal&Time, and select cell C2, type ‘,’, select cell B2, type ‘*’, select cell C2, type ‘,’, 0, ‘,’, ‘–‘, and select cell A2.
Since each cell reference in the expression needs to be mixed, select cell C2 and press the F4 key thrice to insert the ‘$‘ symbol before the column. Likewise, repeat the process for cells B2, C2, and A2. And the formula in cell B3 of the worksheet, Compound Interest, becomes:
=FV(B$2/’CI_Principal&Time’!$C2,’CI_Principal&Time’!$B2*’CI_Principal&Time’!$C2,0,-‘CI_Principal&Time’!$A2)
Please Note: The formula appears in the first worksheet, CI_Principal&Time. The reason is the formula in the worksheet Compound Interest has cell references from this worksheet. And on pressing the Enter key, the CI for a principal of $2,000 and interest rate of 10% gets displayed in cell B3 of the worksheet, Compound Interest.
Step 2: Press the Enter key to get the output in cell B3 of the worksheet, Compound Interest.
Step 3: Next, drag the cursor from cells C3:E3 in the worksheet Compound Interest to copy the formula.
Step 4: With the cursor on cells B3:E3 in the worksheet Compound Interest, drag through cells B4:E7 to copy the formula. You will have the output as shown below.
Please Note: The interest rate while calculating CI remains fixed in columns B to E (10%, 5%, 15%, 12%). Also, the information fetched from the worksheet CI_Principal&Time remains static for each Principal.
Important Things To Note
- Mixed references in Excel ensure either the specific row or column reference remains fixed when copying a formula into another cell.
- In mixed references, either the row or column can remain absolute at a time.
- One can manually insert the ‘$‘ symbol before the row or column they wish to lock. Alternatively, they can use the shortcut. Select the cell reference in the formula and press the F4 key twice or thrice to insert the ‘$‘ symbol before the row or column.
- You can use mixed references when working with functions, such as FV, and across different workbooks.
Frequently Asked Questions (FAQs)
Users can manually insert the ‘$’ symbol in front of the reference row or column to create a mixed reference in a formula. Otherwise, you can apply the shortcut. Select the cell reference in the formula and press the F4 key twice to add the ‘$‘ symbol ahead of the row or thrice to add the ‘$’ sign in front of the column.
Relative reference to a cell in a formula implies the row and column of the cell referenced changes when copying the formula into another cell. On the other hand, when a function uses absolute reference, the specific cell row and column remain unchanged. And in mixed references, either the row or column remains locked at a time. The other component has a relative reference.
Consider the table shown in the image below:
The aim is to find how the value in Input 1 will affect values in Input 2 and display the result in cells C2:C4. However, while Input 1 has one value, Input 2 has three values. So, the Input 1 value must remain static, and Input 2 value has to change when copying the multiplication formula from cell C2 to cells C3 and C4.
The row reference of cell A2 remains unchanged through cells C2:C4. Thus, the formula will have a mixed reference to A2 as A$2:
You can use mixed references when you want to keep either a cell row or column as absolute while copying a formula into another cell. It could be in the same worksheet or even a different workbook.
Download Template
This article must be helpful to understand the Mixed References in Excel, with its formula and examples. You can download the template here to use it instantly.
Recommended Articles
This has been a guide to Mixed References in Excel. Here we learn using mixed references in excel with examples and a downloadable excel template. You can learn more from the following articles –
Leave a Reply