Using the Shift Key in Excel Array Formulas - dummies

Using the Shift Key in Excel Array Formulas

By Conrad Carlberg

There’s an important difference between entering a formula by means of the keyboard combination Ctrl+Enter, and by means of Ctrl+Shift+Enter. Both result in an array of results if you begin by selecting a range of cells, but only the formula entered using Ctrl+Shift+Enter is what Excel conventionally terms an “array formula.” Here’s the difference.

When you array-enter a formula, using Ctrl+Shift+Enter, you are entering one formula in multiple cells. Those cells normally display different results, often because your formula uses a function such as LINEST or TRANSPOSE that is designed to return different values in different cells. Or the different results can come about because the formula’s arguments include a range of cells.

It’s a subtle difference, but using Ctrl+Enter results in different formulas in the selected cells, where Ctrl+Shift+Enter results in the same formula in each of the selected cells.

0301_Shift-Key
Each cell in C1:C5 contains the same array formula.

The cells in the range C1:C5 share the same array formula. The results that appear in the individual cells are different because the array formula returns an array of five different values.

The cells in the range C7:C11 have different formulas, although only one was entered via the keyboard. If you selected C7:C11 and typed this formula

=A7

and then entered the formula with Ctrl+Enter, that would have the same effect as if you had entered the formula normally in C7, copied it, and pasted it into C8:C11. That is, the formula adjusted its references to point at A8, A9, A10, and A11. Different cells, different formulas.

Cells C13:C17 were populated the same way as C7:C11, except that the formula typed was:

=A13:A17

Again, Ctrl+Enter was used to fill all five cells in C13:C17. The formula also adjusted its references as shown in E13:E17. The results employ what Excel calls the implicit intersection. That is, the location of the formula implies the location of the intersection with an array of cells.

So, C13 points at A13:A17 and C13’s row intersects A13:A17 at A13. The formula in C13 returns the value found at that intersection, 1. Similarly. The formula in C17 points at A17:A21. It intersects A17:A21 at A17 and the implicit intersection causes the formula to return the value in the intersection, 5.