Create a Multiplication Table
November 25, 2021 - by Bill Jelen
![Create a Multiplication Table Create a Multiplication Table](/img/excel-tips/2021/11/create-a-multiplication-table.jpg)
Problem: I want to create a multiplication table to help my kids in school. I want to be able to enter a single formula in cell B2 that I can copy to the entire table.
![The numbers 1 to 12 appear from A2 down to A13. The same numbers appear from B1 across to M1. With the cell pointer in B2, create a formula that can be copied to 144 cells to create a multiplication table.](/img/content/2021/11/LE10000159.jpg)
Strategy: In “Copy a Formula While Keeping One Reference Fixed,” you learned how to use an absolute reference, such as $C$1, so that Excel would not change from column C or row 1 as it copied the formula. To create a multiplication table, you need to use a mixed reference. A mixed reference, such as $A2, will lock the formula to column A while allowing the row to change. A mixed reference, such as B$1, will lock the row to row 1 while allowing the column to change.
The formula you need for the multiplication table is a formula that will multiply whatever is in row 1 above the cell by whatever is in column A to the left of the cell.
To have a reference that always points to row 1, you use something in the format of B$1. To have a reference that points to column A, you use a reference in the format of $A2.
-
1. Enter the formula =$A2*B$1 in B2.
![The first formula is =$A2*B$1. The single dollar sign before $A2 says that you want the current row but always column A. The single $ before the 1 in B$1 says you want the current column but always row 1.](/img/content/2021/11/LE10000160.jpg)
2. Copy the formula in B2 to the entire range.
Result: Excel will always properly multiply column A by row 1.
![Copy the first formula from B2 to B2:M13 and you have a multiplication table. The formula in M13 is doing =12*12 for 144. That formula is =$A13*M$1.](/img/content/2021/11/LE10000161.jpg)
This article is an excerpt from Power Excel With MrExcel
Title photo by Gayatri Malhotra on Unsplash