The '$' (to declare an "absolute reference") becomes effective when you *copy* a formula, not when the formula is moved due to adding (or deleting) a row and/or column.

Let's take your example with 450 formulae / column pointed at a single cell. You now need to add another row, and all 450 formulae become invalid, because the cell that they point at has moved but their references remained static. What if you have 50,000 of them? Do you fancy changing every one by hand?

Copying formulae from one cell to other cells as you build a spreadsheet is common, and the absolute referencing is designed to facilitate that. On the other hand, almost no-one wants the referencing to remain static when rows/columns are added/removed.

Here is some extra when the menu option:

`Menu`

/`Tools`

/`Options`

/`LIbreOffice calc`

/`General`

- `Expand references...`

is selected (or not):-

`=SUM($A$5:$A$9)`

is placed in `cell A1`

(number) `1`

is placed in cells `A5`

,`A6`

,`A7`

,`A8`

,`A9`

A1 = 5

- formula: =SUM($A$5:$A$9)

Default (`Expand references...`

is NOT selected):-

Add a row at A5:

- A1 = 5
- formula: =SUM($A$6:$A$10)

(or) Add a row at A10:

- A1 = 5
- formula: =SUM($A$5:$A$9)

Non-Default (`Expand references...`

IS selected):-

Add a row at A5:

- A1 = 5
- formula: =SUM($A$5:$A$10)

(or) Add a row at A10:

- A1 = 5
- formula: =SUM($A$5:$A$10)

If this helps then please tick the answer (✔).