Learning about dynamic arrays

Let's first understand the term array and when to use it. An array is simply a collection of data that can be stored in one or multiple rows or columns. Data within these columns can be numbers or text. In Excel, an array is a formula that performs multiple calculations on one or more items in an array.

Array formulas can return multiple or single results. We can use arrays to eliminate and speed up the number of actions we need to perform in Excel to produce a result. To follow along with the next example, open the workbook named Arrays.xlsx.

For instance, in the example that follows, the usual method you could implement to arrive at a grand total for the cost of each course would be to work out the total amount for each course and then sum the totals to establish the grand total.

Figure 10.1 – Working out grand totals using the traditional method

This method is great, but we could speed this up by making use...