Contents
Vocabulary
Under array usually understand a set of data united in a group. Arrays are one-dimensional (array elements form a row or column) or two-dimensional (matrix). It is easy to see that in almost any Excel spreadsheet, if desired, you can find one or more of these arrays:
Array formulas in Excel, these are special formulas for processing data from such arrays. Array formulas fall into two categories – those that return a single value and those that produce a whole set (array) of values as output. Let’s take a look at some simple examples…
Example 1. Classic of the genre – sales receipt
Task: calculate the total amount of the order. If you go the classic way, you will need to add a column where you multiply the price and quantity, and then take the amount for this column. If you apply the array formula, then everything will be much more beautiful:
- select a cell С7
- enter from the keyboard =SUM(
- highlight the range B2: B5
- enter the multiplication sign (star)
- highlight the range C2: C5 and close the bracket of the SUM function – in the end it should turn out like this:
- so that Excel perceives our formula as an array formula, press not Enter, as usual, but Ctrl + Shift + Enter
Voilà!
Those. Excel performed a pairwise multiplication of the elements of arrays B2:B5 and C2:C5 and formed a new array of costs (in computer memory), and then added all the elements of this new array.
Pay attention to the curly brackets that appeared in the formula – a distinguishing feature array formulas. Entering them manually from the keyboard is useless – they automatically appear when you press Ctrl + Shift + Enter.
Example 2. Allow me to… transpose?
When working with tables, it often becomes necessary to swap rows and columns, i.e. turn the table on its side so that the data that used to go in the row is now in the columns and vice versa. In mathematics, this operation is called transposition. Using an array formula and a function TRANSP (TRANSPOSE) it is done at once.
Let’s say we have a two-dimensional array of cells that we want to transpose.
- Select a range of cells to place the transposed table. Since the original cell array was 8 rows by 2 columns, we need to select a range of empty cells with a size of 2 rows by 8 columns.
- enter the transposition function =TRANSPOSE(
- as a function argument, select our cell array A1:B8
we press Ctrl + Shift + Enter and get a “reversed array” as a result:
Editing an array formula
If the array formula is located not in one cell (as in Example 1), but in several cells (as in Example 2), then Excel will not allow editing or deleting one single formula (for example, in cell D10) and will display a warning message Can’t change part of an array.
To edit an array formula, select the entire range (A10:H11 in our case) and change the formula in the formula bar (or by clicking F2). Then you need to repeat the input of the modified array formula by pressing the keyboard shortcut Ctrl + Shift + Enter.
Excel will also not allow you to freely move the cells included in the array formula or add new rows-columns-cells to the range of the array formula (i.e., to the range A10:H11 in our case)
Example 3. Multiplication table
Remember your childhood, school, your math notebook… What was on the back of the notebook on the cover? The multiplication table looks like this:
With the help of array formulas, it is all done in one movement:
- select range B2:K11
- enter the formula =A2:A11*B1:K1
- we press Ctrl + Shift + Enterso that Excel treats it as an array formula
and get the result:
Example 4: Selective summation
See how, with the help of one array formula, data for a specific product and customer is beautifully and easily selected:
In this case, the array formula synchronously runs through all the elements of the ranges C3:C21 and B3:B21, checking whether they match the given values from cells G4 and G5. If there is no match, then the result of equality is zero, if there is a match, then one. Thus, the sums of all transactions where the customer is not ANTON and the product is not Boston Crab Meat are multiplied by zero and only the necessary orders are summed up.
- Array formula to extract non-blank cells from a range
- Array formula to extract unique cells from a range
- Array formula to extract data from a list (reusable VLOOKUP)