When you press CTRL+D in Excel 2007 or 2010 (and probably older versions), it makes an exact copy of the cell above it (modifying cell references, of course). In doing so, it copies everything else like font formatting, fills, and even borders (ugh). If I could just copy the value/forumula and just inherit the current cell's formatting, that'd be awesome.

Update 2014/08/01

I came up with this awesome macro. However, the downside to using a macro is that you lose the ability to undo. I know there is a way to implement undo functionality with a macro without creating an undo macro. I've done this before in Excel. There's a way to tell Excel to somehow preserve the state of the workbook so that undoing will actually revert. That's another question, though, which I'll probably end up posting.

Sub CtrlD()

Dim r As Range

Set r = Selection

Application.Union(r, r.Offset(-1, 0)).FillDown

End Sub

Unless anyone knows of a more native approach to this then that answerer will get the mark.

5 Answers 5


Select the range by SHIFT + arrows where you want to fill the contents to.

Press F2.

Press CTRL + ENTER to fill area.


You can copy only formulas and values, leaving the formatting behind by using the keyboard shortcut Ctrl - ' -- that's the Ctrl key and the single quote.

Or you can use the fill handle to drag down and then select the Autofill options drop-down with "Fill Without Formatting".

enter image description here

  • 1
    Unfortunately, CTRL+' doesn't appear to modify cell references (a requirement in my question). Instead of modifying =A1+A2 to be A2+A3, it copies formula verbatim. Also, dragging the fill handle down with the mouse defeats the purpose of a keyboard shortcut. I'd like to be able to press CTRL+[whatever] and have it do everythign that CTRL+D does without it copying the formatting. May 13, 2013 at 13:23
  • Ctrl+' only seems to work for a single cell. Also note that it copies the value from the first non-highlighted cell above the selection. Jun 19, 2018 at 14:34

Turns out you can do the equivalent of this from the keyboard only by pressing the following keys in sequence: Alt, H, V, O, after copying the formula you want.

It's somewhat cumbersome but you can basically use it just like Ctrl+D.

This works because it gets you to "Paste Formulas and Number Formatting" which I think is basically what you want. Borders and font styles don't get carried down.

There's probably a way to put this as a button on the ribbon and assign it its own shortcut.

  • The idea is good, but another answerer already came up with a similar solution involving pasting formulas which doesn't copy any formatting at all. Another downside to this is that you lose whatever has been previously copied to your clipboard. It's also inconvenient to have to first highlight the row above and then copy which are key strokes that you don't have to do with the CTRL+D shortcut. Aug 1, 2014 at 10:53
  • @oscilatingcretin Indeed, it is not perfect. But, the copying number formatting is not a strike against: CTRL+D does the same thing.
    – Ben S.
    Aug 2, 2014 at 0:22

In the ribbon: go to View tab-->unhide-->personal.xlsb-->Ok-->record new macro--> In the dialog box: name:"pasteVvalue"-->shortcut "crtl+D"-->record in "personal macro folder"-->ok Use any random command from the ribbon-->stop recording-->display macro:"pasteValue"-->erase all vba code between the line Sub pasteValue() and End sub insert the following--> save

 Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False

From now on crtl+d will behave the way you want (copy value or formula in changing references) in all Excel files you're using on your computer.

  • You forgot to actually copy the text in your macro. You also used xlPasteValues instead of xlPasteFormulas. I've updated my question with a solution using your idea. If no one else advises of a more native method of accomplishing this, you'll get the answer. May 13, 2013 at 18:07
  • Actually, wait, I can't use this. I just realized that copying a cell will blow out whatever I have stored in my clipboard, so this won't work. May 13, 2013 at 18:10

This macro captures the functionality of teylyn’s answer (autofill without formatting) in VBA, as inspired by your code:

Sub Extend_Content_From_Above()
    Dim above As Range

    For Each cur In Selection
        Set above = cur.Offset(-1, 0)
        above.AutoFill Destination:=Range(above, cur), Type:=xlFillValues
    Next cur
 End Sub

Note that it explicitly handles a multi-cell selection, since Excel’s native handling of that is arcane.

  • This appears to exhibit strange behavior. Make A1=3 and B1==A1. Now select range A2:B2 and run your macro. It attempts to fill a series on column A while it adjusts column B's cell reference. However, if you run your macro on cell B2 alone, it copies the value. Oct 28, 2015 at 14:36
  • Ah, same thing in Excel 2013.  I didn’t think to test it on a multiple-cell selection, since your question didn’t mention that you were interested in doing that.  If it’s any consolation, teylyn’s mouse-based answer behaves the same way.  But the good news is, I’ve fixed it; see the revised answer. Oct 28, 2015 at 17:34
  • I'm not trying to nitpick, but what does your solution do that Application.Union(above, above.Offset(-1, 0)).FillDown from my OP doesn't? Oct 28, 2015 at 19:04

You must log in to answer this question.

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