In the example shown, the formula in C5 is: Note both cell references have absolute and relative elements, so they are referred to as “mixed references”. In $B5, the column is “locked” so that it won’t change, and in C$4, the row is locked. As the formula is copied, this is what the references look like for the first 5 rows and columns: Mixed references are a common feature in well-designed worksheets. They are harder to set up, but make formulas much easier to enter. In addition, they are a key strategy for preventing errors since they allow the same formula to be copied to many locations without manual edits.

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.