Ask Your Question

[calc] Can I cut & paste the *contents* of multiple cells at once without affecting references and formats?

asked 2015-07-29 23:38:10 +0200

barak gravatar image

updated 2015-08-23 19:14:33 +0200

Alex Kemp gravatar image

If I triple-click a single cell I can cut and paste just the content of that cell, without affecting cell formatting and without messing up formulae which reference the cell I'm working on. Is there a way to do that for multiple cells at once? Maybe some sort of cut-contents-only keyboard shortcut? More generally, is there a way to "freeze" cell formatting and formulae in place and then work on cell contents only? Thanks, B

LibreOffice en-us Mac OS X 10.7.5 en-us

LibreOffice 3.5.7 en-us Ubuntu 12.04

EDIT While the question about cell formats still stands, turns out that on references I was partially wrong. Not being able to attach a file, I'll try to reformulate the question as best as I can. I have three columns: A (time), B (speaker name), C (speech duration). On each row except the first, time is obtained by adding cols A and C of the row above (eg. A2 = A1+C1 and so on). If I cut, say, the third speaker (B3:C3) and paste him/her elsewhere, all formulae in column A are unchanged. If I need to shift all speakers from (say) the third to the last down one row to make room for another speaker (cut B3:Clast and paste that one row down) then formulae starting with A5 downwards are "updated" (eg. A5=A4+C5 instead of A4+C4). This occurs only if I select from any given row to the last one. My question is, how can I prevent this behavior? I would like formulae to remain unchanged no matter what.

Sorry for the long and convoluted explanation. Thank you for your patience, B

edit retag flag offensive close merge delete

3 Answers

Sort by » oldest newest most voted

answered 2015-08-01 12:29:25 +0200

pierre-yves samyn gravatar image


Starting from these data in Sheet1:


For inserting Z between a & b:

  • Copy A3:C7, paste in another sheet (e.g. Sheet2) in A2
  • Type Z and duration in Sheet1.B3 & C3
  • Copy "saved" data in Sheet2, Paste in Sheet1.A4


edit flag offensive delete link more

answered 2015-08-01 17:09:20 +0200

barak gravatar image

Summing up the two very helpful suggestions I got.

  • Pierre-Yves' answer: basically, use
    copy rather than cut. It works, and
    also helps maintaining cell format.
    As far as I can tell, there is no
    need to paste in another sheet, just keep the data in the clipboard and
    paste back at the new location.
  • m.a.riosv's answer: use indirect/absolute references. In my case, formula would be = (OFFSET(INDIRECT(CELL("address"));-1;0))+(OFFSET(INDIRECT(CELL("address"));-1;2)) -- looks awfully convoluted but works perfectly and is perhaps a more elegant solution.

I learned a lot. Thanks to both for your time, B

edit flag offensive delete link more

answered 2015-07-30 02:25:59 +0200

m.a.riosv gravatar image

updated 2015-08-01 10:57:22 +0200

Usually if the formulas have the references set up properly using absolute/relative references, it's possible to do it, pasting with: right-click Paste Only - formula.

edited 20150801

After more detail in the question.

It's possible construct a formula that always refer a relative reference to their position:


always take the cell in the row before in the same column


does the same, but their limitation is to not move A1


also works, but in this case the limitation is to not move C1

But it's a heavy consuming function, maybe can delay a bit if you are going to use thousands.

edit flag offensive delete link more


Thank you for your answer but I wasn't asking about moving formulae. Sorry, my question wasn't clear enough and my assumptions were partially wrong. I tried to reformulate it, please see above.

barak gravatar imagebarak ( 2015-07-30 18:09:49 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2015-07-29 23:38:10 +0200

Seen: 1,878 times

Last updated: Aug 01 '15