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
```

Super User is a question and answer site for computer enthusiasts and power users. It only takes a minute to sign up.

Sign up to join this community
Anybody can ask a question

Anybody can answer

The best answers are voted up and rise to the top

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.

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:

- Select B3,
- enter the formula
`=B1:E1+B2`

,**don't**finish using`Return`yet; - 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`

:

- finish editing the formula using

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`

:

Also, there is a keyboard shortcut for the method described by wian. You need to highlight the constant reference and press `Fn` + `F4` (on a laptop keyboard, maybe just `F4` on a desktop).

It will automatically mark each position identifier with the `$`

sign, which turns the whole thing into a constant. A simple keyboard shortcut to save 2-3 seconds of time. It works for both MS Excel and LibreOffice Calc.

An additional solution that also works in Office Excel:

Mark the constant reference with `$`

for each position identifier:

$B fixates the column,

$2 fixates the row.

Now you can drag the formula to the right and only the first value updates while dragging.

Original 47 28 35 62

constant 3

result 50 *(=B1+$B$2)* 31 $(=C1+$B$2) 38 *(=D1+$B$2) 65 *(=E1+$B$2)*

Not the answer you're looking for? Browse other questions tagged ask your own question.

or