I recently updated LO to version 24.8.3.2. Now in Calc, when I insert a “row above” an existing row, the cells in the new row do not take the formatting of the corresponding cells in each column to the row above. This is different from previous versions of LO Calc, and for me, makes adding a row a nightmare.
When you Insert Rows Above, the formatting comes from the row above the inserted row, not the one below. It has been like that since Insert Row was the only option.
.
If the row above is formatted with a number format but contains text, then the formatting will still be there, just not visible. This can be achieved by formatting the entire column or by including the heading row in the formatting.
FormattedRangeVsFormattedColumn.ods (19.0 KB)
Exactly. Maybe I didn’t make my comment clear. I have been using Calc for many years. I frequently Insert Rows Above and, as you stated, the new row gets formatted from the row above. However, that is not happening now. Under my LO 24.8.3.2, the new row is not being formatted from the row above the inserted row, as I have been experiencing for many years. In fact, it seems that the inserted row is not formatted at all (except, perhaps, a default format).
OK. Perhaps I’m using wrong terminology when I speak “formatting.” In my Calc spreadsheet, I have several columns in which the cells contain mathematical functions. In previous versions of Calc, whenever I Inserted Rows Above, the mathematical functions in cells of the inserted rows automatically “adjusted” for the added row. For example, Row 2 Column C sums Row 1 Column C plus Row 2 Column B; subsequent rows follow that pattern. Inserting a row should (and used to for me) adjust the math function to continue that mathematical pattern.
That is different. Click Tools > Options LibreOffice Calc > General and tick the box Expand references when new columns/rows are inserted
OK. Yes, I always do that.
If you click Data > Calculate is AutoCalculate ticked or highlighted?
I modified your spreadsheet to make Column C add cells as I described. Then I add a row above Row 12 (creating a new Row 12). As you can see, the calculation in Column C did not adjust to the added row.
FormattedRangeVsFormattedColumnRev1.ods (24.6 KB)
It did, the reference in C13 changed from =SUM(C11;B12) to =SUM(C11;B13) keeping the calculation the same.
To make the formula the same as the cells above, you can
- drag the formula down from cell C11, drag it down to overwrite cell C12 & C13
- Copy one of the cells above and paste it into C12 & C13
- select cells C11:C13 and press Ctrl+D
1 Like
Well, of course, I could manually “fix” the calculation error as you say. That is not the issue. Such a “manual fix” was not necessary on previous versions of Calc - and should not be necessary now. The calculations in Column C of this spreadsheet are very very simple and basic. In my engineering spreadsheets, the calculations are much much more complex, and often involve multiple columns (making the corrections – as I originally stated – a nightmare). In previous versions of Calc, the solution at which you derived using a manual procedure happened automatically whenever a row was inserted.
Anyway, thank you for trying!!
Using LO 25.8.3, I open your file, sheet “NumberRowsOnlyFormatted”
- copy some row of data from columns A:F
- select any row within or directly below the data range
- insert a row of new cells
- paste-special, values only
Result: formatted numbers exactly as in the copied row.
btw: you do not use any cell styles.