Contents
Sometimes an Excel user has to perform a huge number of tasks. It takes a lot of time. To greatly speed up the process, array formulas were invented. They allow you to perform very complex tasks automatically and simultaneously. When using conventional formulas, this becomes impossible.
Array formulas allow you to immediately perform a huge number of operations and get the result instantly. Let’s take a closer look at what array formulas (arrays of functions) are and how they can be used to make your life easier.
Vocabulary
In the broadest sense of this term, an array is a collection of data. In our particular case, this implies a set of functions.
In principle, any table or range can be considered an array.
An array formula from the received data returns either a single value or a result consisting of an array, depending on the type of function.
Types of Function Arrays in Excel
In general, the following kinds of function arrays in Excel can be listed:
- One-dimensional. Their characteristic feature is that they include only cells of one row or column.
- Two-dimensional arrays can contain a huge number of cells, both within the cells or columns of one sheet, and several.
Thus, arrays of functions make it possible to process a huge amount of data consisting of hundreds and even thousands of cells. And even those that are outside the sheet.
In turn, one-dimensional arrays can be divided into horizontal and vertical. The first include cells of one row, and the second – columns.
Array formulas allow you to process information from a wide variety of data.
The important thing is that this type of formula does not work with single values, but with all of them in one set.
Array Formula Syntax
Let’s imagine that we have a table that has a set of cells, and the amount due is described in one cell. For the first, we will look for intermediate results. In the second case, we will calculate the total amount.
First, we need to select the range to which the formula will be applied in the future. In our case, this is a set of cells starting with E3 and ending with E8.
Then we put the cursor in the formula bar, and write =C3:C8*D3:D8 there.
Convert formula to array format. You need to press Ctrl + Shift + Enter.
After that, we get a table with ready-made subtotals.
As you can see, after performing these operations, the formula was dressed in curly braces. It is they who say that this function will be processed as an array formula. We see that each cell of each row has turned into a finished result.
Important! It is impossible to change what was written by an array formula. Don’t worry about this. All information is securely protected.
If you try to make any changes to the resulting cell, you will receive a notification that the operation is prohibited.
Let’s look at another use case for an array formula. This time, we will output only one resulting value in the final cell. To achieve this goal, you must complete the following steps.
- Select the cell that will contain the result of the calculation using the array formula.
- Enter the formula there. In our case, we will sum the values from the target range, so we use the formula SUM(C3:C8*D3:D8).
- Using the combination Ctrl + Shift + Enter, we transform the standard formula into one that works with data arrays.
As a result, we get the following result.
In case we didn’t know how to use array formulas correctly, we would have to use two regular ones. And so we are spared this need.
Let’s take a closer look at the syntax of this formula.
In our case, one-dimensional arrays were used. The formula processes each, after which it performs the necessary results, and displays the result in a cell.
Components of array formulas:
- array function. This is a description of the operation that Excel should perform.
- Range arrays. These are directly those ranges that will be processed by the formula.
- The array operator is the colon sign. Indicates the distance in a certain number of cells between specific addresses.
To make it easier to understand, here is a small picture.
Analyze data with an array formula
Array formulas are very convenient to use if the task is to analyze information. In particular, you can check it for compliance with a certain criterion. Let’s say we have a table that describes sales by different managers. Three employees work in one department, and three types of goods are sold. And finally, each of them has its own price, and the final cost is recorded in the third column of the table.
We are faced with the task of determining how effective each of the managers is, given the goods they sell. You can do it yourself, but with the help of an array formula, this task can be completed in just a few clicks.
Let’s write down in the appropriate cells the information that will serve as a criterion for evaluating the effectiveness. In our case, this is the product and the identifier of the manager we are checking.
To successfully solve this problem, it is enough just to use a small array formula.
Our ultimate goal is to get the sum, so we need to use the appropriate function. In addition, we need to apply a condition against which the range will be checked.
The result is the formula shown in the screenshot.
It uses 3 multipliers. The first checks the managers. The second is goods. And the third – directly determines the amount of the order.
How does this formula work? Yes, very simple. Its algorithm is the following:
- Excel first checks all managers and products to see if they match the given criteria. If yes, the function returns 1, if not, 0.
20 - If the values do not correspond to at least one of the conditions, one of the factors becomes equal to zero. Accordingly, the final result will be similar.
- If both conditions are met, then the function performs the multiplication operation.
- Finally, all the obtained values are summed up, after which the cell gives the total efficiency for processed orders.
This is how a simple formula can be used to perform even the most complex information processing. Ultimately, you can upgrade the report somewhat by adding a drop-down list in which products and managers are selected. But this is a completely different topic.
Example: sales receipt
And now let’s start considering the features of working with array formulas in more detail, using specific examples. There are many types of tasks in which its use is a necessity. The simplest among them is a regular sales receipt.
Let’s say we have a set of products that have been sold to a customer. We know their price and the quantity that we managed to sell. our task is to calculate the total cost of each of the commodity items.
How would this problem be solved without using an array formula? First, we would need to calculate how many goods were sold in total. To do this, it would be necessary to multiply the quantity by the price.
After that, we would need to get the sum of all the costs to get the final result. Moreover, we would have to create a separate column in order to carry out the calculations.
And to do the same with an array formula, you just need to specify it.
Since we are faced with the task of calculating the sum, we need to use the appropriate function. In our case, we need to carry out the summation of products, so we need to write it down. First, select the desired range from column B, and then multiply by neighboring cells in column C.
True, if everything is left in the same form, an error will be generated after confirming the input of the formula using the Enter key. This is because our formula is not yet an array formula. To do this, press the combination Ctrl + Shift + Enter.
Why was the array formula more successful at this task? The sequence of actions was rather complicated. First, neighboring cells were multiplied horizontally, and then they were added. In simple words, it was necessary to carry out the summation of the array. In this case, the result was the same, and additional calculations were not required.
If you look at the formula bar, you will see that there are curly braces that show that a person is working with an array formula.
Important! These brackets are not text. They cannot be entered manually. If you need to edit the formula, then at the end of the input you again need to press the Ctrl + Shift + Enter combination instead of simply pressing the Enter key.
Example: swap rows and columns
Very often a situation may arise when you need to swap rows and columns. This operation is called transposition. With function arrays, this is incredibly easy.
Imagine we have such a two-dimensional array, located vertically.
We need to select the data range in which the finished table will be placed. Since in our example there are 8 rows and 2 columns, then, accordingly, it is necessary to select, on the contrary, 8 columns and 2 rows.
After that, you need to enter the formula =TRANSPby entering A1:B8 as the function argument.
After that, we press the above key combination to create an array formula, after which we get the transposed table.
Editing an array formula
As far as we already know, if you need to make changes to the final range, then this will not work. Excel will issue a warning message. But what should you do if you need to edit the formula?
It is not at all difficult to do this. First you need to select the entire resulting array, then put the cursor on the corresponding cell, and then enter changes in the formula. To confirm your actions, do not forget to press the key combination Ctrl + Shift + Enter.
Changing the contents of an array
Beginners often face difficulties in trying to change part of an array, because no matter how they try to get out, Excel still shows a message that this cannot be done. However, all ingenious is simple. In this case, too. You just need to follow a few simple steps:
- Find the “Cancel” button located to the left of the formula bar. Another option is to press the Esc key, which performs the same operation. In all cases, the lock will be removed and all operations can be performed again.
- Enter the array formula again with the same range.
Thus, changing the contents of the array is possible only if you cancel everything, and then re-enter the formula. Inconvenient, of course, but much more convenient than using standard formulas.
Array Functions
Consider the most commonly used array functions.
Operator SUM
Just like a regular function SUM, this operator sums the range values. The only difference is that with its help you can perform your actions in two stages: first, carry out the first operation with all the values of the range, and then sum the resulting results together.
Operator TRANSP
In detail, how to perform transposition using the array function was discussed above. To summarize, this function makes it possible to create a range in which rows and columns will be swapped. For example, it can be used to create two versions of a table: horizontal and vertical.
Operator MOBR
This function makes it possible to make an inverse matrix. Used when the equation has multiple unknowns.