Quick, clean, and to the point

Multiplication table formula

Excel formula: Multiplication table formula
Generic formula 

Building a multiplication table in Excel is a classic problem because it requires a "mixed reference" – a reference that is partially absolute, partially relative.

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".

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. 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

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.