# Using the Shift Key in Excel Array Formulas

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.

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.