Handling Relative References in Excel [VIDEO]

When you copy a formula in Excel, any relative references (those without dollar signs) may change depending on where you paste the formula. If you would like to copy a formula and not have the relative references change you have two options.

From Cell Above

If you are copying from the cell above you can use Ctrl + ‘ (Ctrl + single inverted comma) which will copy the exact entry from the cell above. In terms of formulas, this will be the exact same formula no relative reference changes.

To Any Other Location

If you need to copy the formula to a separate location you can use a different technique.

You can copy the formula within the formula bar. You use the mouse to select the whole formula in the formula bar and press Ctrl + c or right click and choose Copy. Press Esc to deactivate the formula bar.

Then select another cell and paste. You can’t paste to a range using this technique.

You can also use this technique to copy and paste within the formula bar. Sometimes when creating IF functions that handle errors you need to repeat the same part of the formula. Just select part of the formula and use copy and paste within the formula bar.

The short video below demonstrates the two techniques.

 

 

Please note: I reserve the right to delete comments that are offensive or off-topic.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.