how do i paste absolute values, like paste special > values

rudgeworks · August 15, 2019, 12:32pm

When i use a formula - how do i paste absolute values, as i would useing “paste special” > “values” in MS excel?

anon73440385 · August 15, 2019, 12:40pm

Hello

Copy your source cell (CTRL-C or Right click - Copy)
Move to your target cell
Right click -> Paste special and make your selection what to paste (plenty of options) – or – use Edit -> Paste Special...

I assume this doesn’t differ much from Excel.

Update - see screenshot (copying cell A3, moving to target A5 and Paste Special -> Number will add value 3 to A5

image description

rudgeworks · August 15, 2019, 12:54pm

There is no obvious way of pasting the resultant value of a formula - ie cell contains =a1+b1 in excel I can copy the cell and use paste special … values to past the result of the formula ( but not the formula it’s self) or am I missing something.

anon73440385 · August 15, 2019, 1:04pm

Did you even try what I wrote - The procedure in my answer pastes the result of a formula and not the formula itself. That’s why it is called Paste special as it does in Excel. Please see Update in my answer.

JohnMurrell · February 1, 2020, 9:32am

See my answer - that works Ok.

anon73440385 · February 1, 2020, 2:06pm

how do i paste absolute values

Your answer answers something you did not ask.

JohnMurrell · February 1, 2020, 9:31am

It does not work like Excel

The function used to be under Tools - Formula to Value

It has now moved to data - Calculate - Formula to Value

QED ?

anon73440385 · February 1, 2020, 2:06pm

This is not an answer to what you asked - You asked about paste and this answer is about conversion of formula to values.

JohnMurrell · February 1, 2020, 4:07pm

In Excel Paste Special Values converts the result of the formula in a cell to a value ir it removes the formula. In Excel you can paste ‘over the top of’ the selected cells thus removing the formula and ending with values. This is apparently not allowed in Calc. It appears that in Calc you can paste special - Values into other cells but not into the source cells.

As a result you have to use the calc function ‘Formula to Value’ if you want the result in the cells that used to have formula.

Neither spreadsheet has the ability to paste special absolute values as far as I am aware so I believe the person who asked the question had already used the ABS function to get the absolute value in the cell(s) and was attempting to convert this to a value rather than having the result as a formula for some reason

Pasting values in a spreadsheet is dangerous of course and should be deprecated as someone may well change an input value and not notice that the resultant pasted value did not change