Ask Your Question

How to paste only formulas in Calc without over writing non-formula cells [closed]

asked 2014-10-08 12:40:17 +0200

5auli gravatar image

I want to be able to copy and paste only formulas in case when I have e.g. several similar columns, rows or sheets containing different values of data. By formula here I mean something beginning with "=" and by values of data I mean numbers or text.

This situation may arise e.g. when analyzing data of comparable items like profits of different investment alternatives. After entering the formulas (or equations) for profit calculation and data for various items to different columns/rows/sheets one may notice that some of the formulas are incorrect. It would be convenient to correct the formulas only to one column/row/sheet and then copy and paste the formulas without over writing the data.

A simple example. Let's assume I have copied two cells, A1 and A2. A1 has a formula '=A2+1' and A2 has a number 2''. By pasting I want to replace the old formula of cell B1 '=B2+3' with the formula of cell A1 (with relative referencing) without replacing the content of cell B2 which has a number '4'. If I use the "'Paste Only' > Formula" what happens is that the content of B1 will be changed as intended to ''=B2+1' but the cell B2 value will be deleted.

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2016-03-04 20:04:22.003331


Sorry, I had neither the time nor the patience to study your own solution in detail. But ...

If your apparatus of formulae is not too complicated you may assign a name to every formula, at least to everyone that might undergo further development. Editing such a formula later with the help of the 'Insert' > 'Names' > 'Manage' tool this will affect all the occurrences at once.

Lupp gravatar imageLupp ( 2014-10-09 12:35:58 +0200 )edit

Thanks, I have to think about your proposal. Concerning my own solution (Oct 9th 2014), I am pretty confident that the principle is correct even quite complex. However, I did notice I made a minor mistake when I wrote it here. I think I will wait that I get enough karma to be able to add the screenshots so that it is more readable. Meantime I will study if I can simplify and reduce the steps required.

5auli gravatar image5auli ( 2014-10-09 17:25:51 +0200 )edit

The karma problem should be solved.

Lupp gravatar imageLupp ( 2014-10-09 18:44:46 +0200 )edit

Thanks Lupp, I rewrote my own solution with screenshots. I hope someone will find an easier way to achieve the same result.

5auli gravatar image5auli ( 2014-10-09 20:20:51 +0200 )edit

What are your urgent reasons to mix up data and formulae in the same column? If I had a somewhat database like task for Calc i mostly tried to have columns (or even complete sheets) containing DATAorFORMULAEexclusively (with the exception of a group of formula-rows on top of data columns). Thus I hadn't much to worry about your problem.

Lupp gravatar imageLupp ( 2014-10-09 22:10:52 +0200 )edit

In principle I agree. In ideal world at least. The need comes from the way how I often do analysis. In the beginning I believe I'm doing something small and simple. Then I add some parameters and formulas, then I add some more items to be analysed. After a while I decide to move each item to separate sheet believing the analysis model formulas are correct. And finally I notice that the formulas do need some tweaking - in each sheet. I know this is not ideal, but ...

5auli gravatar image5auli ( 2014-10-10 06:31:11 +0200 )edit

OK I well know the phenomenon you described. It seems to be ubiquitous, not only when using software. There are yet a few countermeasures as far as Calc is concerned. In most cases you can double the number of used columns and you will already have a sufficient separation of the data from the formula apparatus.

Lupp gravatar imageLupp ( 2014-10-10 08:57:03 +0200 )edit

2 Answers

Sort by » oldest newest most voted

answered 2014-10-09 06:57:56 +0200

5auli gravatar image

updated 2014-10-10 15:58:18 +0200

Note. If you want to display the formulas in the cells, like in the "images" above, proceed as follows: Choose Tools - Options - LibreOffice Calc - View. In the Display area mark the Formulas box. Click OK.

The following is my own answer

In left the normal calc view,in right the formula view.

The challenge is to copy only the formulas from the column A to column B without overwriting the content of cells that contain text or numbers. In the example the challenge is to copy only the cell A1 to B1 and save the content of the cell B2.

image description

The first step is to copy to a temporary location the formulas from the cells that contain formulas and values (text or value) from other cells. This is achieved by formula =IF(ISERROR(FORMULA(B1)),B1,FORMULA(A1)) in column C cells (naturally the 1 replaced by the respective row number).

image description

The second step is to convert the cell content to a string that can be edited. This is done by Copy + Paste Special > everything but Formulas. In the view on left it looks like nothing happended but the change is visible on right.

image description

The third step is related to a strange way how calc works. Even the D1 contains something that looks like a formula unfortunately calc recognizes it as a formula only after doing some editing to it. A save way to edit is to replace the equal sign with equal sign. Yes that is in effect no change but calc recognizes the edit. The way to do this is by normal Edit > Find & Replace "=" -> "=" to the whole colum D. Note how this time nothing changed on right view.

image description

The fourth step is to correct the relative reference. Instead of the original “A2+1” we should have “B2+1”. This can be easily achieved by copying the column D to column E.

image description

The fifth and final step is to cut and paste the column E to column A. Next we could delete the temporary columns C, D and E.

image description


edit flag offensive delete link more


1) Am afraid I did not understand the fifth step. Might you be ready to supply a Calc document containing 6 sheets: Sheet1 containing the first state and the following sheets containing the result of one by one steps?

2) The third step can be done with the help of the 'Data' > 'Text to Column' tool.

Lupp gravatar imageLupp ( 2014-10-09 21:59:35 +0200 )edit

1) Concerning the fifth step, it is done because the challenge was to copy only the formulas from the column A to column B. I will provide the example as a 6 sheet document later. Maybe today. 2) Concerning the third step, thanks, you are right. It can be done with 'Data' > 'Text to Column' tool. As a matter of fact I find both the 'Find & Replace' and the 'Text to Column' methods a bit quirky ways to circumvent the calc idiosyncracy.

5auli gravatar image5auli ( 2014-10-10 06:17:36 +0200 )edit

I have no access to Excel for about 7 years now. In earlier times I had it at hand for comparison. Excel also required special means to trigger the "recognition" process for cell contents. So did StarOffice.(the common ancestor of the open office suites) I knew since 1997. There was no "calc idiosyncracy".

Lupp gravatar imageLupp ( 2014-10-10 09:08:13 +0200 )edit

I added the calc document at the end my answer. Starting state on first sheet and each step on following sheets.

5auli gravatar image5auli ( 2014-10-10 16:01:35 +0200 )edit

answered 2014-10-08 15:51:32 +0200

ROSt52 gravatar image

I would do:

  • Select A1 and A2 and ctrl+c
  • click into B1
  • right click B1
  • select "Paste Special"
  • select Text, Numbers, Formula
edit flag offensive delete link more


Thanks for your answer but that is not what I asked. I try to be more clear with my question. Instead of editing my original question I write it as a new message.

5auli gravatar image5auli ( 2014-10-09 06:44:20 +0200 )edit

Question Tools

1 follower


Asked: 2014-10-08 12:40:17 +0200

Seen: 2,035 times

Last updated: Oct 10 '14