Array formulas in Excel - Tutorials and examples

An array formula is a formula that can perform multiple calculations of one or more items in an array. Array formulas can return multiple values ​​or a single result. Today Dexterity Software will introduce readers to some common array formulas that are used often.

Array formulas in Excel - Tutorials and examples Picture 1

Example of a multi-cell array formula

You have a detailed list of quantity and unit prices of some items sold in the month:

Array formulas in Excel - Tutorials and examples Picture 2

To calculate the total sales, you must calculate the sales of each item, then add up.

Array formulas in Excel - Tutorials and examples Picture 3

Instead of the above, replace it with the above = SUM (C4: C13 * D4: D13) and press Ctrl + Shif + Enter .

Array formulas in Excel - Tutorials and examples Picture 4

Note: For an array formula, you must use Ctrl + Shift + Enter to end the formula otherwise it will produce an error.

Array formulas in Excel - Tutorials and examples Picture 5

Example of array formulas to create constants

You create the array constant by entering the list of items, then enclosing the list in curly braces. For example, you need to enter the list of Apple, Lemon, Orange. You choose cell A3: C3. In the formula bar, type = {"Apple", "Lemon", "Orange"}. Then press Ctrl + Shif + Enter .

Array formulas in Excel - Tutorials and examples Picture 6

And this is the result:

Array formulas in Excel - Tutorials and examples Picture 7

Some other examples use array formulas

In addition to the SUM function example as above, you can use array formulas with other functions like Left, Right, Mid, Max, Min, If .

LEFT, RIGHT, MID and array formulas

Consider the following example for the data array:

Array formulas in Excel - Tutorials and examples Picture 8

The MAX function uses an array of data

To find the maximum value of AB in the example below:

Array formulas in Excel - Tutorials and examples Picture 9

You must perform two calculations, first you calculate the difference of points A and B, then find the maximum value.

Array formulas in Excel - Tutorials and examples Picture 10

Instead of having to do the above two steps, you use the array formula = MAX (B4: B12-C4: C12) and press Ctrl + Shiff + Enter .

Array formulas in Excel - Tutorials and examples Picture 11

Above Software Tips showed you how to use array formulas in Excel. Good luck!