Have you ever wanted to use one of Excel’s awesome formulas on a few cells, but didn’t want to type it all out? Fortunately, there’s an easy way to quickly copy a formula from one cell to another in just two clicks, so you can spend less time inputting data and more time analyzing it. Here’s how it works!
Method 1: Use Copy and Paste Special Values
This method uses toolbar buttons you already know. The difference is most people paste the formulas into a new column. Unfortunately, they don’t realize they can paste the results into the same column.
- Press Ctrl+Alt+F9. This forces the spreadsheet to recalculate all cells in case auto-recalculate was turned off.
- Click the top cell of the column you wish to copy. In this tutorial, it would be C1.
- Press Ctrl+Shift+ ↓ to select the column.
- Click the Copy button from the Home tab on the ribbon.
- Now, click the bottom part of the Paste button on the toolbar to reveal more options.
- In the Paste Values section, click the first icon.
- If you move through column C you should see that the formulas have been replaced with the text names.
Method 2: Click the Plus Sign to Fill the Whole Column
To apply the formula to the entire column, double-click the plus sign instead of dragging it. For those who have skipped the first section of this tutorial, the detailed steps follow below.
- Input your formula in the top cell.
- Position the cursor to the lower right corner of the cell with the formula, wait until it turns into the plus sign, and then double-click the plus.
Note. Double-clicking the plus sign copies the formula down as far as there is some data in the adjacent column(s). As soon as an empty row occurs, the auto fill stops. So, if your worksheet contains any gaps, you will have to repeat the above process to copy the formula below an empty row or drag the fill handle:
Related: how to hide formula in excel
Method 3: Copy Formula in Excel to Another Sheet
Step 1: Copy a Formula Down in Excel by Dragging
You can quickly apply an Excel formula to adjacent cells by dragging the corner of the cell containing the formula. Follow the instructions below:
- Click on the cell containing the formula you wish to copy.
- Hover your cursor over the bottom right corner of the cell. Your cursor should turn into a plus sign.
- Select and hold the bottom right corner of the cell, then drag it into the desired direction to select cells to fill.
- Release the mouse. The formula will be applied to the selected cells automatically.
- If your cursor doesn’t turn into a plus sign and you can’t drag the cell, this feature might be hidden.
Step 2: Copy a Formula Down in Excel Without Dragging
Sometimes, you may need to copy an Excel formula into non-adjacent cells, and the dragging method won’t work. In this case, you can select cells manually. Follow the steps below:
- Click the cell containing the formula.
- Press the “Ctrl” and “C” keys simultaneously to copy the formula.
- Press and hold the “Ctrl” key, then click on the cells to which you wish to apply the formula.
- Click an arrow next to “Paste” at the top of the document and select “Formula.” It will be applied to the selected cells automatically.
Method 4: Copy Formula in Excel to Entire Column
Step 1: Using the Fill Down Option
For this method to work, you first need to select the cells in the column where you want to have the formula. Below are the steps to use the fill down method:
- In cell A2, enter the formula: =B2*15%
- Select all the cells in which you want to apply the formula (including cell C2)
- Click the Home tab
- In the editing group, click on the Fill icon. Click on ‘Fill down’
- The above steps would take the formula from cell C2 and fill it in all the selected cells
Step 2: Using the Quick Access Toolbar
If you need to use the fill down option often, you can add that to the Quick Access Toolbar, so that you can use it with a single click (and it’s always visible on the screen).
- To add it to the Quick Access Toolbar (QAT), go to the ‘Fill Down’ option, right-click on it, and then click on ‘Add to the Quick Access Toolbar’
- Now, you will see the ‘Fill Down’ icon appear in the QAT.
Method 5: Copy Formula in Excel Without Changing Reference
Step 1: Manually Copy Paste the Exact Formula
If you only have a handful of formulas that you want to copy and paste without changing the cell references, doing it manually would be more efficient.
- Select the cell from which you want to copy the formula.
- Go to the formula bar and copy the formula (or press F2 to get into the edit mode and then copy the formula).
- Select the destination cell and paste the formula.
- Note that this method works only when you have a few cells from which you want to copy formulas.
Related: how to remove formula in excel
Step 2: Using Notepad to Copy Paste Formulas
If you have a range of cells where you have the formulas that you want to copy, you can use a Notepad to quickly copy and paste the formulas.
Here are the steps to copy formulas without changing the cell references:
- Go to Formulas –> Show Formulas. This will show all the formulas in the worksheet.
- Copy the cells that have the formulas that you want to copy.
- Open a notepad and paste the cell contents in the notepad.
- Copy the content on the notepad and paste in the cells where you want the exact formulas copied.
- Again go to Formulas –> Show formulas.
Note: Instead of Formulas –> Show formulas, you can also use the keyboard shortcut Control + (this is the same key that has the tilde sign).
Method 6: Copy Formula in Excel Shortcut
Step 1 : CTRL + R for Copying Formula Across a Row
Now we want to show the same method for copying any formula across a row. We have the following data. We want to copy down this formula from B4 to E4.
- Select the cells from B4 to E4.
- Press CTRL + R on your keyboard. You will find that the formula in cell B4 is copied from cell B4 to E4.
Step 2: Copy Down Formulas with a Single Mouse Interaction
This is one of the easiest ways to copy down any formula in excel with just one-time mouse interaction. That means you will need to use the keyboard only once after that you can do the job easily with the keyboard.
- Enter the desired formula into any cell and press enter.
- Select the last cell of the range.
- Enter Ctrl+Y and all the values will be calculated within the range.
These are some Methods to Copy Formula in Excel. I have shown all the methods with their respective ways but there can be many other iterations. If you have any other method of achieving this, then please feel free to share it with us.