Creative Commons License

This work is licenced under a Creative Commons Licence.

User login

Auto-filling of grids of calculations in a spreadsheet.

A quick explanation of how to fill in calculations in a grid in a spreadsheet like excel.

If you want to multiply two cells together in excel, a simple formula is all that is required (e.g. =A1*B1). However, when you want to multiply a large set of numbers by another large set of numbers, and get each answer separately, you don't want to have to type in a formula in each cell. An example of this would be creating a grid when learning multiplication tables: you have the numbers 1 to 10 down one side, and the numbers 1 to 10 across the top. It's not immediately straight forward how to get Excel to fill the answers into each of these cells.
What we need here is to use a mixture of relative and absolute cell references. Assuming that the numbers are in cells A2-A11 and B1-K1, with A1 blank, the formula you would put in B2 is:


This can then be copied across the entire range to calculate the answer.