Multiplication Table Challenge Solution

The Challenge

In the last blog post I set a challenge for you.

Create a table similar to the one below, in cell B2, create a formula that can be copied to all other cells in the table.  The result of this will be a multiplication table.

multiplication table layout for challenge


The Solution

The solution in theory is very simple, multiply the numbers in row 1 by the numbers in column A.  The formula in B2 would then be =A2*B1

which works, but if you copy this formula to a few more cells you can see it goes wrong.

simple multiplication example

3 times 2 is not 24 (even I know that!).

Our formula is a relative formula.

If you copy it 2 columns to the right of the original, the column letter increases by 2.  If you copy the formula down 2 rows the row number increases by 2.

So, the formula in C4 is =B4*C3 which is 6*4=24.


We will need to use a mixed reference formula.

We want to multiply row 1 every time by column A, so we will make this part of the formula absolute, this is done by adding a dollar sign $ before the column letter or row number so that they don’t change when the formula is copied to other cells.

The solution to the problem is =$A2*B$1 and we can copy that formula across rest of the cells.

the solution to the challenge


Now when you copy the formula, it will always reference column A and row 1, but the other half of each cell name will still be relative.


Did you get it yourself?


[grwebform url=”” css=”on”/]




Click Here to Leave a Comment Below 1 comments

Get every new post delivered to your Inbox

Join other followers:

%d bloggers like this: