2 Ways to Use the Ctrl+Enter Keyboard Shortcut in Excel - Excel Campus
31

2 Ways to Use the Ctrl+Enter Keyboard Shortcut in Excel

Bottom line: Learn a few quick ways to use the Ctrl+Enter keyboard shortcut to save time with entering data or formulas.

Skill level: Beginner

Time Saving Tips for Entering Formulas or Data in Excel with Ctrl Enter

I had a few questions come in about the Ctrl+Enter keyboard shortcut I used in last week’s videos on the Progress Doughnut Chart.  I mentioned this tip (#2) in my post on 17 Excel shortcuts for 2017, but thought I should explain it in more detail.  Ctrl+Enter is a shortcut I use all the time when entering data or formulas.

#1 – Ctrl+Enter to Stay on the Active Cell

When editing a cell, pressing the Enter key will select the cell below the cell you are currently working in.  This is the default behavior in Excel, and it allows us to quickly work our way down a list when entering data into cells.

Enter vs Ctrl Enter Excel Keyboard Shortcuts

If we hold the Ctrl key while pressing Enter, the selection will NOT move to the next cell.  Instead, the cell that we just edited will remain selected.  The cell we are editing is referred to as the active cell.  So, Ctrl+Enter keeps the selection on the active cell after entering data or a formula.

When is it useful?

There are a lot of uses for this shortcut.  In last week’s video I was entering the percentage completion number in the cell.  I wanted to keep that cell selected so I could type a different number in it to see the change in the chart.  I didn’t want to have to hit Enter, then up arrow back to the cell.  Using Ctrl+Enter allowed me to keep the active cell selected and modify the cell again.

Ctrl Enter to keep cell selected on progress doughnut chart example

We might also want to keep the selection on the active cell so we can take the next step to apply formatting, fill down, fill across, adjust row height, or copy the cell.

This saves time from having to re-select the cell with the mouse or up arrow key.  It might not seem like much, but think about how many times you have edited a cell and then wanted to make additional formatting changes or copy the cell.  “If I had a nickle for every time I edited a cell…” 🙂

Change the Selection Direction After Pressing Enter

We can also control which cell is selected next after pressing the Enter key, or turn it off. This is a setting within Excel on the Excel Options menu.

Excel Options for After pressing Enter move selection direction

To change the Direction After Pressing Enter setting:

  1. Go to File > Options.
  2. Click “Advanced” in the left side menu.
  3. The first option is “After pressing Enter, move selection”.
  4. We can choose the direction from the drop-down menu (Down, Right, Up, Left)
  5. We can also uncheck the checkbox to turn the feature off.  This will keep the active cell selected after pressing enter.

If you don’t do much data entry, you might want to try turning the feature off and see how it works for you.  You can still use the up and down arrow keys to select cells.  This is totally a personal preference.  It’s just good to know the option is available.

#2 – Ctrl+Enter to Fill All Selected Cells with the Same Data or Formula

When we are entering data or a formula in a cell, and have multiple cells selected, Ctrl+Enter will copy the data/formula to all of the selected cells.

Copy Formula to All Selected Cells Ctrl Enter Shortcut Excel

In the screencast above I use Ctrl+Enter to fill all the selected cells with random numbers using the RANDBETWEEN function.  This saves time from having to copy/paste or fill down and across.

Here are the steps to perform the Ctrl+Enter shortcut on multiple cells:

  1. Select a range of cells.  It does NOT have to be a contiguous range.
  2. Type data or a formula in the active cell.
  3. Hold down the Ctrl key and press the Enter key.
  4. The data/formula is copied to all selected cells.

When is this useful?

This can save time when we want to enter the same data or formulas in multiple cells, especially if they are NOT in the same contiguous range.

If you just want to fill data or formulas down a column, then we can use the fill handle or copy/paste.  However, if the cells that are NOT in the same row or column, then Ctrl+Enter can be a real time saver.

Select and Modify Non-Contiguous Cells with Go To Special

We can also use the Go To Special menu to select specific cells on the sheet like: blanks, numbers, errors, formulas, data validation, visible cells.  The Go To Special menu helps us select all the cells of the same type.

Select Non-Contiguous Cells of the Same Type with the Go To Special Menu

The Go To Special Menu can be opened by pressing F5 on the keyboard, (Ctrl+G on the Mac)  then pressing the Special… button in the bottom-left corner.

Once we have the cells selected, we can then edit the active cell and press Ctrl+Enter to copy the data/formula to all selected cells.  The screencast below shows this in action.

Use Go To Special & Ctrl+Enter to Fill Blank Cells

Another good example of this technique is filling down values in non-contiguous ranges.  In the screen cast below I have a report with region names in column A.  The region names need to be filled down to the blank cells below in each area.

We can use the Go To Special menu to first select all the blank cells in column A.  Then we can input a simple formula to reference the cell above, and press Ctrl+Enter to fill the formula to all blank cells.  This fills all the blank cells with the value above.

Go To Special and Ctrl Enter to Fill Down Blank Cells on Non Contiguous Range

This is much faster than copying down each area manually, especially if you have hundreds or thousands of rows of data.

Check out my article on how to copy & paste visible cells only for another example of how to use the Go To Special menu.

How do You use the Ctrl+Enter shortcut?

I hope those tips save a little time out of your busy day.  How do you use Ctrl+Enter?  Please leave a comment below with any suggestions, tips, or questions.

Thank you! 🙂

Please share
Jon Acampora
 

Welcome to Excel Campus! I am excited you are here. My name is Jon and my goal is to help you learn Excel to save time with your job and advance in your career. I've been an avid Excel user and VBA developer for 10+ years. I am also a Microsoft MVP. I try to learn something new everyday, and want to share this knowledge with you to help you improve your skills. When I'm not looking at spreadsheets, I get outdoors and surf. :) more about me

