Many Excel worksheets have columns and rows of identical formulas that have different formatting throughout. Now, when you edit a formula and want to copy it down your column or across your row, but don’t want to over-ride the formatting of the other cells, you would normally use a right-click and select Copy, select the cells where you would like to paste and right-click and select Paste Special | Formulas.
Guess what?? There are quicker, easier methods to achieve the same outcome:
Start with the cell containing the formula you would like to copy.
- Select the cells where you would like to paste the formulas.
- Notice that the formula you would like to copy is in the active cell.
- Click F2 and you will be in edit mode.
- Click CTRL + Enter and your formula has been copied down without affecting formatting in other cells.
But wait! There’s more! There is another method.
Follow the steps below to learn how:
- Point to the Fill Handle at the right-hand bottom corner of the cell you would like to copy.
- Depress your right mouse button and drag down your column.
- When you release your mouse button, select Fill Without Formatting.
- Your formula has now been filled down to all of the cells without affecting your formatting.
Carol love your articles… This will help me a lot. Need to share this with my wife she uses excel often so I think this tip will help her out..
I’d like to find a way to change the default FIND options in Excel. Currently it defaults to WITHIN SHEET whereas WITHIN WORKBOOK would be better for me.
Dan
Excel does not allow you to specify the settings you would like to have for a default in the Find dialog box. There is, however, a work around of sorts. Excel will remember the last settings in the Find dialog box for the entire Excel session and will not be reset until you exit and restart Excel, which means that all you have to do is create a small macro that will set the setting you would like to have in the dialog box.
There are a couple of methods to do this, the first of which is to create a macro that sets the options in the dialog box directly:
Sub SetFind2()
Dim c As Range
c = Cells.Find(What:=””, LookIn:=xlValues, LookAt:=xlPart)
End Sub
The second method would be to utilize the Find method of the Cells object:
Sub Macro1()
‘
‘ Macro1 Macro
‘
‘
Sheets(“Sheet1″).Select
Cells.Find(What:=””, After:=ActiveCell, LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False).Activate
End Sub
Both of these will work for you up to a certain point. All you have to do is run your macro when you start Excel. The settings in the dialog box are then changed for the rest of your Excel session unless you manually change them.
I hope this has been helpful to you.