I need help with Google Sheets. We are using a sheet as a working file and new rows will be added by different users. Formatting and formulas are included in various columns.

How do I ensure the new rows will contain the required formatting and formulas?

1 Answer 1


to ensure retaining of formulas there is a cell / row / column lockdown future which is invoked by F4 right after you type a cell reference. this will add $ symbols before column and/or row. there are 4 possibilities for how to refer to a cell:

type something into cell B3 and paste in cell E1 one of these:

  • =B3 (this will refer to B3 but by adding *row+column it will refer to C4)
  • =$B$3 (...by adding *row+column it will refer to B3)
  • =$B3 (...by adding *row+column it will refer to B4)
  • =B$3 (...by adding *row+column it will refer to C3)

*meaning of row+column - (adding a row between rows 1 and 2) + (adding a column between column A and B)

to ensure formatting to be not broken by inserting row+column you will need to pre-format them with or without using Conditional formatting

to ensure formulas will do what it should by inserting row+column you will need to use =ArrayFormula() which counts for inserting row+column

  • Can you expand on how you make sure the formatting isn't broken? For instance, I have a row in which I see one column spanning several columns, but when I try to insert a new row, it doesn't span these columns. (And I don't know what you mean by "pre-format them with or without" which seems to be meaningless if it can be either one)
    – Michael
    May 22, 2020 at 23:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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