Excel for Accounting & Finance Professionals (Part 5)

Array Formulas in Excel

Arrays in Excel are a powerful tool used to store multiple values in a single variable. An array can be thought of as a row or column of values, or a combination of rows and columns (a table of values). In Excel, arrays can be used in formulas to perform multiple calculations on a set of values. Here’s a bit more detail:

Single Cell Array: This is when an array formula is entered into a single cell. The formula will perform calculations on multiple items but will return only one result. For example, =SUM(C2:C11) sums all the values from C2 to C11, but it returns a single sum.

Multi-Cell Array: When an array formula is entered into multiple cells, it can perform multiple calculations, and each cell in the array range can return a result. For instance, if you have two columns of numbers A2:A11 (USP) AND B2:B11 (QTY) and you want to multiply each corresponding set of numbers, you could use an array formula by selecting C2:C11 =A2:A11*B2:B11 and pressing <CTL+Shift+Enter> to enter the formula.

Dynamic Array Formulas: Starting with Excel 365 and Excel 2019, Excel introduced dynamic arrays that automatically spill over, meaning that if a formula returns multiple values eg by entering in Cell C2 =A2:A11*B2:B11 pressing the <ENTER> key, it will automatically fill the neighboring cells with the additional values without having to enter formulas into each individual cell.

Array Constants: These are arrays where the values themselves are hard-coded into the formula. They are enclosed in braces {} and the values are separated by commas for new columns and semicolons for new rows. For example, ={1;2;3;4;5;6;7;8;9;10} defines a 10x1 array which can also be substituted with Excel ROW function.

Arrays are especially useful in Excel for complex calculations and can greatly simplify the use of repetitive formulas. They are a foundational concept for anyone looking to perform advanced data analysis or manipulation in Excel.