60

I'm looking for a keyboard shortcut that will duplicate the functionality available by double-clicking the "handle" in the bottom-right corner of a cell. Is there something that can do this, without third-party software or creating custom macros?

12 Answers 12

46

I provide you with two methods to accomplish what you want. Unfortunately, both methods do have their own caveats, since Excel does not provide an all-in-one keyboard shortcut for auto-fill. I highly suggest you try both methods and then determine which works best for you. Outside of these two methods, I do not believe you can accomplish what you seek without the use of macros or add-in modules. Should you go the macro route, the AutoFill method on any Range object should be all that you need.


Method 1 (alternate, but it works with no mouse interaction)

Another way to accomplish this is to use a combination of the fill-down shortcut and the select all in range. After you enter the data in the cell, press Ctrl+Shift+End to select from the current cell to the end of the range that would be auto-filled. Then, press Ctrl+D to "fill down" into the entire range from the top cell.

Method 2 (my preferred way, one-time mouse interaction)

Once you fill in the cell you want to auto-fill downwards, double-click the bottom right corner as usual. To repeat the "auto-fill down" step, so long as you only enter data and press enter, you can repeat it by pressing Ctrl+Y.

This means that you do need to use the mouse, but only the first time. All subsequent work can be done purely with the keyboard, so long as you only use it to enter data (i.e. you don't use the context menu button to modify anything).


Note that the first method does come with a minor caveat - your view/cursor will move to the bottom of the spreadsheet (which does not happen with the Ctrl+Y method). You can press the Home key to move the cursor back up quickly.

2
  • Watch out: Ctrl+Y always fills just the same number of cells as the first time, so it might overwrite where you don't want it. Apr 7, 2021 at 7:15
  • It should be mentioned that the shortcut CTRL+D is a localized shortcut. Running Excel in German, for instance, the keyboard shortcut is CTRL+U. See this documentation page for reference and change the language/country code in the URL address appropriately: support.microsoft.com/en-us/office/…
    – AxD
    Jun 21, 2023 at 10:19
11

I'm also curious about a shorter keybind for the Autofill Handle. But this also works:

Key Sequence for Fill Series

Select the entire range you wish to extend the series, including the blank cells and the "seed" (the cell that contains the initial value on which the autofill series will be based).

Press ALT >> H >> F >> I >> S >> Alt + F >> Enter.

enter image description here

To create/edit your custom lists, press Alt + F, go to Popular then click on Edit custom lists. The custom lists serve as bases for how the Autofill Handle populates cells.

Fill Down

Ctrl+ D

Note

These key combinations only work for the English language of Excel. If you use Excel in any other language, search for the corresponding key combination for your language in: Home ribbon > Fill (in the Editing ribbon section) > Series.... In the dialog box select AutoFill and hit Enter. The fill down key combination may also differ (Ctrl+ D).

1
  • 2
    ^_^ If you add up-up-down-down-a-b-a-b chuck norris appears. Personally, I almost always don't use the fill handle much, and I rely on the rest of the data table to get around, or the Name box. For example, if i want to copy a formula from A1, to 200 cells below it, I'd type "A200" into the name box, press Enter, then Ctrl+Shift+Up, then Ctrl+D.
    – Ellesa
    Nov 23, 2011 at 3:05
3
  1. Select the range you want the data to be filled including the first line/ cell where data is available to the last line/ cell where data ends.
  2. Press Ctrl + G (go to)
  3. Go to Specials and select "Blanks" from the options available. Press OK.
  4. In the cell you are currently in (having selected the blank cells in the rage) enter the formula referring to previous cell "= + up arrow" (Say if you are in cell A3, type =A2) and press Ctrl + Enter.
  5. Convert the above formula into values by making copy & paste selecting the entire range again.
1
  • Very nice solution and the most efficient of the bunch. Aug 7, 2016 at 17:41
3

So far, my best solution was to:

  1. CTRL-C of source cell

  2. CTRL-G (go to) enter last cell in a column

  3. SHIFT-ENTER (highlights that whole section)

  4. ENTER (pastes values in all cells)

Works with Excel 2007 / 2013 / 365

1

Assuming there is data in an adjacent column, I like to do the following (assume the other column is adjacent to the left of the column to be filled):

Select the cell where you wanted to double-click the fill handle.

Then:

  1. Press Left to change to the column with other data
  2. Press Ctrl+Down to go to the end of the data
  3. Press Right to get back to the column that will be populated
  4. Press Ctrl+Shift+Up to select up to the cell with the fill value
  5. Press Ctrl+D to fill (Down)

The keys pressed are always the same, so you can put this on a keyboard macro.

Another sequence that does the same thing: Left, End, Down, Right, End, Shift+Up, Ctrl+D.

0

A shorter pre-2010 keyboard shortcut to bring up the Series (Windows) dialog box mentioned in @Ellesa's answer is:

ALT, E, I, S

It's only shorter by one keystroke but still an extra stroke nonetheless.

0

For what it is worth on the Mac I use Keyboard Maestro to execute a series of key commands for this so I don't have to use the mouse.

The sequence is:

  1. Down Arrow (these two commands allows copy to work properly)
  2. Up Arrow
  3. Command+c
  4. Left Arrow (change to right arrow to reference to right)
  5. Command+Shift+down arrow
  6. Option+Tab (change to option+shift+tab if you referenced to the right in step 4)
  7. Command+V

It is nearly instant and in most cases does the same thing as if you double clicked on the right lower portion of the cell you wanted to copy down. The only difference that I am aware being that if you are on a column that has nothing to the left right then takes priority of what it is filling down with.

That advantage here with these commands you can make two shortcuts and choose which side it is filling down referencing from.

0

You could also just copy, highlight, and paste after you enter in a formula. This method allowed me to just use keyboard, no mouse clicks.

Although it may just be quicker to double click. Depends on preference.

For example, I am doing a VLOOKUP. and the first lookup value is in cell B2. 1. I entered the formula in A2. 2. Then I copied cell A2 (Ctrl+c) 3. Then starting in A3, I highlighted the range I wanted to populate the formula in (Ctrl+Shift+Down) or (Shift+Down) 4. Then I pasted (ctrl+v)

When I did this the formula in cell A3 had the lookup value at B3, A5 had B5, etc. You may want to do a spot check to make sure the formulas adjust the first time you do that.

0

All of the answers above do not mimic the same behavior as double clicking the bottom right corner of a cell to Autofill a column to a desired number of rows based on adjacent columns. VBA is the only way to accomplish this with only the keyboard, using the following code:

Sub AutoFill_Column()

Selection.AutoFill Destination:=Range(Selection, Cells(ActiveCell.SpecialCells(xlLastCell).Row, Selection.Column))

End Sub

The same can be accomplished for autofilling a row (based on adjacent data above the row) with:

Sub Autofill_Row()

Selection.AutoFill Destination:=Range(Selection, Cells(Selection.Row, ActiveCell.Offset(-1, 0).End(xlToRight).Column))

End Sub
0

The simplest keyboard-only method that I've come across is to use the Name Box (to the left of the Formula Bar) to type in the range and press Enter to select it. Now Ctrl + D does the copy.

Say the first cell containing the formula you want to copy is D22 and you want it copied down to D3333, then you type D22:D3333 in the name box and press Enter and Ctrl + D.

A range such as D22:E4444 can be filled right by also using Ctrl + R.

N.B.

  1. The cursor doesn't need to be in the cell you want to copy, but it's more foolproof if it is.

  2. Fill Up and Fill Left don't work in the same way (but you don't need them).

  3. Works at least for Excel 1912 on Windows.

