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?

Answer 1

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.