Click Here to Leave a Comment Below 31 comments
Hao - April 16, 2017

Hi Jon

Thank you for your great article.
I am also interested in the gif you made to make it more easy to understand.
I know you made the gif from Camtasia. But I wonder how to make the red progress bar below the gif. This bar is useful because I know when a gif image ends. Could you share with me the method to acquire this red bar?
I am sorry if my comment is not related to the topic.

Thank you so much.
Hao

Reply
Tony - April 5, 2017

I’m learning a lot from ways to use Ctrl-Enter – but I’ve also found another shortcut which is helpful.

Default for Enter is to move down a row.
Ctrl-Enter stays in the same cell/row.
Shift-Enter moves up a row.

Add to this the left/right arrows or Tab / Shirt-Tab to go right and left and the only thing I haven’t found is how to go diagonally LOL

Thanks as always, Jon.

Tony.

Reply
Paul - April 2, 2017

The question is can I USE the
Control + enter to copy that
Cell to 25 other work sheets
to the same location???

Reply
    Jon Acampora - April 10, 2017

    Hi Paul,
    Yes, you can. You will need to select the other sheets first by holding the Shift or Ctrl keys to select multiple sheets.

    Reply
Fausto Torricelli - April 2, 2017

I found useful the paragraph about filling the non-contigous cells with data /formulas

Reply
    Jon Acampora - April 5, 2017

    Awesome! Thanks Fausto! We can also use that same method with the Go To Special menu to replace cells with blanks, errors, formulas, numbers, text, etc.

    Once the cells are selected, you can replace them all with a formula or value using Ctrl+Enter.

    I think it’s one of those techniques that we might not use every day, but it sure comes in handy when we need it.

    Thanks again!

    Reply
AQUIB JAVED - April 1, 2017

I am highly thankful to you . Really Ctrl+ Enter key is a time saver key which I came to know just because of you.

Reply
Dubes - March 31, 2017

Thanks, very helpful.

Reply
Peter Buyze - March 31, 2017

Ctrl+Enter to Stay on the Active Cell – there is an easier way to stay on an active cell:

File > Options > Advanced, in the section “Editing options” uncheck the 1st line there which reads “After pressing Enter, move selection.

I have been in the unchecked mode now for at least a year & I must say it is so much easier to work like that. Anyone using Excel regularly, esp. power users, should have that box unchecked.

Reply
    Jon Acampora - March 31, 2017

    Hi Peter,
    Thanks for the suggestion! Yes, I mention that in the article above in the section titled “Change the Selection Direction After Pressing Enter”. I agree that this can be faster. It just takes some getting used to. 🙂

    Reply
      Peter Buyze - March 31, 2017

      My apologies Jon, I missed that, but I should have known better than to try to give an Excel expert like you a new tip. With all that egg on my face I wish a good day :-$

      Reply
        Jon Acampora - April 1, 2017

        Hi Peter,
        No problem at all! When I read your comment I imagined a kid in a classroom that raises his hand before the teacher finishes asking the question. I was that kid sometimes… 🙂

        I love your enthusiasm for learning Excel, and I encourage you to keep sharing your own tips. I do NOT know everything, not even close, and I learn a lot from everyone in the Excel Campus community.

        There were quite a few replies from other readers that turn off the “After pressing Enter” setting. Seems to be a popular choice. Thanks again for all your support Peter! 🙂

        Reply
