Mixed reference for multiplication table

An excel formula to mixed reference for multiplication table

 Mixed reference for multiplication table

To build a single formula that you copy throughout a multiplcation table, you can use a mixed reference.

In the example shown, the formula in C5 is:


Both cell references have absolute and relative elements, so they are referred to as "mixed references".

How this formula works

This formula is designed to be copied throughout the interior of the multiplication table without change. In other words, when the formula is copied to other cells in the table, the references will automatically update as needed to calculate the product of the corresponding row and column.

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:

  1 2 3 4 5
1 =$B5*C$4 =$B5*D$4 =$B5*E$4 =$B5*F$4 =$B5*G$4
2 =$B6*C$4 =$B6*D$4 =$B6*E$4 =$B6*F$4 =$B6*G$4
3 =$B7*C$4 =$B7*D$4 =$B7*E$4 =$B7*F$4 =$B7*G$4
4 =$B8*C$4 =$B8*D$4 =$B8*E$4 =$B8*F$4 =$B8*G$4
5 =$B9*C$4 =$B9*D$4 =$B9*E$4 =$B9*F$4 =$B9*G$4

Mixed references are a common feature in well-designed worksheets because they prevent errors by allowing the same formula to be copied to many locations without manual edits.

0 votes. 0 / 5