Excel Data Interview Series: Array Formulas in Excel

Hey yo, everyone. Welcome to Day 2 of the Excel Data Analyst Interview Questions series. You can read up yesterday’s topic — Conditional Formatting in Excel — here. In continuation this series, today, we’ll be discussing Array Formulas in Excel.

First off, what is an array? An array in Excel refers to a group of values — they could be in a row, column, or both. Simply put, it is a collection of two or more items. An array formula is a formula that will perform calculations on the array. With an array, a user can perform multiple calculations on multiple items using just one formula.
For instance, you have data on your Excel spreadsheet containing the Month, Price, and Quantity of Units Sold of products, and you want to calculate how much was generated per each product. Instead of multiplying the values row by row (e.g. B2 by B3, C2 by C3, and so on), you could use an array formula. To do this, you need to highlight all the cells where you want the array formula to be in (D2:D25), then type the equal sign and highlight the Price & Quantity Sold columns (B2:B25 * C2:C25), and press Ctrl + Shift + Enter, and the results will spill down the D column.
This is called a multi-cell array, as the results will be stored in multiple cells. An array formula in a single cell is called a single-cell array.

In our Marketing Campaign dataset, let’s make use of an array to calculate the total amount each customer has spent.

Note: To create an array formula, you have to press Ctrl + Shift + Enter, else you’ll just create a regular formula.

“So how is this any different from a regular Excel formula?”, you might ask. Well, the major difference between these two is that an array formula processes multiple values instead of one. While a regular formula performs operations on an individual value, an array formula performs multiple calculations and processes multiple values at once, depending on the condition used with the array formula, thereby making the array formula the most efficient to work with, especially when one is working with a lot of data.
Other advantages include:
• A single array formula can replace literally hundreds of regular formulas
• Array formulas are useful for performing complex calculations with ease
• They ensure consistency because all formulas in the table are exactly the same
• A model containing arrays will use less memory and be more efficient
• They are especially useful for performing calculations on large datasets or for performing calculations that require multiple steps

One major disadvantage of array formulas, however, is their difficulty in being edited.

Also, though they use less memory, they can slow down your calculations if there are too many large arrays in one model.

That’s it for today. Give this lots of claps and comments, and I hope to see you tomorrow for our discussion on (Regular) Formulas in Excel. 🤗