MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Excel Shortcuts - Ctrl+Enter

July 31, 2017 - by Bill Jelen

Excel Shortcuts - Ctrl+Enter

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

Auto-Generated Transcript

  • Learn Excel from MrExcel Podcast
  • episode 2120 to copy a formula into
  • the entire selection merit so you know
  • most of the time if we're building a
  • little formula here profit divided by
  • revenue when you 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 Excel the 40 greatest
  • excel tips of all time that I've seen
  • the people who can do these five steps
  • amazingly fast right they don't even
  • copy they just go left ctrl down arrow
  • right ctrl shift up arrow and then ctrl
  • D ctrl D is the thing to say take the
  • top thing and copy it down alright and
  • they do those five 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 equals 2 times
  • column and you need to copy that right
  • so ctrl R will copy right ctrl D control
  • are both cool keyboard shortcuts but one
  • thing that's really cool here is if we
  • know that the GP percent has to go into
  • all of those cells right there we can
  • select that whole range and then do
  • equal the cell divided by that cell and
  • press ctrl enter like that and it will
  • fill the whole range the other thing
  • 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
  • ctrl 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 equal ran between ran between
  • 1000 comma 2000 ctrl enter and we now
  • have a fake data set way thanks for
  • stopping by see you next time for
  • another net cast from MrExcel

Title Photo: Pexels / Pixabay

Bill Jelen is the author / co-author of
Excel Subtotals Straight to the Point

I used to use the Subtotals feature daily after downloading mainframe data. This book covers every tip and trick for using Subtotals.