Leading the way in Microsoft Office Development

Step 1 - What are Arrays in Formulas ?

For more details on array formulas please refer to the Array Formulas section.

It is possible to use arrays in your formulas and the reason you may want to do this is because it lets you enter the values directly as opposed to having to use cells.

When you use arrays in formulas you do not need to enter them using (Ctrl + Shift + Enter). To enter the formula press Enter.

Step 2 - Examples of Array in Formulas

Example 1 - The table below shows how you can use an array constant.

Exactly the same result can be obtained by placing the numbers into an array and passing the array (as a whole) to the worksheet function.

Array Constants can contain numbers, logical values (i.e. True or False) or text.

You can use different types of data in the same array constant.

Example 2 - The table below shows more array constants.

Step 3 - Difference between Arrays in Formulas and Array Formulas

Example 3 - The table below shows more array constants.

All the formulas in column F are equivalent and all return the total 110.

Formulas can work with arrays that are contained in cells, arrays that are constant as well as array formulas.

Step 4 - Things to Remember

You cannot change any of the individual cells within an array block.

If you accidentally enter an array formula by not pressing (Ctrl + Shift + Enter) then the formula will either return an incorrect value or #VALUE! will be returned.