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.

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.

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.

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 “~”.

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 “~~”

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

I use a single (or double) quotation to make the formula appear as a string of text. … Yes, I’m a Lotus 123 lover!.

I have done this procedure several times and it does not work. What am I missing?

It is the keyboard shortcut for the Replace… command – check the Edit menu list

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!!).

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.

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.

“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”

“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? “

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

“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. “

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.

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 “~”.

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 “~~”

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

I use a single (or double) quotation to make the formula appear as a string of text. … Yes, I’m a Lotus 123 lover!.

I have done this procedure several times and it does not work. What am I missing?

It is the keyboard shortcut for the Replace… command – check the Edit menu list

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!!).

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.

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.

“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”

“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? “

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

“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.”