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
• 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.