Excel Shortcut Ctrl + Enter. Very Helpful

Today, I’m going to show you one of the most handy shortcuts around – it won’t show you how to do anything you can’t already do, but it could save you bags of time. The shortcut? Ctrl + Enter.

 

Ctrl + Enter allows you to fill multiple cells with the same content in one go, whether they’re in a single contiguous range or not. It also doesn’t matter what data type the content is, i.e. formula, text, date, etc.

But what’s the problem with Fill Down (Ctrl + D, or by dragging the fill handle (the black square at the bottom right corner of the active cell))? Firstly, it’s an extra step we don’t need to take, but more importantly, its behaviour changes depending on the data type. Let’s start with this simple layout:

01FillDownBefore
Before we start…
01FillDownCopy
Using the Fill Down Method

Many of you might be tempted to drag the fill handle down, which in most cases will copy the cell contents like this:

But if the data type you’re using can be used as part of a series, whether a built in one such as a date or number, or a custom series like I have for European countries, the default fill down will continue the series:

01FillDownSeries
Fill Down with Series doesn’t always work first time
02CtrlEnterBefore
Select the range, and start typing in the active cell

You may have gotten around this before, by typing the item in twice, and then filling down – it’s a useful trick, but I believe Ctrl+Enter is better, and here’s an example of why:

02CtrlEnterAfter
Then hit Ctrl + Enter to fill all cells with the same content

As you can see, I’ve selected by range before typing, and have typed Germany in the first cell. The cursor is still active – I haven’t yet committed that. By hitting Ctrl and Enter together, the following happens:

 

To my mind this is infinitely better. I didn’t waste time dragging to a certain range, and maybe changing the default “Fill Series” option back to “Copy Cells”. Also, I now know these cells match exactly. There can’t be any with extra spaces at the end or differences in spelling from typing errors. The cells are all exactly the same.

 

02CtrlEnterFormulaBefore
Just select the cells and type the formula as it should be for the active cell

Let’s take it a step further – how can I do the same thing with a formula? Well the process is really no different. Now I have data for my sales  to Germany, I want to understand the average value for all the methods of sale. The formula is obviously really simple – turnover/orders. This is just to demonstrate the versatility of the Ctrl+Enter method.

02CtrlEnterFormulaAfter
CTRL and Enter replicates the formula for all cells correctly

So I select my range, and type my first formula: and then hit Ctrl + Enter. In this picture, I’ve cycled through the selection to E5, just so you can see the formula – it’s exactly the same, with the caveat that the formula has changed to reflect the relative cells it’s working with. Perfect for large numbers of formulae.

We can use this with non contiguous ranges to apply the same formula to large numbers of cells:

02CtrlEnterBeforeNonContig
Select non-contiguous ranges with Ctrl First
02CtrlEnterAfterNonContig
Then Ctrl and Enter to apply the same formula everywhere

 

Or we might use this with a locked cell reference or two. In this case, I can determine the proportion of each letter by dividing it by the total. To ensure the same cell is used for the total each time, I pressed F4 to lock the cell, shown by the dollar sign in front of the column letter and row number. More on locked cells in a post to be written!

02CtrlEnterBeforeLockCell
Lock any cells you don’t want to be relative in the normal way
02CtrlEnterAfterLockCell
And it still works!

Try it for yourself – it’s the handiest and most versatile keyboard shortcut there is, and if you get used to it, there are even benefits outside of Excel:

Ctrl+Enter will:
Send an email in Outlook, make a call in Lync and commit text entries and transformation adjustments in Adobe Photoshop and Illustrator.

No ratings yet.

Please rate this