Here’s a Neat Excel Trick for Pasting Formulas

By Carol Bratt / 04/28/2015 / 3 Comments / Microsoft Excel

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.

About The Author

Carol Bratt

Carol holds A+, MCP, and MOS computer certifications and is the resident DCT Office expert. She trains the staffs of small law firms in the use of Microsoft Office applications and has authored many books covering Microsoft Office as well as written articles for Infopackets, TechnoLawyer, and Digital Harbor. For more Microsoft Office tips visit Carol's Corner Office or follow Carol on FaceBook and Twitter.

3 thoughts on “Here’s a Neat Excel Trick for Pasting Formulas”

Jason Shuffield
04/28/2015 at 5:07 pm

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..
dan
05/15/2015 at 2:20 pm

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
Carol A. Bratt
05/15/2015 at 3:38 pm

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.

Comments are closed.