Using array formulas is fairly straight forward once you understand the basic concepts. An important thing to remember is that you should select arrays of the same size for use in array formulas (same number of rows and columns). Array formulas that involve cell ranges (arrays) of different sizes, will not work.

Take the following worksheet as an example of a basic array formula.

Here we have a simple group of numbers, made up of 15 rows by 3 columns of data. Let’s say that you want another block of numbers the same size, containing each of the original numbers divided by 3 and increased by 100.

To do this, you could select a block of cells the same size as the original, and type the formula =C2:E16 /3 +100 into the formula bar, being sure to press Ctrl + Shift + Enter after the formula has been typed.

You can see that each number in the original block has been divided by three and added to 100 before being entered into the second block. You can also see the curly braces around the formula displayed in the formula bar. Every time you enter or edit an array formula you must press Ctrl + Shift + Enter to ensure that Excel recognises it as an array formula.

In the following example we have two columns of numbers, C2:C16 and E2:E16. Note that both columns of numbers are the same size. You can select a third column of the same size, type the formula =C2:C16*E2:E16, and press Ctrl + Shift + Enter.

Each cell in the fist column will be multiplied by the corresponding cell in the second column, with the result being displayed in the third column.

Note the curly braces around the formula displayed in the formula bar. If you only pressed Enter, the curly braces would not be added, and Excel would not recognize the ranges as arrays. As a result, you would only see the value of the first two cells multiplied together, 4840, in the cell H2.

When entering your formula you can select the ranges in the formula with your mouse rather than typing them directly. Also, any changes made to the source data will be recalculated and automatically updated in the area containing the formula results. Always remember to press Ctrl + Shift + Enter if you want to enter an array formula. If you press Enter by mistake, just click on the cell containing the formula, press F2, and then press Ctrl + Shift + Enter.