Using ARRAYFORMULA()'s in Row 1 of your Spreadsheet

I can’t take credit for this tip but I just found out about this trick/tip by reading a post here. So the “Normal” way of using an ARRAYFORMULA function is to use it in row 2 of a given column. You just have to be careful not to accidentally deleted it, or have a user delete it.

So the next idea was to put it in row 2 but not put any other data in the row and then hide or minimize that row. Basically your data for the non ARRAYFORMULA columns would start on row 3.

Then I saw the post I’ll link to below. Credit goes to @sardamit and @B_Y who had their own way of handling it. In both methods, both the column title and the ARRAYFORMULA are in row 1. Making it much harder to accidentally delete the accidentally. Just take care if you decide to change the column title however :slight_smile:
Here is a demo spreadsheet that shows how to format it, and put it to use with a simple formula. Make a copy and test it out.
Here are the formulas that are in row 1 of the above spreadsheet

={"Add Method 1";ARRAYFORMULA(IF(ISBLANK(A2:A),"",A2:A + B2:B + C2:C))}

=ARRAYFORMULA(IF(ROW(A:A)=1,"Add Method 2",IF(ISBLANK(A:A),"",A:A+B:B+C:C)))

Both work great, I guess it’s just a matter of which you like best visually and is easier for you to understand what is going on.

Here is the topic I gleamed this tidbit from: If user deletes 1st row, he loses array formula

Edit: One other tip is to delete all of the empty rows below any data you may have in the sheet that has those arrayformulas. Many a person, including yours truly, has had a “problem” that a new row if data that you can see in the app, but has somehow disappeared in the sheet. It hasn’t disappeared it’s just at the very bottom row, maybe row 1001, with 900 empty rows before it.

7 Likes

Thank you, George. :slight_smile: