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