Basic Array Formula Example: Excel & Google Sheets

This article explains what an array formula is and walks through a basic example you can use in Excel and Google Sheets. You enter a range of cells instead of a single cell, and the formula performs the calculation on every value in that range at once. By the end, you will know how to write, enter, and troubleshoot your first array formula.

Formula

The most basic array formula sums or multiplies a range of cells without writing a formula for each cell. In spreadsheet notation, the formula looks like this:

=SUM(A1:A5)

This is an array formula because it operates on the array A1:A5 (five cells) and returns a single result. In older Excel versions, you would press Ctrl+Shift+Enter to confirm it, which wrapped it in curly braces like {=SUM(A1:A5)}. In modern Excel (365 and 2021) and Google Sheets, you just press Enter, and the formula automatically handles the array.

Every array formula has three parts:

• Input array: the range of cells or values you feed in (e.g., A1:A5).
• Operation: what you do to each element (e.g., multiply by 2, sum, count).
• Output: either a single value (scalar) or a range of values (spilled array).

The calculator above uses a comma-separated list as the input array, sums it, counts it, and averages it—mirroring what a basic array formula does in a spreadsheet.

Worked Example

Using the default values from the calculator: 1, 2, 3, 4, 5.

1. Sum: 1 + 2 + 3 + 4 + 5 = 15
2. Count: there are 5 numbers, so 5
3. Average: 15 ÷ 5 = 3

The calculator shows Sum = 15, Count = 5, Average = 3. That matches exactly.

In a real spreadsheet, if you put those numbers in cells A1 through A5, the formula =SUM(A1:A5) returns 15. The array formula does the same thing but can also do more complex operations, like multiplying each cell by a constant before summing: =SUM(A1:A5*2) would give 30 (because 2+4+6+8+10 = 30).

How Array Formulas Work

Array formulas process each cell in the range individually, then combine the results. Think of it like a loop: for each cell, perform the operation, store the result, then move to the next cell.

For example, =A1:A5*2 returns a new array of five values: {2, 4, 6, 8, 10}. If you wrap it in SUM(), it adds those five results together.

In modern Excel, this is called dynamic array behavior. When you type =A1:A5*2 in a cell, Excel automatically “spills” the results into the cells below, filling five cells. Google Sheets does the same but requires you to press Ctrl+Shift+Enter for some array operations (though most simple ones work with Enter).

Key point: the formula evaluates every element, not just the first one. That is what makes it an array formula.

When to Use a Basic Array Formula

Use an array formula when you need to perform a calculation on a whole range and either:

• You want a single summary result (like total sales, average score).
• You need to apply a condition to each cell before aggregating (e.g., sum only values greater than 10).
• You want to avoid helper columns (extra columns that hold intermediate results).

Common real-world examples:

• Sum of all sales where the region is “East”: =SUM((A1:A100="East")*(B1:B100))
• Count of cells that contain a number above 50: =COUNTIF(A1:A100,">50") (not an array formula, but often replaced by one)
• Multiply two columns element-wise and sum: =SUMPRODUCT(A1:A10, B1:B10) (a built-in array-like function)

If your task can be done with a regular function like SUMIF or COUNTIF, prefer those—they are faster and easier to read. Array formulas shine when no built-in function exists for your exact need.

Common Mistakes and How to Avoid Them

Array formulas are powerful but easy to get wrong. Here are the most frequent errors:

• Forgetting to press Ctrl+Shift+Enter in older Excel. If you just press Enter, you get a #VALUE! error or the wrong result. In Excel 365/2021, this is no longer needed, but if you are using Excel 2019 or earlier, always use CSE (Ctrl+Shift+Enter).
• Mismatched range sizes. If you multiply two arrays of different sizes, you get #N/A or #VALUE!. For example, =A1:A5*B1:B4 fails because the arrays are not the same length.
• Using array formulas on entire columns. =SUM(A:A*2) works but can slow down your sheet because it processes millions of cells. Limit the range to your actual data.
• Editing a spilled array. In dynamic Excel, you cannot edit a cell inside a spilled range. You must edit the original formula cell.
• Mixing text and numbers. Multiplying text by a number gives #VALUE!. Ensure your range contains only numeric values.

