Create a Multiplication Table

November 25, 2021 - by Bill Jelen

Create a Multiplication Table

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.
Figure 179. Need a formula to point to row 1 and column 1.

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.
Figure 180. Multiply column A by row 1.
  • 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.
Figure 181. =$A13*M$1 creates the multiplication table.

This article is an excerpt from Power Excel With MrExcel

Title photo by Gayatri Malhotra on Unsplash

Bill Jelen is the author / co-author of:
MrExcel 2021 - Unmasking Excel

This is a 5th edition of MrExcel XL. Updates for 2021 include: LAMBDA, LET, Power Query Fuzzy Match, Sort & Filter in Sheet View, Cut-out people, Save object as image, STOCKHISTORY, Wolfram Alpha Data Types, Custom Data Types from Power Query, Weather data types, bilingual spreadsheets, Performance improvements, Unhide multiple worksheets, Action pen, Collapsible task panes, LET function to re-use calculations, store formulas using LAMBDA, Recursive LAMBDA, Branching LAMBDA, Lambda to return a picture, Excel function quick reference.