Array Formulas in Excel
This chapter explains array formulas in Excel. With a single-cell array formula, you can complete many calculations in just one cell.
Without Array Formula
You can find the greatest progress without an array formula by following these simple instructions:
1. First, we check the progress for each student one by one.
2. Use the MAX function to find the top progress value.
With Array Formula
The range in column D does not need to be saved. An array constant in Excel is a group of values or a range that Excel remembers and keeps in memory.
1. We can use the formula below to check the first student’s progress.
2. To find the maximum progress, use the MAX function and instead of C2, select the range C2 to C6. Replace B2 with B2 to B6.
3. Press CTRL + SHIFT + ENTER to finish.
Note: When a formula is an array formula, Excel places it inside curly braces {} in the formula bar. Do not type these yourself. These will be removed once you start editing the formula.
Explanation: The array constant is kept inside Excel’s memory, not as a visible range of cells. The array constant looks as follows: {19;33;63;48;13}
The MAX function checks the numbers in this array and gives back 63 as the biggest number.
4. When working in Excel 365 or Excel 2021, complete the action by pressing Enter. Bye-bye curly braces.
F9 Key
When working with array formulas, you can easily check the array constants.
1. Select C2:C6-B2:B6 in the formula.
2. Press F9.
That looks good. The elements of a vertical array constant are separated by semicolons. But for a horizontal array constant, use commas to separate the elements.
1/13 Completed! Want to know array formulas in detail? ➝
Next Chapter: Sort