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:
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:
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:
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.
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.
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:
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!
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.