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! 🙂

36 comments

Your email address will not be published. Required fields are marked *

  • Hi Jon,

    I am using a windows xp, pressing ctrl+enter brings up a dialog box asking me to select to turn on/off scheduler. i am trying to understand how this works. Can you help add some light on this.

    habeeb

  • Hi John

    How to say ! My mind and heart full of happiness.
    I am reading your article first time.
    Full of useful tips. Really very time saving methods.
    Thank you very much.

    Srinivasan K.

  • Hi all,

    The problem is solved 🙂 It was another (newly installed) software where the shortcut CTRL+ENTER has been assigned to. So after closing that software + tasks the CTRL+ENTER key was working again in Excel.

  • Hi all,

    Lately the CTRL+ENTER function doesn’t work in my Excel 2010. If I press both buttons, nothing happens. The selected cells are NOT automatically filled with values. Does anybody know what the reason is? Is there a flag missing (or too much) in the general settings? Thanks for your advice.

  • 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

  • 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.

    • 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!

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

  • 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.

    • 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. 🙂

      • 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 :-$

        • 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! 🙂

  • 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.

  • 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

    • 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! 🙂

      • 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.

  • 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.

  • 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

    • 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!

  • 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

      • 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

JOIN US & LEARN EXCEL

Learn 10 great Excel techniques that will wow your boss and make your co-workers say, "how did you do that??"
Plus weekly updates to help you learn Excel.

Download the eBook

About Me

Jon Acampora Profile

Hello and welcome! My name is Jon Acampora and I'm here to help you learn Excel.

This blog is updated frequently with Excel and VBA tutorials & tools to help improve your Excel skills and save time with your everyday tasks. Subscribe above to stay updated. More about me...

MVP_Horizontal_BlueOnly