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?
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?
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