9 ways to Copy Formula Down in Excel without Dragging

In normal cases of auto-filling a formula down in a column, we all are comfortable with using the Fill Handle. But using the Fill Handle can be a time-killing solution if your datasheet has a lot of cells. I’m going to show you 9 ways to copy a formula down in Excel without dragging the Fill Handle.

For this article, I am going to use a simple dataset of a list of fast food items (column A) from a restaurant, their raw price (column B) and their price with 15% VAT (column C).In the column Price (15% VAT), I have used an easy formula: =B2+B2*15%

Formula

 =B2+B2*15%

Formula Explanation

  • B2 is the original price of the food items.
  • B2*15% is the 15% increase in food prices.

Dataset to show copying a formula down in Excel without dragging

 

Copy Formula Down in Excel without Dragging by Double Clicking on Fill Handle

You don’t have to drag the Fill Handle always to copy a formula in your datasheet. There is a better way to do it. Follow these two steps:

  1. Take the cursor in the right bottom corner of cell C2 to bring the Fill Handle ‘+’ icon.
  2. Double-click on the Fill Handle.

This way the Fill Handle will autofill the rest of the cells of the column with the formula if your column has a single sequence of cells.

Result of copying a formula down in Excel without dragging

Pros: This is the fastest method and is going to save you a lot of time.

Cons: This method is not applicable if the cells of the column are not adjusted.

I am giving an example below for your convenience. Row 6 is blank here in the dataset.

Now if you double-click on the Fill Handle, the formula will only be applied from cell C2 to cell C5.

Copying a formula down in Excel without dragging If cells are non-adjacent

You can overcome this problem though. To apply the formula in the entire column, manually drag down the Fill Handle.

Copy Formula Down in Excel without Dragging Using Excel TABLE

Excel Table can help you to copy down a formula in Excel sheets. But in this case of using Excel Table, first, set the table and then put the formula in the Table. To do that, go through these steps:

  1. Select the Insert tab.
  2. Under the Tables group, Select the Table command.
    The Create Table dialog box will come along on the screen.
  3. Select your table range.
  4. Check the My table has headers checkbox has a checkmark.
  5. Hit the OK button.
    Create Excel Table to Copy Formula Down in Excel without Dragging
    You will notice your data table has a Table format now.

  6. Write your preferred formula in cell C2.
  7. Press ENTER.

Use Excel Table to Copy Formula Down in Excel without Dragging

The formula has been set in the rest of the cells now.

Result of Using Excel Table to Copy Formula Down in Excel without Dragging

Pros: Using the Excel Table can also help you to fill the non-adjacent cells.

I am using the previous example where the data of row 6 is missing.

Now type the formula in the top cell of Price (15% VAT) – column C.

You will see the formula has filled the column, even though the cells are not adjusted.

Use of Excel Table to Copy Formula Down in Excel without Dragging (if cells are non-adjacent)



Copy Formula Down in Excel without Dragging with Keyboard Shortcuts

We all know how to copy and paste something. But copy-paste can also be an alternative method to copy a formula down without dragging the Fill Handle. To apply keyboard shortcuts for copying down formulas without dragging:

  1. Select the top cell (cell C2) with the formula.
  2. Click on the Home tab.
  3. From the Clipboard group, select the Copy command (or press CTRL+C).
    Use the CTRL+C Keyboard Shortcuts to Copy Formula Down in Excel without Dragging
  4. Now select all the cells of column C.

  5. Again from the Clipboard group, click on the Paste command (or press CTRL+V).

Use the CTRL+V Keyboard Shortcuts to Copy Formula Down in Excel without Dragging

This way you can copy and paste a formula through the columns without going for the drag-down technique.

Copy Formula Down in Excel without Dragging by Fill Command

Another quick way to copy the formula without dragging the Fill Handle is to use the Fill command. Here are the steps below to do that:

  1. Select the cells of column C.
  2. Select the Home tab.
  3. From the Editing group, click on the Fill drop-down menu.
    Use the Fill Command to Copy Formula Down in Excel without Dragging
  4. From the Fill drop-down list, select the Down command.

Use the Fill drop-down & Down command to Copy Formula Down in Excel without Dragging

You will get the same desired result without dragging the formula.



Copy Formula Down in Excel without Dragging Using the Power Query

You don’t need to add a column for the formulas in the sheet. You can create a new column for the formulas in the Power Query. Now, follow these steps below:

  1. Select the Data tab.
  2. From the Get & Transform group, click on the From Table command.
    The Power Query Editor window will show up on the screen.

  3. Click on the Add Column tab.
  4. From the General group, select the Custom Column option.
    The Custom Column wizard will pop up.

  5. Insert a column name in the New Column Name box.
    I gave the column name – Price (15% VAT).
  6. Insert the formula in the Custom Column Formula box.
    But I have noticed, as I wrote the formula, a text saying “Token Eof Expected”. Show Error” becomes visible in the bottom part of the box.
    Power Query to Copy Formula Down in Excel without Dragging
    If this message appears in the Power Query,
  7. Click on the “Show Error”.
  8. Correct the errors.
    My error in the formula is, that I used the ‘%’ sign. So, I altered the ‘%’ to 15/100.
    Use the Power Query to Copy Formula Down in Excel without Dragging
    Now, a message saying “No syntax errors have been detected” will be displayed.
  9. Click on the OK button.

  10. Select the Home tab.
  11. Click on the Close & Load drop-down menu and select the Close & Load command.

The results are presented in a new table now.

Result of Using the Power Query to Copy Formula Down in Excel without Dragging

Copy Formula Down in Excel without Dragging Using a VBA Code

Those who are familiar with using the VBA code can also apply the VBA codes to avoid the drag-down method. Hence, here are the steps below to do that:

  1. Select the Developer tab.
  2. Under the Code group, click on the Visual Basic command.
    Use the Visual Basic option to Copy Formula Down in Excel without Dragging
    You can also enter the Visual Basic Editor in the Windows versions by pressing ALT+F11 or ALT+Fn+F11. Another shortcut to enter the Visual Basic Editor: Right-click on the current worksheet and select View Code.
    The Visual Basic Editor will come along on the screen.

  3. Click on the Insert drop-down menu.
  4. Select the Module option.
    Use the Module command in Visual Basic to Copy Formula Down in Excel without Dragging
  5. Enter the codes presented below:
    Sub Price_Calculator() 
    Dim gRc As Long 
    Dim g As Long 
    
    gRc = Cells(Rows.Count, "B").End(xlUp).Row 
       For g = 2 To gRc 
          Cells(g, "C").Value = Cells(g, "B").Value + (0.15 * Cells(g, "B").Value) 
            Next g 
    
    End Sub

    Use the VBA code to Copy Formula Down in Excel without Dragging

  6. Click on the Run menu.
  7. Select the option Run Sub/UserForm (or press F5).

  8. Close the Visual Basic Editor (or press ALT+F4 or ALT+Fn+F4).
  9. Now from the Code group, click on the Macros command.
    The Macro dialog box will pop up.
  10. Click on the Run button.

Run the Macro to Copy Formula Down in Excel without Dragging

You have your desired result now.

Result of Using the VBA code to Copy Formula Down in Excel without Dragging



Copy Formula Down in Excel without Dragging Using Shortcut Keys

I am going to use a different example here to show you how to use CTRL+D and CTRL+R shortcut commands.

Formula

=$B5+$B5*C$2

Formula Explanation

  • $B5 means, the $ sign is going to lock column B and change the row number in the order of B5, B6, B7, B8,…B13.
  • C$2 is the fixing of row number but changing the column number. That means C2 will be D2 if I move the formula to the right side direction.

To copy the formula down in Excel without dragging with shortcut keys, go through these steps below:

  1. Select a cell.
  2. Type the formula: =$B5+$B5*C$2
    Use the CTRL+D Copy Formula Down in Excel without Dragging
  3. Press ENTER to apply the formula.
  4. Select the cells from C5 to D13.
  5. Press CTRL+D to apply the formula in the down direction.
    Result of Using the CTRL+D shortcut Key to Copy Formula Down in Excel without Dragging
  6. Press CTRL+R to copy the formula to the right side direction.

This way you can copy a formula in more columns on the right side.

Result of Using the CTRL+D and CTRL+R to Copy Formula Down in Excel without Dragging

Copy Formula Down in Excel without Dragging Pressing CTRL+ENTER

If your data table has small numbers of cells, this is another fast technique to copy formulas without dragging. To do that, press CTRL+ENTER by following these steps below:

  1. Mark all the cells of column C.
  2. Type the formula in the top cell (cell C2).
    Use the CTRL+ENTER to Copy Formula Down in Excel without Dragging
  3. Press CTRL+ENTER.

That’s a very 3-step easy technique for copying formulas in Excel.



Copy Formula Down in Excel without Dragging by Using Keyboard Arrow Sign

This approach is suitable for those who are more into using the keyboard. You don’t need to touch the mouse cursor for once in this entire procedure. To copy the formula down without dragging with shor, go through these steps below:

  1. Press CTRL+C to copy cell C2.
  2. Press the Right arrow ‘←’ key to move to cell B2.
    Press CTRL+C & right arrow to Copy Formula Down in Excel without Dragging
  3. Press CTRL+Down arrow ‘↓’ key to straight away go to the last cell (cell B10) of column B.
    Now you are probably thinking, why I did not move to cell C10 from cell C2. It’s because there is no value in cell C10, so we can not proceed directly in C10.
  4. Now, press CTRL + SHIFT + Up arrow ‘↑’ key.
    This will select the cells of the range C2:C10.
    Press CTRL + SHIFT + Up Arrow to Copy Formula Down in Excel without Dragging
  5. Press CTRL+V to paste the formula.

This is a very handy technique to apply.

Conclusion

I have tried to show you all the feasible methods to copy formulas without dragging down the Fill Handle like always. Now you can apply one of the methods whichever you prefer. Leave a comment in the comment section if you have anything to say about this article. Have a good day!

Frequently Asked Questions

What is the shortcut for duplicate formulas in Excel?

In Excel, the shortcut for duplicating formulas is CTRL + ‘

Pressing CTRL and the single quotation mark () keys simultaneously copies the formula from the cell above the active cell and pastes it into the current cell. This keyboard shortcut is a quick way to replicate formulas in Excel.

How do I autofill a format?

In Excel, to autofill a format:

  1. Click on the cell containing the format you want to replicate.
  2. Move the cursor to the small square (Fill Handle) at the lower-right corner of the selected cell.
  3. Click and drag the Fill Handle over the range where you want to apply the same format.
  4. Release the mouse button to complete the autofill of the format.

By using the Fill Handle to drag and replicate the format, Excel will automatically apply the selected format to the adjacent cells in the specified range.

Rate this post

Leave a Reply

Your email address will not be published. Required fields are marked *