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

microsoft excelmicrosoft-excel-2010

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.

Best Answer

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

Related Question