How to disable autoincrementation from single cell?

English
#1

Is there a way to configure LibreOffice Calc, so that it won’t autoincrement a value if it is dragged from a single cell?

I have a spreadsheet with columns containing either values entered by hand (which are initially zero) or formulas.

A B C D E
0 300 200 0 500
0 100 500 0 300

When I now select last row and drag it down (so that formulas are copied with incremented rows), I end up with the result:

A B C D E
0 300 200 0 500
0 100 500 0 300
1 300 600 1 200
2 400 300 2 100

Afterwards I always have to manually go through the cells and replace autoincremented values with zeros.

Is there a way to disable this behavior?

IMO values should be autoincremented only if I explicitly define the rule, eg.

0 1

Into

0 1 2 3

But

0

Into

0 0 0 0

I just checked, Microsoft Excel behaves exactly this way.

#2

Use Ctrl+Pulldown.
You can also select the range including that cell down and hit Ctrl+D

1 Like
#3

Will that still increment ranges in formulas? I mean, I expect that 0 is extended to 0, but eg. =$'2023'.E47 is extended to =$'2023'.E48

#4

Just try it! Start drag, hold Ctrl, drop…

And Welcome!

#5

If you don’t want cell references being adjusted while being pulled down or copy-pasted then use absolute addressing, like =$'2023'.E$47 for an absolute row reference.
See Addresses and References, Absolute and Relative.

#6

If I select your data down, I get exactly this:

A B C D E
0 300 200 0 500
0 100 500 0 300
0 -100 800 0 100
0 -300 1100 0 -100
0 -500 1400 0 -300
0 -700 1700 0 -500

Note that I selected the two rows, and dragged these down, thus providing an indication of the change in data. I obtain the behavior you describe if I only select the last row to drag down.

I am using 7.5.2.2. It is always advised to mention the version you use when asking a question.

1 Like

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.