Adding a constant in LibreOffice Calc

Question

How do you add a constant to a group of cells in LibreOffice Calc?

For example, here are four cells:

47 28 35 62

Now add 3 to each cell:

50 31 38 65

Solution 1

Even easier, but overwriting the original values:

• Copy the Constant using Ctrl+C
• Select the values that should get changed;
• Use "Paste Special" (Edit -> Paste Special or Ctrl+Shift+V), select "Add" as Operation:

Result: the values are replaced by the result of the addition with the constant.

Solution 2

There are different ways to do this. The easiest one would be to use the auto-complete feature. Another approach suitable for more complex operations involves the array formula feature.

Given a spreadsheet holding the four "source" values and the constant, the result should start at B3:

Do the following:

1. Select B3,
2. enter the formula =B1:E1+B2, don't finish using Return yet;
3. mark the function as array function:
   • finish editing the formula using Ctrl + Shift + Enter; or
   • open the function wizard, click "array function" and hit OK:

As result, in B3:E3 the result of the addition will appear. Since it's the result of an array, you can delete it only after selecting the complete result array. Changing just B3 won't work.

If you don't want the result to behave as an array, just copy the result array, select Edit -> Paste Special... (or Ctrl+Shift+V), deselect "Paste all" and all the other Selection options, and select just Numbers: