A Multiplication Table in EXCEL

  • Post author:
  • Post category:Excel

A Multiplication Table in EXCEL

Create a multiplication table in Excel

Let me give you some different ways to handle formulas. Firstly, using the so-called Table formulas by completing the edited formula in the selected range. Secondly, I will provide you with a lot of keyboard shortcuts, which significantly facilitates and accelerates our work. All above makes us more effective and thus more competitive, e.g. on the labour market.

Construction of the multiplication table

In this example, we are creating a multiplication table with a sheet at the top left corner.

1. Vertical insertion

Fill a column with successive numbers from 1 to 10 in rows starting from cell A2.
a) each number individually or
b) insert 1 into A2 and 2 into A3, then select both cells, and then, by dragging the mouse in the lower right corner of cell A3 up to cell A11, fill the remaining cells automatically with successive numbers
c) you can also use the quick fill method by selecting the tab
• Home, in the Editing section, select the Fill and Series icons

2. Horizontal insertion
Analogously, we input 10 numbers, but this time horizontally starting from cell B1 to K1
a) You can use a similar method as above, instead of Columns, select Rows.
b) Alternatively, if you have already inserted a sequence of numbers vertically, you can use it horizontally:
• check the created range of numbers A2: A11;
• copy CTRL + C to the clipboard;
• from cell B1 paste as Transposition
Paste Special / Transpose.
c) In addition, there is one more way to insert a transposed number, rigidly linked with each other using an array formula.
• first select the range B1: K1, in which you want to enter the formula.
• in the function brackets as an argument we put the range we want to copy = TRANSPOSE (A2: A11)
• and we accept CTRL + SHIFT + ENTER
• then select Columns, Step value: 1, Stop Value: 10 and OK

Solution of the multiplication table

a) Array formulas

• Select range B2:K11
• Insert formula =A2:A11*B1:K1
• Use shortcuts CTRL + SHIFT + ENTER


b) Formulas mixed cell reference

• Select range B2:K11
• Insert formula  =$A2*B$1
• Use shortcuts CTRL + ENTER

Formatting the multiplication table

• Select the entire table CTR + *
• Home> Conditional Formatting> Colour scales and select 2 options, which will cause the highest values to turn red, the lowest to green and between the intermediate colours.

Excel spreadsheet download

Download the sample file containing the solutions here.

Download Free

You can learn similar tips and tricks in Excel at a comprehensive Training in London.