Ctrl+Enter in Excel: Fill Many Cells at Once

Ctrl+Enter in Excel enters what you type into every selected cell at once. Select a range, type a value or a formula, press Ctrl+Enter (Mac: Control+Return), and the whole range is filled in one step, with no copying and no dragging. This guide shows how it works with values and formulas, how to use it on scattered cells and blanks, and how it compares with Fill Down.

What does Ctrl+Enter do in Excel?#

Ctrl+Enter confirms an entry in all the selected cells instead of just the active cell. The active cell is the white one in a selection, where your typing appears.

It has two uses:

• Several cells selected: the entry goes into every one of them.
• One cell selected: the entry is confirmed and the same cell stays selected. Plain Enter would move down. That's handy when you want to type a formula and then look at the result, or copy it straight away.

Don't mix up Ctrl+Enter with Alt+Enter, which starts a new line inside a cell (Mac: Option+Return).

How to fill many cells at once with Ctrl+Enter#

Here's how to put the word "Pending" into a block of cells:

1. Select the cells, for example D2:D20. Drag with the mouse, or click the first cell and use Shift+arrow keys.
2. Type Pending. It appears only in the active cell for now.
3. Press Ctrl+Enter (Mac: Control+Return).

Every cell in D2:D20 now says Pending. The selection stays in place, so you can see what changed. The same works for numbers, dates and formulas. If you press Enter by mistake, only the first cell is filled: press Ctrl+Z (Mac: ⌘Z), select the range again and retry.

Ctrl+Enter with formulas: relative and absolute references#

When you enter a formula with Ctrl+Enter, Excel writes it into the active cell and then adjusts the relative references for every other cell, exactly as if you had filled it down. Take this sales table, with a bonus rate of 5% in F1:

1. Select C2:C7, with C2 as the active cell.
2. Type =B2*$F$1.
3. Press Ctrl+Enter.

C2 gets =B2*$F$1 and returns 420. C3 gets =B3*$F$1 (310), C4 gets =B4*$F$1 (455), and so on. B2 is relative, so it moves down one row per cell. $F$1 is absolute: the dollar signs lock it, so every row uses the same rate. Without them, C3 would point at F2, which is empty, and return 0.

Always write the formula for the active cell, the one where your typing appears. If the active cell is C7 because you selected from the bottom up, write =B7*$F$1. The guide on applying a formula to a whole column compares Ctrl+Enter with the fill handle and Excel Tables.

Fill non-adjacent cells and blank cells#

Ctrl+Enter doesn't need a neat block. Hold Ctrl (Mac: ⌘) and click scattered cells to build a selection, type the entry, and press Ctrl+Enter. Every selected cell gets it, which is a quick way to mark several rows "N/A" or reset a few scores to 0.

Its most famous use is filling blanks. In a report where each group name appears only once, with blank cells below it:

1. Select the column.
2. Press F5 (Mac: Control+G), click Special, choose Blanks and click OK. Only the empty cells are now selected.
3. Type = and press the up arrow, which points the first blank at the cell above it.
4. Press Ctrl+Enter.

Every blank now copies the value above it. The full method, including how to turn the formulas into fixed values, is in how to fill blank cells in Excel, and Go To Special shows the other selections it can make.

To fill only the visible rows of a filtered list, select the range, then use Go To Special > Visible cells only (Windows shortcut: Alt+;) before typing. That way hidden rows aren't touched.

Ctrl+Enter vs Ctrl+D: which should you use?#

Both put the same formula into many cells, but they work differently. Ctrl+D copies the top cell of the selection down, including its formatting, so the first cell must already hold the formula. Ctrl+Enter enters what you're typing and leaves each cell's formatting alone.

Use Ctrl+Enter when you haven't typed the formula yet, when the cells aren't next to each other, or when the cells have formatting you want to keep, such as coloured bands. Use Ctrl+D when the first cell is already correct and you just need to copy it down. The article on Fill Down and Fill Right covers Ctrl+D and Ctrl+R in detail.

Common Ctrl+Enter problems#

If Ctrl+Enter doesn't give the result you expect, check these:

• Only one cell filled. The selection was lost, often because you clicked a cell while building the formula. Select again and use the keyboard to point at cells.
• Every row shows the same wrong answer, or zeros. A reference that should be locked wasn't. Add $ to the fixed cell (press F4 on Windows while the cursor is in the reference; on a Mac, ⌘T).
• Existing data overwritten. Ctrl+Enter replaces whatever was in the selected cells. Undo with Ctrl+Z (Mac: ⌘Z).
• Formula points at the wrong row. You wrote it for a cell other than the active one. Rewrite it for the active cell.

For more time-savers like this, see the Excel keyboard shortcuts cheat sheet.

Practise it#

Shortcuts only stick once your fingers know them, and speed matters in a timed test. Practise Fill Down and Fill Right in a live spreadsheet: you type the formula and it checks it instantly. Then build your selection skills in the selection shortcuts lesson, and test your speed in the basic Excel mock test.