When to use Ctrl+Shift+Enter and when to use Enter in Excel?

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?

Solution 1:

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