How do I copy a value rather than a cell reference in a formula?

If I have the following cells

A1 |  =A3  |

A3 |   8   |

So the cell at A1 outputs '8' as it simply references A3. But if I delete the 8 from A3, it deletes the 8 from A1 also. How do I 'copy' the 8 into A1 so it's a value rather than a reference?

I should then be able to delete A3 but still have A1 as 8.....

Possible?

John Doe

Posted 2017-09-15T14:31:20.917

Reputation: 1

3Copy the cell in A3 and use Paste Special just the Value. – Scott Craner – 2017-09-15T14:34:27.507

You basically want the formula to automatically overwrite itself with the answer after is has finished formulating, correct? If so, this could be done in VBA but how would depend on how the data is going into your sheet. – DavePenn – 2017-09-15T20:48:45.880

Answers

Specifically for Windows Office 2016, copy the source cell with a formula inside. Right-click into the destination cell and choose Paste Value.

Similarly for Google Sheets, copy the source cell with a formula inside. Right-click into the destination cell and choose Paste Values Only.

Mac Office may act differently, a future reader may update this answer with the correct info.

Christopher Hostage

Posted 2017-09-15T14:31:20.917

Reputation: 4 751

No need to write =A3 in cell A1 in the first place but if you already did, you can simply navigate back to A1 and in the menu bar up top click on edit > copy. Then, still on A1, click edit > paste special > values. This is the same on Windows and Mac.

If efficiency is important to you, use shortcuts.

In Windows:

Copy using Ctrl + c on the cell containing the value;
Alt + e s v on the cell you are pasting the value into.

On a Mac:

Copy using COMMAND + C
Paste special using COMMAND + CONTROL + V
Select values and hit enter

Best of luck!

JulianWells

Posted 2017-09-15T14:31:20.917

Reputation: 11