Things you need to know about copying and pasting formulas
Ideally, you will use cell references in your formulas and functions (I will use the term formulas in this post to mean both formulas and functions). Cell references reduce the manual workload for you: whenever you change a value in a cell, Excel will automatically recalculate formulas relating to this cell. When it comes to cell references in formulas, there are some things you will need to consider, which we will discuss in this post.
Copying and Pasting of Formulas
Whenever you copy a formula into a different cell, Excel will adjust the cell reference. This adjustment will be based on where the new cell is located on your sheet compared to the previous cell. These are called relative cell references. Example:
- In cell A1, you have the function =SUM(B2:B5)
- You copy the function and paste it into cell C5. The formula will now be: =SUM(D6:D9)
As you can see, Excel will keep the column-reference as +1 (A and B vs C and D), and the row-reference to start at +1 (row 1 and row 2 vs. row 5 and 6). This will be the case in either direction (columns to the left or right, rows above or below).
Fixing Cell References in Place
There are many usage cases where you will need the cell references to be fixed in place before copying and pasting them. Fixing a cell reference in place means that whatever cell in your sheet you paste the formula into, it will stick to the cell reference area you have outlined. Your previously relative cell reference becomes absolute. To allow flexibility for the user’s needs, there are three options of fixing a cell reference into place, all involving $ signs around the reference:
| Cell Reference Section Fixed in Place | Pasting Into a Different Cell Will… | Location of the Dollar Sign | Example |
|---|---|---|---|
| Column and Row Reference | … keep both the column and row reference unchanged. | Before the Column Reference AND Before the Row Reference | $B$2:$B$5 |
| Column Reference | … keep the column reference unchanged and adjust the row reference accordingly. | Before the Column Reference | $B2:$B5 |
| Row Reference | … keep the row reference unchanged and adjust the column reference accordingly. | Before the Row Reference | B$2:B$5 |
Keyboard Shortcut
Microsoft has included keyboard shortcuts to make fixing cell references in place easier:
- Windows-Computer: F4 (the F-function-buttons are located at the top of your keyboard)
- Mac-Computer: fn+F4 (the F-function-buttons are located at the top of your keyboard)
To use this shortcut, you will need to be ‘in the ‘inside’ the formula and have the relevant cell references either highlighted or your mouse cursor positioned in them. The first time you use the shortcut, Excel will absolute the column AND row reference. If you press the keyboard shortcut again (while still inside the cell), Excel will switch it to an absolute row reference, and on the third click it will turn into an absolute column reference. The forth time will remove all $ again.
Once you have the absolute cell references in place and copy and paste the formula again into a different cell, the cell references will stay intact as they are now absolute.