Add title row with ARRAYFORMULA in Google Sheets

Question

I watched a tutorial where the author uses an IF statement along with the ARRAYFORMULA function to add a title row to a column of data. Links are given to the docs; however, for an example of how to use ARRAYFORMULA see this answer.

An example can be seen below:

I was able to populate the C column by placing the following formula in C1:

=ARRAYFORMULA(if(row(A:A) = 1, "spent", B:B - A:A))

I'm confused about the syntax. I understand that X:X references the entire X column but I don't understand how it's being used to check if we're at cell A1 in one context and then being used to apply mass formulas in another context.

• How does the above line work?
• Can you illustrate with some examples?

Solution

It sounds to me that the information you learned led you to expect that row(A:A)=1 translates to row A1?

It works a little different than that, the syntax as your using it now, is basically saying if any row in A:A has a value of 1, then write "spent" else subtract B-A

My suggestion:

use a literal array to make your header, then use the if(arrayformula) to only populate rows with values, for aesthetics:

Example:

={"Spent";arrayformula(if(isnumber(A2:A),B2:B-A2:A,))}

Explanation:

The {} allow you to build a literal array, and using a semicolon instead of a comma allows you to stack your cells vertically, following that we check if there is a value in column A, if so, subtract A from B, else leave it blank.

Comments

[mbigras]

+1 for how you handle the 0s! I've been using: if((B:B - A:A) > 0, B:B - A:A, "") but I like your way with isnumber better :)

[Aurielle Perlmann]

thanks :) Also btw the reason i technically leave just a trailing comma , instead of ,"" is only because ,"" techinically leaves an occupied blank cell, but for some reason leaving only the comma doesn't - i forgot to explain that in my answer

[mbigras]

I noticed that also, have you come across situations where the empty string "" will get you into trouble? I think it would be worth adding to the answer :)

[Aurielle Perlmann]

yeah, from time to time - it especially can throw off counta's when trying to count values,since it will technically count the blank strings as 1

[Splic]

Your post should be an answer rather than a comment. However, the reason is that sorting the table, inserting a row between 1 and 2, or otherwise moving the formula will result in the cells above it not being populated.

[ProNotion]

Thanks so much for sharing this as it is something that has bugged me for ages. I've always ended up having to put an unnecessary extra row under my headers for the ARRAYFORMULA