A Multiplication Table in EXCEL
Create a multiplication table in Excel
By the way, I’ll give you different ways to handle formulas: first of all in ranges using the so-called Table formulas or a keyboard shortcut by completing the edited formula in the selected range. In addition, I will give a lot of keyboard shortcuts, which significantly facilitate and accelerate our work, which makes us become more effective and thus more competitive, even on the labour market.
Construction of the multiplication table
Let’s assume we’re creating a multiplication table with a sheet at the top in the left corner.
1. Vertical insertion
Put vertically in succession numbers from 1 to 10 in rows starting from cell A2.
a) each number individually
b) or 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 put 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
• 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.
You can learn similar tips and tricks in Excel at a comprehensive Training in London.