Note: this content is for Google Sheets only.
Here at Tiller, we populate a number of columns automatically with your bank data each day, but what if you want a custom column with your own formula that runs alongside these transactions?
One option is to write the formula in row 2 (just below the header) on your Transactions sheet and then copy/paste or drag it into the entire column. That works for historical data, but tomorrow Tiller will insert new rows into your Transactions sheet, and those rows won’t carry that formula forward.
Using Google Sheets ARRAYFORMULA magic
With a little bit of effort, and a couple handy tips, you can adapt many custom formulas into something that can be automatically and neatly expanded into every current and future row in your Transactions sheet using ARRAYFORMULA.
As an example, Tiller's data feeds automatically populates a ‘Week’ column that uses Sunday as the start date of the week. With this data you can easily create pivot tables based on weekly spending, but what if you want the start of your week to be Monday? You’ll need to write your own formula.
It’s a common scenario, and thankfully it’s easy to use Google Sheets’ ARRAYFORMULA to fill this alternate week start column. We’ll want to place the formula into the header row and tweak it a bit so that the data is displayed in a way that makes sense.
Setting up the Google Sheets ArrayFormula for calculating a Monday week start date
First, enter this formula into the header row for a new column where you want to see the “alternate week” start date, where the Date is in column A.
=ARRAYFORMULA($A:$A-WEEKDAY($A:$A,3))
At this point we also want to format the column to display the data as a date. Click the column letter header and then open the format menu and choose Format > Number > Date. If we left it here, we’d have the data, but now the header row says ‘#VALUE’:
Google Sheets ARRAYFORMULA in action!
To amend this, we can use an IF statement to program the array formula to know that if we’re looking at something other than a number we want to display “Alt Week”. The dates in Column A are numbers, but the header row value for column A, ‘Date’, is not a number.
=ARRAYFORMULA(IF($A:$A <> “Date”, $A:$A-WEEKDAY($A:$A,3), “Alt Week”))
However, if we scroll down to blank lines in the spreadsheet we see that some random date is being calculated for each empty row. To remove those extra values we’ll use another IF statement that adds an empty string in anywhere that column A was also blank:
The ARRAYFORMULA calculates dates on the blank lines.
=ARRAYFORMULA(IF(ISBLANK($A:$A), “”, IF($A:$A <> “Date”, $A:$A-WEEKDAY($A:$A,3), “Alt Week”)))
Voilà! We’ve now got an Alt Week column with no blank lines and that won’t break if we insert rows at the top.
A small final tweak is to adjust the formatting of the header row of the new column to match the other header rows. You can quickly do this by selecting one of the other header rows, click the “paint format” button and then click the new header.
A few things to keep in mind when using the ARRAYFORMULA:
You still won’t be able to edit calculated values on an individual row. In other words, in this example, if you wanted to change the week, your only option is to change the data in column A.
The Google Sheets 2 million cell limit kicks in faster as you add columns, so if you plan to use this extensively consider using ARRAYFORMULA on a separate sheet that also uses a QUERY or IMPORTRANGE to pull only a portion of the raw data (just one year of data for instance).
Inserting new columns or changing the order of the columns can be problematic so make sure you insert the column in a good spot when you’re setting it up the first time.
The Google Sheets ARRAYFORMULA is incredibly powerful. You could write one that displays a category in all caps to make charts look nice, set up some year-month formatting for pivot tables or charts and pretty much anything else you can imagine. The applications are countless!