Copying a Formula from a Cell While Keeping the Absolute Reference or Relative Reference

Avoid the nightmare of pressing F4 multiple times when coping and pasting formulas!

To copy/paste a Formula without changing the Absolute or Relative references:

Option 1: Select a cell under the cell containing a formula and press Ctrl+ ‘.
Option 2: Copy and paste the formula from the Formula Bar to a cell, instead of from a cell to another cell.

For example, cell C12 contains the formula:

1. Select the formula string in the Formula Bar and press Ctrl+C to copy it.
2. Leave the Formula Bar by clicking the Enter or Cancel icons to the left of the fx on the Formula Bar.
3. Select another cell and press Ctrl+V.

Users are saying about us...

  1. Quite elegant a solution; only suggest using “~” instead of “#” as temporary replacement for the “=” sign. “#” is part of error ‘variables’, while “~” is seldom used for anything.

  2. wondering what the “~” is actually used for, it does not work for me in the find and replace scenario. ie My excel does not find the character “~”.

  3. In excel the ~ is used by the replace and find functions as a prefix to the wildcards “?” and “*”, so in order to find “~” you must enter the tilde twice. i.e. to find “~” type in “~~”

  4. However, for individual cells, I prefer selecting the formula and using Ctrl-C, escaping, then selecting the target cell and using Ctrl-V.

  5. I use two $ signs (as in $$) rather than tildes or anything else. Hasn’t caused trouble yet. Ctrl H is also good for changing particular elements of a formula, especially when you’re wanting to make changes in many formulae in multiple tabs (I have an application with 21 tabs, & the replace function is essential!!).

  6. I insert an apostrophe in front of the equal sign to change the formula to a literal. I then copy the cell contents and remove the apostrophe.

  7. Copy the formulas to the same place in a different sheet. Move them in the new sheet to the desired location, then copy them back to this new location in the original sheet.

  8. “Copying the formula itself is easier
    by..
    1. F2
    2. Shift+Home — to select all formula
    3. Ctrl + C — copy
    then Ctrl + V — paste to relevant place”

  9. “Does anyone know how to insert a formula alone to existing filled in cells without deleting existing data?
    Example: If a there are 50 rows of data, say values in Kilo Bytes, now i want to convert it to MB by dividing the KB data by 1024. So I got to insert =/1024 to the existing data.

    In KB
    ———
    893
    10345
    67899

    To Convert to MB, here’s what I should have:
    =893/1024
    =10345/1024
    =67899/1024

    Is there an easy way to do it instead having to edit the numbers and insert formula in each cell? “

  10. I need a formula to take a total of numbers and make a percentage out of the for example: If you sold $5,000.00 in sales what would be the percentage of that or at the end of the month what would be you sales percentage

  11. “Sorry, but I cannot make out what it is that you are asking for.

    Please can you give a few numbers as an example, and the result that you would want calculated. “

  12. Quite elegant a solution; only suggest using “~” instead of “#” as temporary replacement for the “=” sign. “#” is part of error ‘variables’, while “~” is seldom used for anything.

  13. wondering what the “~” is actually used for, it does not work for me in the find and replace scenario. ie My excel does not find the character “~”.

  14. In excel the ~ is used by the replace and find functions as a prefix to the wildcards “?” and “*”, so in order to find “~” you must enter the tilde twice. i.e. to find “~” type in “~~”

  15. However, for individual cells, I prefer selecting the formula and using Ctrl-C, escaping, then selecting the target cell and using Ctrl-V.

  16. I use two $ signs (as in $$) rather than tildes or anything else. Hasn’t caused trouble yet. Ctrl H is also good for changing particular elements of a formula, especially when you’re wanting to make changes in many formulae in multiple tabs (I have an application with 21 tabs, & the replace function is essential!!).

  17. I insert an apostrophe in front of the equal sign to change the formula to a literal. I then copy the cell contents and remove the apostrophe.

  18. Copy the formulas to the same place in a different sheet. Move them in the new sheet to the desired location, then copy them back to this new location in the original sheet.

  19. “Copying the formula itself is easier
    by..
    1. F2
    2. Shift+Home — to select all formula
    3. Ctrl + C — copy
    then Ctrl + V — paste to relevant place”

  20. “Does anyone know how to insert a formula alone to existing filled in cells without deleting existing data?
    Example: If a there are 50 rows of data, say values in Kilo Bytes, now i want to convert it to MB by dividing the KB data by 1024. So I got to insert =/1024 to the existing data.

    In KB
    ———
    893
    10345
    67899

    To Convert to MB, here’s what I should have:
    =893/1024
    =10345/1024
    =67899/1024

    Is there an easy way to do it instead having to edit the numbers and insert formula in each cell? “

  21. I need a formula to take a total of numbers and make a percentage out of the for example: If you sold $5,000.00 in sales what would be the percentage of that or at the end of the month what would be you sales percentage

  22. “Hi Bobbie,

    Sorry, but I cannot make out what it is that you are asking for.

    Please can you give a few numbers as an example, and the result that you would want calculated.

    Thanks,

    Alan.”

Leave a Reply to Cameron Cancel reply

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

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.

Visit Us On TwitterVisit Us On FacebookVisit Us On Google PlusVisit Us On Youtube