Video array formulas

Try it!Transcript



Create array formulas, often called Ctrl+Shift+Enter or CSE formulas, to perform calculations that generate single or multiple results. Watch this video to learn more.

Why use array formulas?

Array formulas are often referred to as CSE (Ctrl+Shift+Enter) formulas because instead of just pressing Enter, you press Ctrl+Shift+Enter to complete the formula.

If you have an experience using formulas in Excel, you know that you can perform some fairly sophisticated operations. For example, you can calculate the total cost of a loan over any given number of years. You can use array formulas to do complex tasks, such as:

• Count the number of characters that are contained in a range of cells.

• Sum only numbers that meet certain conditions, such as the lowest values in a range or numbers that fall between an upper and lower boundary.

• Sum every nth value in a range of values.

Enter an array formula

1. Select the cells where you want to see your results.

2. Enter your formula.

3. Press Ctrl+Shift+Enter. Excel fills each of the cells you selected with the result.

Want more?

Guidelines and examples of array formulas

Create an array formula

Rules for changing array formulas

You want to calculate the total of a large range of cells, such as the value of these Stocks.

You could type = sign, B2 (the number of Contoso shares), asterisk to multiply, B3, the share price, the + sign to add, C2, asterisk, C3, and so on.

But it would take forever with a large series, and it would be easy to make a mistake, and not notice it.

An easier way? Use an array formula. An array is a series of data in a row, column, or a combination of rows and columns.

You have probably used them before. B2:F2 is an array, also commonly referred to as a range of cells.

An array formula performs calculations on the data in one or more arrays, returning either a single or multiple results.

With an array formula, you type = sign, SUM, opening parenthesis, select the cells in the Shares row (this is the first array in the array formula), then an asterisk to multiply, select the cells in the Price row (this is the second array in the array formula).

And this is the key difference when you enter an array formula, you press Ctrl+Shift+Enter, not just Enter.

If you press just Enter, you'll get either an incorrect value, if the regular function is valid, or a value error, if it is not.

I didn't type a closing parenthesis in the formula, pressing Ctrl+Shift+Enter does this for you.

The array formula multiplies each cell in the Shares row by the respective cell in the Price row and then sums, or adds these and returns the total of $14,421.87.

In the Formula Bar, the formula is enclosed in braces, { }, meaning that it is an array formula.

This is automatically done when you press Ctrl+Shift+Enter. Don't type these braces. If you do, Excel will interpret your formula as text, not as a formula.

Up next: Use SUM, AVERAGE, and MAX in array formulas.