In Excel, a mixed reference means a cell is partly locked. You can lock either the row or the column when using a mixed reference. This is handy when you're dragging a formula across a table. In this guide, we'll demonstrate how to use a mixed cell reference in Excel with a real-life example.
How to Use Mixed Reference in Excel
When you use a mixed reference in Excel, it's a bit different from an absolute reference. You put a dollar sign ($) either in front of the row or the column. The part without the dollar sign stays relative.
For instance:
- $A$1 means both the column (A) and the row (1) are absolute references.
- $A1 means column A is absolute, but the row (1) is relative. So, the row might change in a formula.
- A$1 means column A is relative, but the row (1) is absolute. So, the row won't change, but the column might be in a formula.
Note: To make a cell with absolute reference press F4 ($A$1). Press F4 twice to create a mixed reference in a cell. The first press will lock the row (A$1), and the second press will lock the column ($A1).
Example to Use Mixed Reference in Excel
Let's say you have a list of prices in Excel, and you want to calculate the discounted prices. In column B, you have the original prices. We'll use a formula to find the discounted prices after a 5% and 10% discount. In cells D4 and E4, we entered the discount percentages.
- Now, in cell G4, enter a formula to calculate the discounted price of $100 (from B4) after a 5% discount (from D4). We want to use this formula for other prices in columns G and H as well.
- To do this easily for the whole column, use AutoFill. Make sure to lock the column for the original prices (B4) by using $B4, so it doesn't change when you drag the formula. Also, lock the row for the discount percentage (D4) by using D$4, so it stays the same in all cells.
- Use AutoFill to copy the formula to cells G4 to H9.
- Look at the formula in cell H9 to see how the mixed cell reference is applied according to what we wanted to achieve.
Therefore, we hope you have understood the basics of mixed cell reference in Excel for two different purposes. If you have any queries, please contact us or leave a comment.