Jesse - March 31, 2017

Great time savers Jon, this will enable me to have excel do a lot of my tedious work I’d rather somebody else did, thanks again.

Reply
Lisa - March 31, 2017

Wow! That is so neat! Thank you, I love learning something new every week from your Blog! I already know where to use this feature!! When using the corner box to copy down, it always changes by adding a year or day to the numbers, this will be so great to select and enter all cells at one time!! Even worked when I filtered out the cells with data, and only changed the blank cells!

I am sure you can tell I am excited about learning this. Again, Thank you. Lisa

Reply
    Jon Acampora - March 31, 2017

    Woohoo! Thank you Lisa! I’m so excited to hear that you are already making use of Ctrl+Enter.

    I have another tip that you might like. Sometimes it does make more sense to use the fill handle (corner box) to copy (fill) down the formulas. Like you said, when the value is a date, Excel will fill down with a Series and increment the dates. If you want to copy the date instead, you can press Ctrl+D after double-clicking the fill handle. Here is a screencast that shows it in action. I will do another post with some tips on the Fill Handle.

    How to Copy Down with the Fill Handle in Excel

    Please let me know if you have any questions. Thanks again and have a nice weekend! 🙂

    Reply
      Lisa - April 3, 2017

      Jon, Thank you for another alternative. I figured there had to be a shortcut to change from incremental to same. CTRL-D will come in handy as well.

      I learned on Lotus 123 before we had a mouse to help, so I learned a lot of the shortcuts this way, and still would prefer to use them instead of the mouse.

      Reply
Asela - March 31, 2017

Thanks, Now I understood

Reply
Barbara Sambol-Curran - March 31, 2017

Thanks Jon for your easy-to-understand instructions. I pre-date the internet and even my dinosaur brain is learning its way around Excel with a lot of help and guidance from you.

Reply
Caleb - March 31, 2017

Hi Jon,

I love Ctrl+Enter. I use it a lot when I have to fill blank cells. I just learned about Go To Special yesterday- that plus Ctrl+Enter is magic! It saves a ton of time.

Thank you for all the tips. Today you taught me the RANDBETWEEN formula and how to fill non-contiguous range with data. Totally awesome- you have changed my work life the past few months.

–Caleb

Reply
    Jon Acampora - March 31, 2017

    Hi Caleb,
    Thank you for the nice feedback. I’m happy to hear you are learning more about Excel and enjoying the tips.

    RANDBETWEEN is great for filling whole numbers and the RAND function can be used for decimal numbers between 0 and 1.

    Thanks again!

    Reply
Mark - March 31, 2017

Thank you so much for the Ctrl+Enter tips!

Reply
Phylipe Schad - March 31, 2017

Very good tips!

Reply
Tony - March 31, 2017

Hi Jon, Glad to hear that you and your family are doing well. It`s challenging but very rewarding to have a new addition to the family. I should know as I have 4 daughters ranging between 35 years down to 13 years of age!!

Anyway Jon I have a question for you can a range of formulas be copied from one spreadsheet to another all in one go or does this have to be done individually?

Thanks in advance,
Tony

Reply
    Jon Acampora - March 31, 2017

    Hi Tony,
    Thank you for the warm wishes. I’m not sure I understand your question. Yes, you can copy a range of cells that contains formulas between spreadsheets.

    Reply
      Tony - April 11, 2017

      Hi Jon,
      PLEASE can you help?
      How do I NOT count the value of a colored cell in a range of data?
      There are many tips on how to count up different colored cells however there are none showing how NOT to count.

      Any help would solve my problems,

      Thanks in advance,
      regards,
      Tony

      Reply
Fred Sackey - March 31, 2017

What else don’t I know?

Reply
    Jon Acampora - March 31, 2017

    Haha! I ask myself the same question every day. I think that’s the fun part about Excel. There is always something new for us to learn.

    Reply
Sue Weber - March 31, 2017

Hi Jon
The “fill non-contiguous range” is truly awesome!!
Thank you so much
Sue

Reply

Leave a Reply:

Take Your Excel Skills & Career to the Next Level

10 Excel Pro Tips eBook

Get my eBook & FREE weekly updates to help you learn Excel.

x