Mixed references can be used to set up formulas that can be copied across rows or columns without the need for manual editing. In some cases (3rd example above) they can be used to create a reference that will expand when copied. Mixed references are a common feature in well-designed worksheets. They are harder to set up, but they make formulas much easier to enter. In addition, they significantly reduce errors since they allow the same formula to be copied to many cells without manual edits.
Example shown
In the example shown the formula in E5 is: This formula is carefully constructed with two mixed references so that it can be copied across the range E5:G7 without manual changes. The reference to $C5 has the column locked to make sure the formula continues to pick up price from column C as it’s copied. The reference to E$4 has the row locked so that as the formula is copied down from row 5 to row 7, the formula will continue to pick up the percentage value in row 4.
Toggle between absolute and relative addresses
When entering formulas, you can use a keyboard shortcut to toggle through relative and absolute reference options, without typing dollar signs ($) manually. Author
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
