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

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.

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.

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.

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.

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.

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.

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