excel

Here’s a Neat Excel Trick for Pasting Formulas

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

  1. Select the cells where you would like to paste the formulas.
  2. Notice that the formula you would like to copy is in the active cell.
  3. Click F2 and you will be in edit mode.
  4. 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:

  1. Point to the Fill Handle at the right-hand bottom corner of the cell you would like to copy.
  2. Depress your right mouse button and drag down your column.
  3. When you release your mouse button, select Fill Without Formatting.
  4. Your formula has now been filled down to all of the cells without affecting your formatting.

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

  1. Jason Shuffield

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

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

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

Scroll to Top

WHY NOT SUBSCRIBE TO OUR NEWSLETTER?

Get great content like this delivered to your inbox!

It's free, convenient, and delivered right to your inbox! We do not spam and we will not share your address. Period!