One of the great things about working digitally is that it can cut down on a lot of unnecessarily repetitive work. For example, if you must fill the same content into multiple cells in a spreadsheet, you can just copy and paste the values to save time.
Although, this can get a little tricky if you need to copy formulas. Luckily, Excel has several ways to copy and paste formulas. But, there are a couple of things you need to keep in mind when doing so.
Relative Cell References
Before we can jump into copying formulas, you need to know a little bit about how Excel references cells. Excel tracks the relation between the cells in the formula, not the actual cells.
For example, in the image below, cell C2 contains the formula A2 + B2. But thanks to relative cell references, Excel reads this as: add the cell that is two places to the left to the cell that is one place to the left.
These relative cell references can be very handy. If you want to add the values on row 3 and 4 in the same way you did for row 2, all you have to do is copy the formula down without worrying about changing the rows yourself. Excel updates the rows in each formula so that the cells to the left of that add together.
However, sometimes you don't want a cell's location to change when you copy a formula.
For example, let's say you want to work out the sales tax on a series of products, as we've done below. If you add the sales tax to one cell, you want that cell to remain the same in the formula for each product. To do this, you need to tell that Excel the location of that cell is fixed, not relational. You do this with a $ sign in front of the row, column, or both.
Adding the $ before the B tells Excel that no matter where we paste the formula, we want to look at the B column. To stop the row from changing, we also added a $ before the 1. Now, no matter where we paste the formula, it will always reference B1 for the tax value.
As we copy the formula down the column, the price location updates, but the sales tax location stays the same.
Using the F4 Keyboard Shortcut
There is a keyboard shortcut to toggle through a cell’s reference options. When you're writing a formula and click on a cell, hit F4 to fix that cell. For example, if you click on B1 and press F4, it changes the reference to $B$1. If you press F4 again, the cell reference changes to B$1, then to $B1, and finally back to B1.
Copying and Pasting a Formula Manually
The most familiar way to copy and paste a formula is to copy and paste the formula text inside a cell. This is similar to how you would copy and paste text in Word.
Copy the text by selecting the cell and right-clicking the formula at the top of the screen. This brings up a popup with various options, select Copy. You can also use the copy button in the ribbon, which is located in the Clipboard section of the Home tab.
Then unselect the text by pressing the Return key. Finally, right-click the new cell you want to paste into and click on the clipboard icon or use the Paste button in the ribbon. You can also use the keyboard shortcut, Ctrl + C, to copy the highlighted text and Ctrl + V to paste it once you select a new cell.
This method is familiar, but not one of the best ways to copy a formula. If you had to copy a formula to multiple cells it would be time-consuming. This method also copies your exact text, so you don't get the benefits of relative cell references we talked about above.
You should only use this method if you only need to copy the formula to a couple of places and you want the cells to remain exactly the same each time.
A Better Way to Copy a Formula in Excel
An easier way to copy the formula is to use copy and paste on the entire cell instead of just the text inside it. Click on the cell with the formula you wish to copy. Then copy it by either right-clicking on the cell, or using the keyboard shortcut Ctrl + C.
Once you copy the cell, it will have a dashed green border to show that you are currently copying it. Next, select the cell you want to paste the formula to. Then paste the formula by either right-clicking on the cell, or using the keyboard shortcut Ctrl + V.
This time, you'll notice the formula uses relative cell references. Instead of A2 + B2, a formula on the row beneath becomes A3 + B3. Similarly, if you pasted the formula in the next column in the row beneath, it would update to B3 + C3.
How to Drag a Formula Down a Column or Across a Row
The method above can still be too time-consuming if you need to paste the same formula to multiple rows or columns. Luckily, there are two even quicker ways to do this.
First, you can copy the formula as you did above, but instead of pasting it to one cell, you can click and drag to select multiple cells and paste the formula to all of them by right-clicking any the cell or using the keyboard shortcut Ctrl + V.
The second way to paste the same formula to multiple rows is to drag it. At the bottom-right corner of a selected cell, you'll see a green square. Click on that square and drag it over the cells you wish to copy the formula to. This is probably the quickest way to copy an Excel formula down a column or across a row.
Again, you'll see that Excel updates the formula to use relative cell references for each row or column that changes.
One problem you may have when pasting a formula is that it also pastes any styling to the selected cells. Styling includes elements like the font size, cell outline, colors, or bold settings. Pasting the styling is inconvenient if you use alternate line colors or if you outlined your table.
To solve this, Excel introduced Paste Special.
Use Paste Special to paste just the formula without any of the styles that were added to the cell. To use Paste Special, right-click and select Paste Special from the popup menu.
A Recap of How to Copy and Paste Excel Formulas
Excel is optimized to reduce the number of repetitive tasks that you need to complete. Adding the same formula to multiple cells is quick and easy to do in Excel. You can copy and paste formula text, much like you would in a Word document. But if you want to take advantage of relative cell references, you're better off using different methods.
A good way to copy a formula is by selecting the entire cell with the formula and copying that. If you need to copy a formula down a column or across a row, you can also drag the cell across the area you want to copy it to, which is much quicker.
Both methods allow you to copy a formula to multiple cells quickly. Next time you create a spreadsheet, remember to try it out and save yourself some precious time.