Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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

3 Answers 3

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

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. –  Jo Bedard May 13 '13 at 19:19

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 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 at 0:22

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.