Array formulas in Excel

ARRAY FORMULAS in Excel

The purpose of this tutorial is to establish your understanding related to array formulas in MS Excel. Single cell array formulas perform multiple calculations in one cell.

Without Array Formula

In the following tutorial, you will learn how to get results without using an array formula.

1. In the figure below, a minus formula is applied to determine the progress of the students in two test. Column D holds the progress results.

2. In the figure below, you will observe the implementation of Max function to determine the Max value in range D2 to D6.

With Array Formula

Now, in the following tutorial, you will observe how effectively an array formula can be used to determine the complex results in less coding. MS Excel can store this range in its memory. A range stored in MS Excel’s memory is called an array constant.

Now from the previous tutorial (Without Array Formula) where you observed the calculation of individual student progress and ultimately the Max progress value. Now, keeping as Max value the desired result for the following tutorial as shown in the figure below, you will add the MAX function, replacing individual cell C2 with range C2:C6 and cell B2 with range B2:B6.

3. Finish the editing by pressing CTRL + SHIFT + ENTER.

Note that the formula bar above indicates that this is an “array formula” by enclosing it in curly braces {}. These curly braces can’t be applied by yourself as they are system generated and they will disappear when you edit the formula. If you will apply them on your own, your formula will be incorrect.

Using F9 Key to toggle Array view

While working with an array formulas, you can have a look as to what values these array contains. To do this, follow the steps below.

1. Select C2:C6-B2:B6 in the formula.

2. Press F9. This will toggle the view from range to array constants.

Elements in a vertical array constant are delimited by semicolons. Elements in a horizontal array constant are delimited by commas.