2
  • I think most people don't know how to select the name box without using the mouse. Could someone knowledgeable add that to this answer?
    – thymaro
    May 29, 2020 at 7:46
  • It's slightly simpler (and keyboard only) to use Ctrl + G to select the initial cell. In the example above type D22 in the GoTo Box. Then Ctrl + C to copy that cell. Now Ctrl + G to set the copy area by typing D22:D3333 into the GoTo Box. Finally Ctrl + D to complete the operation.
    – DLyons
    May 30, 2020 at 8:18
0

Tried a few ones and none fits my style, this one is 3 steps. Works with formulas.

Given that the highlighted cell is the one you want to fill downwards:

  1. CTRL-SHIFT-DOWN Select downwards until a cell with content.

  2. SHIFT-UP Decrease selection by one. We don't want to overwrite the found cell with content.

  3. CTRL-D Repeat down (In the spanish version, this would be CTRL-J )

On step 2 you can just release CTRL and keep SHIFT down

0

I learned from a professor, that you can copy a formula to multiple cells in a column in a very quick method as long as there are no blank cells in the adjacent column.

If you click the cell with the formula and then hover over the small square at the bottom right and double click it will automatically fill all of the cells to match the number of rows used for the previous column.

Example: I have data in columns F1:G23. I entered a formula in H1 using data in F1 and G1. I click cell H1 double clicked the square it filled all cells up to H23 with the adjusted formula for cell H1.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .