Md. Abdullah Al Murad
To get used to less usage of the mouse there is no alternative to practicing keyboard shortcuts. Sometimes it might seem difficult to copy formula down with shortcut. In terms of copying formulas in Microsoft Excel, there are lots of ways to use keyboard shortcuts. In this article, I am going to show you some ways with shortcut to copy formula down in Excel.
Download the Practice Workbook
5 Simple Methods to Copy Formula Down with Shortcut in Excel
In the following, I have shared 6 simple methods to copy formula down with shortcut in Excel.
Suppose we have a dataset of some Product Names, Prices, and Discount offers for those Products. Here we have also calculated the Discount Price for cell (D5). Now we will learn to copy the formula down shortcut in the workbook.
1. Use Keyboard Keys to Copy Formula Down for a Column
Using the keyboard shortcut you can easily copy a formula down in Excel. When you are copying a formula down within a single column start with the following steps.
Steps:
- First, choose a cell (D7) and press CTRL+SHIFT+END to select all the cells in the column.
- Then, press CTRL+D from the keyboard.
- As you can see we have successfully copied the formula down in the column.
2. Using SHIFT Key Sequence to Copy Formula Down
Another short technique to copy a formula down is you have to press some keys sequentially to reach your desired point.
Steps:
- Similarly, choose a cell (D7) with the formula and press the SHIFT+Down arrow key (↓) repeatedly to select all the cells you want to fill with.
- Hence, holding the ALT button click H+F+I from the keyboard.
- In summary, we have successfully copied the formula for the chosen cells without any hesitation. Simple isn’t it?
3. Utilize CTRL+’ Keys to Copy Exact Formula Down
Sometimes you might feel the need to copy the exact formula in every cell of a column or row. In that situation, you can follow the below instructions-
Steps:
- Select a cell (E8) just below the cell (E7) with the formula in it.
- Hence, press- CTRL+’.
- Immediately, the formula will be displayed in the selected cell.
- Now, press ENTER.
- To finish, follow the same task again and again until you fill cells with the exact formula.
- Within a moment, we completely copied the formula down in Excel using a simple shortcut.
4. Copy Formula Down Using CTRL+C and CTRL+V
If you want you can use the copy and paste shortcut to copy the formula down in your worksheet.
Steps:
- Presently, select a cell (D7) which has the precious formula which you want to copy for the other cells.
- While selecting the cell (D7) press CTRL+C to copy.
- Therefore, choose the immediate cell (D8) below and click CTRL+V to paste.
- Thus, you will get the formula copied to the below cell.
- Now, do the task repeatedly for the other cells following the same task.
- In conclusion, we have copied the formula down within seconds.
5. Shortcut Using Excel VBA Code to Copy Formula Down
In order to copy formulas down in Excel easily, you can use VBA code. Here I will show you how to copy formula for columns using a simple VBA code. Follow the instructions below-
Steps:
- First, select cells (D7:D13) and click ALT+F11 to open the “Microsoft Visual Basic for Applications” window.
- From the new window open a new “Module” from the “Insert” option.
- In the new module, type the following code and click “Save”-
Sub Copyformula_Column()
Selection.AutoFill Destination:=Range(Selection, Cells(ActiveCell.SpecialCells(xlLastCell).Row, Selection.Column))
End Sub
- Next, select “Macros” from the “Developer” option.
- A new dialog box will appear named “Macro”.
- Hence, select the “Macro Name” from the dialog box and press “Options” to continue.
- This time choose your desired shortcut key and click OK.
- Now, returning to the worksheet, press CTRL+E from the keyboard as we have chosen “CTRL+E” as our shortcut key in the previous window.
- Finally, the column is filled with formula copied in Excel.
Shortcuts to Copy Formula Through Rows
1. Apply Keyboard Shortcut to Copy Formula for a Row
To copy the formula down with a shortcut for rows you just have to press the CTRL+R key. Follow the steps below-
Steps:
- To start with, select cells (C8:I8) and click the CTRL+R button from the keyboard.
- Finally, the formula is copied row-wise for all the selected cells.
2. VBA Code to Copy Formula Down with Shortcut
If you want to employ a shortcut to copy formula down for rows then you can follow the same technique but with a different VBA code.
Steps:
- Similarly, selecting cells (C8:I8) press ALT+F11 to open the “Microsoft Visual Basic for Applications”.
- In the same fashion, open a new module and write the below code down-
Sub Copyformula_Row()
Selection.AutoFill Destination:=Range(Selection, Cells(Selection.Row, ActiveCell.Offset(-1, 0).End(xlToRight).Column))
End Sub
- Just like the previous sub-method, create a shortcut key for the macro and then in the worksheet click the shortcut to get your precious output.
- In conclusion, we have successfully copied the formula down in Excel with a simple shortcut.
Dragging Mouse Button to Copy Formula Down: Excel Fill Handle
Instead of using the keyboard you can try the mouse left button to drag and fill cells to copy the formula down quickly.
Steps:
- Here, choose a cell (D7) with a formula in it and then move the mouse cursor over the border of the cell.
- Thus, you will get to see a “Fill Handle” icon just like the following screenshot.
- Simply, pull the “Fill Handle” down to fill the cells with the formula.
- Within a glimpse of your eye, the column will be filled with the formula copying the formula. Isn’t it a simple trick?
Things to Remember
Using the “Fill Handle” tool you can also copy the exact same formula in the same row or column. For that, after filling choose the appeared icon at the end cell, and from there press “Copy Cells” to copy the exact formula.
Conclusion
In this article, I have tried to cover all the methods to copy formula down with shortcut in Excel. Take a tour of the practice workbook and download the file to practice by yourself. I hope you find it helpful. Please inform us in the comment section about your experience. We, the Exceldemy team, are always responsive to your queries. Stay tuned and keep learning.
” title=”