What is Array Formula in Excel: Simple Explanation and Examples
array formula in Excel performs multiple calculations on one or more sets of values and returns either a single result or multiple results. It allows you to process ranges of data with one formula instead of writing many separate formulas.How It Works
Think of an array formula as a recipe that can handle many ingredients at once instead of cooking each ingredient separately. Instead of calculating one value at a time, Excel processes a whole group of values together in one step. This means you can add, multiply, or compare many cells in one formula.
When you enter an array formula, Excel treats the input ranges as arrays (lists of values) and applies the formula to each element. The result can be a single value or a set of values that spill into multiple cells. This saves time and keeps your sheet cleaner.
Example
This example shows how to sum the product of two ranges using an array formula.
=SUM(A1:A3*B1:B3)
| A | B |
|---|---|
| 2 | 5 |
| 3 | 6 |
| 4 | 7 |
When to Use
Use array formulas when you want to perform calculations on multiple values at once without writing many formulas. They are great for tasks like summing products, counting with multiple conditions, or extracting unique values.
For example, if you have sales data in two columns and want to find the total sales amount by multiplying quantity and price for each row, an array formula can do this in one step. This makes your spreadsheet faster and easier to manage.
Key Points
- Array formulas process multiple values at once.
- They can return single or multiple results.
- Modern Excel supports dynamic arrays that spill results automatically.
- Use
SUMwith multiplication inside an array formula to sum products. - Press Enter normally in modern Excel; older versions require Ctrl+Shift+Enter.