Enter Array Formula: Excel CSE Guide

An array formula in Excel is a formula that performs multiple calculations on one or more items in an array, returning either a single result or multiple results. You enter it by pressing Ctrl+Shift+Enter (CSE) in classic Excel, which wraps the formula in curly braces {}. This guide explains how to enter array formulas correctly, when to use them, and how they differ from dynamic array formulas in modern Excel.

Formula

The classic array formula syntax is:

{=function(array1, array2, ...)}

The curly braces are added automatically by Excel when you press Ctrl+Shift+Enter, not typed manually. For example, to multiply two ranges element-wise and sum the result, you use {=SUM(A1:A10*B1:B10)}. Here, A1:A10 and B1:B10 are arrays, and the multiplication creates an array of products that SUM adds up.

Worked Example

Suppose you have sales quantities in A1:A5 (10, 20, 30, 40, 50) and unit prices in B1:B5 (5, 4, 3, 2, 1). To get the total revenue:

1. Select a cell, e.g., C1.
2. Type =SUM(A1:A5*B1:B5).
3. Press Ctrl+Shift+Enter instead of just Enter.
4. Excel displays {=SUM(A1:A5*B1:B5)} in the formula bar.

The calculation: 10*5 + 20*4 + 30*3 + 40*2 + 50*1 = 50 + 80 + 90 + 80 + 50 = 350. The result is 350.

How to Enter an Array Formula

• Single-cell array formula: Select one cell, type the formula, press Ctrl+Shift+Enter. The result appears in that cell.
• Multi-cell array formula: Select a range of cells (e.g., D1:D5), type the formula that returns multiple values (e.g., =A1:A5*B1:B5), press Ctrl+Shift+Enter. Each cell shows one result.
• Editing: To modify an array formula, select the entire range (for multi-cell), press F2, edit, then press Ctrl+Shift+Enter again. You cannot edit just one cell of a multi-cell array.

Dynamic Arrays vs. Classic CSE

Excel 365 and Excel 2021 use dynamic arrays. You enter a formula normally with Enter, and results “spillâ€� into adjacent cells automatically. For example, =A1:A5*B1:B5 entered with Enter in a single cell returns five values in a spill range. The old Ctrl+Shift+Enter is no longer required for most array operations in these versions. However, classic CSE still works for backward compatibility. If you open a legacy workbook, the curly braces remain, but you can often remove them and press Enter.

Common Problems and Mistakes

• Forgetting Ctrl+Shift+Enter: In older Excel, pressing Enter alone gives a #VALUE! error or a wrong result.
• Typing braces manually: Typing { } yourself treats them as text, not an array formula.
• Editing part of a multi-cell array: Excel shows “You cannot change part of an arrayâ€� – you must select the whole range.
• Using array formulas with whole columns: =SUM(A:A*B:B) can be slow; limit ranges to actual data.
• Mixing incompatible array sizes: Arrays must have the same number of rows/columns, or you get #N/A.

When to Use Array Formulas

Use array formulas when you need to perform calculations that would otherwise require helper columns or complex nested functions. Common use cases:

• Summing products of two ranges (SUMPRODUCT is an alternative).
• Conditional sums or counts with multiple criteria (e.g., =SUM(IF((A1:A10>5)*(B1:B10<10),C1:C10,0))).
• Performing operations on every element of a range (e.g., =MIN(IF(A1:A10>0,A1:A10)) to find the smallest positive number).
• Transposing data with =TRANSPOSE(A1:C3) (in older Excel, this requires CSE).

In modern Excel, many of these are replaced by functions like FILTER, UNIQUE, and SEQUENCE, which are easier and more readable.

Alternatives to Array Formulas

If you are using a version that supports them, consider these alternatives:

• SUMPRODUCT: For summing products or conditional sums, it works without CSE.
• Helper columns: Break complex calculations into steps, improving clarity.
• Dynamic array functions: FILTER, SORT, UNIQUE, SEQUENCE, and RANDARRAY in Excel 365.
• Power Query: For data transformation, especially with large datasets.

Frequently Asked Questions

Why does my array formula show #VALUE!?

This usually means you pressed Enter instead of Ctrl+Shift+Enter, or the arrays have mismatched dimensions. Check that the formula is enclosed in curly braces in the formula bar. If not, select the cell, press F2, then Ctrl+Shift+Enter.

Can I use array formulas in Excel Online or Google Sheets?

Excel Online supports dynamic arrays, so you don’t need CSE. Google Sheets uses ARRAYFORMULA() wrapper instead of Ctrl+Shift+Enter. For example, =ARRAYFORMULA(SUM(A1:A10*B1:B10)).

How do I delete an array formula?

Select the entire range that contains the array (for multi-cell), then press Delete. For a single-cell array, just select the cell and press Delete. You cannot delete just one cell of a multi-cell array.

What does the @ symbol mean in array formulas?

The @ symbol in Excel 365 indicates implicit intersection. It forces a formula to return a single value, ignoring the array behavior. You may see it automatically inserted when opening legacy formulas.

Are array formulas faster than regular formulas?

Array formulas can be faster because they avoid intermediate calculations, but they can also be slower if used over huge ranges. In modern Excel, dynamic arrays are optimized, but it’s still best to limit ranges to the actual data.

Related

• How to Turn Off Zoom on Apple Watch
• MW4 Revenge Trophy Guide: Assassin’s Achievement
• Salesforce vs Zendesk: Which CRM Fits?

in t | Finish time | min | 50 | 0
in d | Distance | km | 10 | 0 | 100000
out pace | Average pace | min/km | t / d | 2
out speed | Average speed | km/h | d / (t / 60) | 2