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.

Answer 2

why not just put the column title directly on the first row cell, and start the array formula from the 2nd row, using the A2:A, B2:B syntax?

If something does not have to be in a formula, better put it directly on the cell - simpler for others to understand what's going on, and the formula will be simpler.

Answer 3

If you put the array formula in line 2, and someone sorts the data, then the arrayformula will move. If it is in the header line, this is less likely to happen.

You can also use the IFS function to achieve a similar effect to the array,

=arrayformula(ifs(row(A1:A)=1,"Spent",A1:A="",,True,B1:B-A1:A)

Here the first condition checks the row number, and if it is row ONE, then inserts a Column Header.

The Second condition - A1:A="",, - ensures that blank lines are ignored.

The Third condition True (ELSE) performs the calculation.

This method also allows for different calculations to performed on different rows depending on requirements.