15

I don't understand when I should use Ctrl+Shift+Enter and when to just use Enter in Excel.

Can someone help me understand the difference between them and how they should be used?

  • What do you think the difference between the two is? – Der Hochstapler Nov 11 '13 at 20:29
  • Hmmm... @DerHochstapler Thanks for that helpful comment! – FreeSoftwareServers Nov 20 '19 at 4:15
  • @FreeSoftwareServers You're welcome – Der Hochstapler Nov 20 '19 at 9:22
19

For single cell formulas

There's no completely consistent rule.

In general when you use ranges in formulas or functions that normally only apply to single cells then you need CTRL+SHIFT+ENTER.....so if you use

=IF(A1=3,1,0)

that's a straightforward formula which can be normally entered......but if you change it to

=SUM(IF(A1:A10=3,1,0))

.....then you now need CTRL+SHIFT+ENTER

....but some functions can process ranges or arrays without CSE, e.g. SUMPRODUCT and LOOKUP......and some functions always need CSE, e.g. TRANSPOSE

...and to confuse you further, typically if you use an "array constant" in place of an array, e.g.

=SUM(IF({1,2,3,4,5,6,7,8,9,10}=3,1,0))

...that doesn't need CSE.......and there are ways to make CSE formulas into non-CSE, e.g., this needs CSE

=INDEX(B2:B10,MATCH(1,(A2:A10="x")*(C2:C10="y"),0))

but this avoids CSE to do the same thing with an extra INDEX function

=INDEX(B2:B10,MATCH(1,INDEX((A2:A10="x")*(C2:C10="y"),0),0))

for multi-cell formulas

these are formulas that return an "array" of values, e.g. this formula returns as "array" of 21 values

=FREQUENCY(A1:A100,B1:B20)

so you can select a range of 21 cells to display all those values - such formulas need to be confirmed with CTRL+SHIFT+ENTER after the entry range has been selected

  • 3
    Also, (Ctrl)+(Enter) lets you enter the same simple (single-cell) formula in multiple cells (all the selected cells) simultaneously. – Scott Nov 12 '13 at 1:28
  • Simple... Right! – FreeSoftwareServers Nov 20 '19 at 4:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.