If you see #SPILL! in Excel, it means the formula wants to return more cells than are available (something is blocking the spill range). Clear the obstructing cells.

Array Formulas vs. Regular Formulas

Regular formulas work on a single cell or a fixed set of arguments. Array formulas work on a range and can return multiple results.

Example: =A1*2 is regular. =A1:A5*2 is an array formula.

Here is a quick comparison:

• Regular formula: one input cell, one output cell. Easy to debug.
• Array formula: many input cells, one or many output cells. More powerful but harder to trace.
• Performance: array formulas can be slower because they process every cell. Use them sparingly on large datasets.
• Compatibility: older Excel versions require CSE entry; newer versions and Google Sheets handle them more naturally.

For most users, built-in functions like SUMIFS, COUNTIFS, and SUMPRODUCT cover 90% of what array formulas do, without the complexity.

Alternatives to Array Formulas

If array formulas feel intimidating, there are simpler alternatives:

• Helper columns: add a column that computes each row’s value, then sum that column. Easy to understand but adds clutter.
• SUMPRODUCT: handles array-like calculations without CSE. For example, =SUMPRODUCT((A1:A10>5)*B1:B10) sums B where A is greater than 5.
• SUMIFS/COUNTIFS: for conditional sums and counts, these are faster and clearer.
• LET and LAMBDA (Excel 365): let you define variables and custom functions, making complex logic more readable.

Choose the simplest tool that works. Array formulas are not always the best choice—they are just one option.

Frequently Asked Questions

Do I need to press Ctrl+Shift+Enter for array formulas in Excel?

In Excel 365 and Excel 2021, no—just press Enter. In Excel 2019 and earlier, yes, you must press Ctrl+Shift+Enter to tell Excel it is an array formula. If you forget, you will get a #VALUE! error or the wrong result.

What does the curly brace {} mean in an array formula?

The curly braces are added by Excel when you press Ctrl+Shift+Enter. They indicate that the formula is an array formula. You cannot type them manually—they appear automatically. In modern Excel, they are not shown because dynamic arrays are the default.

Can array formulas return multiple values?

Yes. For example, =A1:A5*2 returns five values. In Excel 365, they spill into adjacent cells automatically. In older Excel, you had to select the output range, type the formula, and press Ctrl+Shift+Enter to fill all selected cells.

Why is my array formula returning #VALUE!?

This usually means a cell in the range contains text, or the ranges are not the same size. Check that all cells are numeric and that each array has the same number of rows and columns. Also ensure you pressed Ctrl+Shift+Enter if using older Excel.

Are array formulas slower than regular formulas?

Generally, yes, because they process every cell in the range. On small datasets (a few hundred rows), the difference is negligible. On large datasets (tens of thousands of rows), they can noticeably slow down your spreadsheet. Prefer built-in functions like SUMIFS or SUMPRODUCT when possible.

Can I use array formulas in Google Sheets?

Yes. Google Sheets supports array formulas, and you can enable them with ARRAYFORMULA() or by pressing Ctrl+Shift+Enter. For example, =ARRAYFORMULA(SUM(A1:A5*2)) works. Many simple array operations work without ARRAYFORMULA, but for complex ones, wrap the formula in ARRAYFORMULA().

Related

• SCP Commands Examples for File Transfer
• Thanksgiving Tech Tips for Easier Visits
• Raspberry Pi Pico vs Zero: Key Differences

in values | Values (comma separated) | 1, 2, 3, 4, 5 | 0 | 100000
out total | Sum of values | - | sum(values) | 2
out count | Count of values | - | count(values) | 0
out average | Average | - | mean(values) | 2