Creating Arrays [Excel Array Formulas]

 

Array formulas are some of the most powerful functions in Excel.

They let you perform multiple calculations all at once.

But we often find even on our advanced Excel courses that they are some of the hardest to understand!

So let’s break down in clear and simple steps how to make arrays, and array formulas.

Creating A Simple Array

In Excel, an array is a collection of items populated together as a column or a row.

We all have created tons of arrays in Excel by populating cells one by one.

But you can also make one by selecting cells, and writing a formula like so:

 

Then press Ctrl + Shift + Enter together, and take a look what happens.

This is a horizontal array constant as it spreads across the columns.

You may create a similar vertical array constant by selecting 4 rows instead of 4 columns before the formula is punched in.

You can also make an array with random data using the RANDARRAY function, one of Excel’s dynamic array functions.

Making An Array Formula

Entering an array formula in Excel is all about three concurrent keystrokes.

 

The image below represents the sale details for different products.

These details include the quantity, sale price, and discount offered per product.

We want to find the net sales (net of discount) for the month.

With array formulas, the whole process is done in a single step.

Simply activate the cell against the total sales and set up the formula as follows.

Breakdown of the formula:

• The cell range B3:B9 consists of the quantity of each product ordered during the period under calculation.
• This range is multiplied by the cell range C3:C9, which consists of the sale price of each product.
• Another multiplication factor is added in the parenthesis for the calculation of the discount.
• The cell range G3:G9 consists of the discount rate per product. (100%-D3:D9) is to reduce the discount from the sale.

The formula is all constructed. However, do not hit only ‘Enter’, but ‘Ctrl + Shift + Enter’.

And this will be your result:

In bigger data sets, you can use the UNIQUE function to pull out unique values, which is great for analysing your data.

Multi-Cell Array Formulas

A multi-cell array formula is where the formula and results of the array formula are spilled across a range of selected cells.

The formula is composed the same way as that of a single-cell array formula.

Continuing with the example above, let’s reiterate the source data for finding net sales.

With a multi-cell array formula, you can find the net sales for each product, as the discount is per item.

If the discount was just in one cell, you would use an absolute cell reference, as it stays in place.

To enter the following formula, select cells E3:E9 where the net sales against each product are to be populated.

 

Press Ctrl + Shift + Enter to yield the results shown below:

And just like that, all your net sales have been calculated!

Then, if you want to sort through the data, you can use the SORT and SORTBY functions, specifically designed for arrays.

Conclusion

Excel array functions are incredible functions of the Excel.

The simple way they enable you to perform multiple calculations all at once is invaluable.

However, they can be extremely intimidating.

Make sure you practice plenty and take it step-by-step!