Copying formulas in Excel is one of the most common tasks that is easily done by a simple mouse click. Despite it being easy, there are specific cases that require a user to have special tricks to avoid changing a cell reference. For instance when you need to copy a cell containing a formula.
Here we guide you on the different methods to use to effectively copy formulas in Excel. Let’s dive in.
How to copy and paste using shortcut
1. In an open Excel worksheet, highlight the cell you wish to copy.
2. Press your keyboard keys Ctrl + C
3. Click on the cell where you want to paste your formula.
4. Press your keyboard keys Ctrl+ V. you can also right-click and click on the arrow next to the Paste
5. The arrow will give you a list of options, here choose the paste option you want to apply on the destination cell.
Using the Excel clipboard
1. In an open Excel workbook, highlight the target cell or range
2. Click on the Home tab on the main menu ribbon.
3. click Copy under the clipboard section
4. Afterward, try any of these steps:
- If you wish to paste the formula alone and any formatting, click Paste located under Clipboard
- Formula alone: in the Clipboard group, click Paste, then Paste Special, and select the Formulas
- If you wish to paste only the formula . Click on Home tab > Clipboard group > Paste > Paste Special >
5. Make sure that the cell reference in your formula will produce the intended results.
Using fill handle
1. You can copy the Excel formula using the Fill handle in a column or row.
2. In an open Excel worksheet, type in your formula in an empty cell.
3. Click on the lower right corner of the cell containing the formula you want to copy. Your cursor will become a bold sign.
4. Click and drag the cursor across the new column or row.
Similar Read: How to drag formulas in Excel till the end
Use Find and Replace
1. Open the workbook that contains the formulas you want to be copied.
2. Highlight the cells or range
3. Press the keyboard keys Ctrl + H to open the Find You can also go to the Home tab on the main menu ribbon > Find & Select > Replace.
4. In the Find and Replace dialog box, here is what you will do:
5. Enter = in the Find what section
6. Enter the hashtag sign on the Replace with the field.
7. Click the OK It will replace all the equal to sign with the hashtag sign in your cells.
8. Copy the cells and paste the results into the destination cells.
9. Go to Home tab > Find & Replace > Replace. In the dialog box, enter the hashtag sign in the Find what field and the equal sign in the Replace with the field.
10. Click Ok. The command will convert your text back to the formula.