CTRL+D in Excel 2010 fills values and formatting, but I want it to only fill values/formulas

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

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".

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

Press F2.

Press CTRL + ENTER to fill area.

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.

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.

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.