Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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.

share|improve this question

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

share|improve this answer
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. – oscilatingcretin May 13 '13 at 13:23

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.

share|improve this answer
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. – oscilatingcretin Aug 1 '14 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 '14 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.

share|improve this answer
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. – oscilatingcretin May 13 '13 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. – oscilatingcretin May 13 '13 at 18:10
I've edited my answer, thanks. – P. Obertelli May 13 '13 at 19:19

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.

share|improve this answer
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. – oscilatingcretin Oct 28 '15 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. – Scott Oct 28 '15 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? – oscilatingcretin Oct 28 '15 at 19:04

You must log in to answer this question.

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