Excel Shortcuts - Ctrl+Enter
July 31, 2017 - by Bill Jelen
Today's article in Excel Shortcuts talks about using Ctrl+Enter after you finish typing a formula. Not Ctrl + Shift + Enter (array formulas), but just a straight Ctrl + Enter.
Ken Puls, who is the king of Power Query, says, “You would think my favorite Excel tip would be Unpivot with Power Query, but my favorite all-time is Ctrl + Enter.” Say that you want to enter a formula into 400 cells. Select the 400 cells. Type the formula in the first cell. Press Ctrl + Enter and Excel enters a similar formula in all cells of the selection.
Gavin White points out another use. You enter a formula in G2. You need to copy the formula down but not the formatting. Select G2:G20. Press the F2 key to put the cell in Edit mode. When you press Ctrl + Enter, the formula is copied but not any formatting. Thanks to Crystal Long, Schmuel Oluwa, Ken Puls, Peter Raiff, Sven Simon, Gavin Whyte.
Watch Video
Video Transcript
Learn Excel From MrExcel, Podcast Episode 2120: CONTROL+ENTER To Copy A Formula Into The Entire Selection.
Alright. So, you know, most of the time, if we're building a little formula here, PROFIT ÷ REVENUE, we want to copy this down, well, most people would double click like that, alright, or there's the keyboard shortcut, people, and I talked about this in my book MrExcel XL: The 40 Greatest Excel Tips Of All Time, that I've seen the people who can do these 5 steps amazingly fast, right? They don't even copy. They just go LEFT, CONTROL+DOWNARROW, RIGHT, CONTROL+SHIFT+UPARROW, and then CONTROL+D. CONTROL+D is the thing to say take the top thing and copy it down, alright, and they do those 5 steps faster than I can double click over here.
It's really, really amazing, and, by the way, if you had a simple little formula, =2*COLUMN, and you need to copy that right, so CONTROL+R will copy right. CONTROL+D, CONTROL+R, both cool keyboard shortcuts, but one thing that's really cool here is if we know that the GP% has to go into all of those cells right there, we can select that whole range and then do = this cell divided by that cell, and press CONTROL+ENTER, like that, and it will fill the whole range.
The other thing that this is great for is if you have a formula that has some formatting, so we have top borders there, see? That's bad. We don't want to copy that top border. So, the formula is already there. I'm going to select the range where I want it to paste into, press F2 to edit, and then CONTROL+ENTER will copy that formula down but it won't copy the top border. The top border. Why did they use the top border instead of the bottom border? I don't know, or if we just need to fill this with fake data, select the whole range here, and =RANDBETWEEN, RANDBETWEEN, a 1000 , 2000, CONTROL+ENTER, and we now have a fake data set. [=RANDBETWEEN(1000,2000)]
Well, hey. Thanks for stopping by. We’ll see you next time for another netcast from MrExcel.
Title Photo: Pexels / Pixabay
