The Tiller Money Community is a place for spreadsheet and personal finance enthusiasts to ask questions, collaborate, get support, and share their own Tiller-powered spreadsheet solutions.
Today we’re simply asking Tiller Money Memo subscribers "What is your favorite spreadsheet feature / shortcut / hack / party trick?"
Share yours below. You can also use this thread to ask questions and chat about the new Community.
Welcome Tiller Money Memo readers! We are so happy to introduce you to the new Tiller Money Community. We have big goals for deploying the Community to help you solve your personal finance and spreadsheet riddles, and support builders as they make great new solutions powered by Tiller.
To warm up, today we’re simply asking "What is your favorite spreadsheet feature / shortcut / hack / party trick?"
Mine is pivot tables, which I use to review my spending month to month. But I also love collaboration. I recently built a big camping packing list and meal planner in Google Sheets that I shared with a couple of families on a joint camping trip. Made everything super easy.
What’s yours?
In Google Sheets and Excel both, I love array formulas. With a big table of data, the idea that I can write a formula for a column once and the formula will populate down automatically is sweet. For example, I use an array formula to lookup and add category groups to my Transactions sheet after I assign a category. Happy to share that formula if others are interested.
Oh, and I will say - the new Google Sheet activity dashboard is a close second place. Gone are the days of wondering if someone saw my latest revisions. It’s so helpful to know when a collaborator was last in the sheet.
This is Google Sheets specific, but I ADORE the fact that I can set up a Google Form to seamlessly fill a Google Sheet with responses and eliminate tedious manual data entry.
Maybe not as exciting to someone who never experienced the joy of manually entering a ton of data into a spreadsheet over the course of an internship. 
Love those too. And to boot, they render really well on a mobile phone.
PowerQuery and PowerPivot are excellent tools used to automate data importing, scrubbing, and linking. This is especially useful when utilizing supplementary data, like custom spending categories. All it takes is a data refresh and BOOM–updated results!
I love using Macros under the “Tools” menu. You can record any of those tedious things that you do over and over again in a sheet and completely automate it. And then it’s fun to go take a look at the Google Apps Script that the macro created to automate your task - and you can then update the Apps script (under “Tools” -> “Script Editor”) with any additional custom items you’d like it to do. It’s fun to play with!
I’ll echo @peter regarding ARRAYFORMULA() but I’ll up the ante…
I’m a big fan of embedding arrayformulas in column headers so that a column header is rendered in the first row but a formula is applied to all rows below. This approach allows spreadsheet users to add and delete rows throughout the column without breaking anything.
In practice, a column header arrayformula may look like this:
=ARRAYFORMULA(IF(ROW(B1:B)=ROW(B$1), "Header Name", IFERROR(VLOOKUP(A1:A,'Other Sheet'!$A$2:$B, 2, FALSE))))
Randy
I’m interested in how you use that formula for categories!
One of my favorites in Google Sheets is =sumif or =sumifs - great for doing analysis and use it a lot here at Tiller for building metrics reports.
Ben Collins demos this nicely in our most recent webinar at about minute 20 when we’re working on building a cash flow dashboard from scratch.
There’s some great tips/tricks above, especially ARRAYFORMULA and writing a function that puts the column name in the top row.
I’ve got 2 favorites in Google Sheets. Version history can really help if I make a change and discover later that it didn’t work. I’ve also wiped out the content in a cell by mistake and Version history let’s me recover it. The ability to look back in time over the sheet is great.
I also try to remember to make a named version at key times with a useful name. For example, “Working version with better formatting”. It’s a simple Version Control System, a stripped down version of Git for users who don’t even know what Git is.
My other favorite is QUERY(). To be able to write SQL queries inside a spreadsheet is so powerful. Its such a quick way to add reports and views of your data. It’s well worth the time to learn how to use this function if you want to have greater control of how to slice and dice your data.
I totally overlooked Version history - that’s a feature I use almost daily. I use it so much it’s an invisible feature!
@randy stole mine, but you can also do “B:B” instead of “B1:B”. It’s great to not have to constantly fill-down on a formula. We use that in Tiller to have a column for coarse-grained categories based on the fine-grained category.
Specific to google sheets, I’m also a big fan of custom formulas and add-ons. You can write a complex formula in Javascript, and they call it from within a spreadsheet cell. I’ve built some custom reporting on year-to-date spend using that which has been helpful for tracking. I’ve also built an add-on for splitting Tiller transactions which pops up a dialog and allows you split a single transaction into multiple transactions, each with it’s own category.
ARRAYFORMLA() already has a lot of coverage here as a favorite, and I’m going to give QUERY() (and the Google Visualization API Query Language!) an honorable mention, then call some attention to the obscure-but-powerful partner to those two: array notation.
Deets here: https://support.google.com/docs/answer/6208276?hl=en
It’s super-easy to understand and use…but it doesn’t really start to shine IMHO until you combine it with those others.
OK, here’s a dirt simple (but mostly useless) example -
={"Portland"; "Seattle"; "Vancouver"}
(separated with semicolons) gives you those three cities in that cell’s column:
and, similarly:
={"Portland", "Seattle", "Vancouver"}
(separated with commas) gives you those three cities in that cell’s row:
I should have stopped here, in the interest of keeping my answer to Ed’s question simple, but as I am prone to do when spreadsheets are involved, when I have a lot of caffeine at lunch, and when I’m excited about something new and cool (like the new Tiller community), I feel a little like getting carried away. So apologies in advance.
Hold.My.Beer.
All that {} array stuff above was simple, and obvious, and maybe kind of cool – but big deal, though, right? WHY would I use this?
Well, it’s a LOT to swallow in one gulp if you’re not already comfortable using QUERY and ARRAYFORMULA, but turns out you can QUERY those {} arrays, like the formula below. This beast (I’ve seen a lot worse, though!) gives me the last 365 days’ debit transactions, and adds a formatted month and the start date of each week column, such that I can then reference the resulting data in pivot tables and charts, to see just exactly how much I spent in various categories or at various merchants over the last 365 days - without having to modify my Transactions sheet. In my case, it’s important for me to leave my Transactions sheet alone because like I said, I get carried away - and after about 5 years of working on it, my particular Transactions sheet is probably referenced by like 50 other formulae just like this one, scattered throughout about 10 sheets shared with my wife and a couple of my college-aged children – so if I messed with my Transactions source sheet, it would probably break a bunch of stuff I’ve already built on top of it and my wife and college age children would call me and ask me what I broke.
But I digress. (a lot, like I said). Here’s that formula that demonstrates one reason I appreciate {} arrays.
=QUERY({'Transactions'!A:L,
ARRAYFORMULA(TEXT('Transactions'!A:A, "YYYY-MM")),
ARRAYFORMULA('Transactions'!A:A - WEEKDAY('Transactions'!A:A))},
"
SELECT Col1, Col2, Col3, -1 * Col4, Col5, Col6, Col7, Col8, Col9, Col10, Col11, Col12, Col13, Col14
WHERE Col1 > DATE '" & TEXT(TODAY()-365, "yyyy-MM-DD") & "'
AND Col4 < 0
LABEL -1 * Col4 'Amount', Col13 'Month', Col14 'Start of Week'
")
Whew! (…hopefully someone chimes in soon with how to enter those carriage returns in a cell, otherwise that sucker gets REALLY hard to read. I love carriage returns almost as much as I love arrays. But I digress further.)
Adding something like this Month and Start of Week columns is why I’d use commas, and the following (simpler!) example shows why I might use semicolons. In this case, all I’m doing is combining my manually entered balance history data with my Tiller-fed balance history into a single sheet. The query isn’t strictly necessary in this case, but I use it here to limit the results to the last 365 days. My manually entered balance history (on a separate sheet, creatively named ‘Manually Entered Balance History’) has the exact same column format as Balance History, and includes such things as the value of my home and car, and the jar of coins I keep buried in my backyard*. Similar to the more complex case above, I have a ton of other ancient sheets that reference my Tiller-fed balance history sheet, and so don’t want to mess with that one directly.
=QUERY({'Balance History'!A:I;
'Manually Entered Balance History'!A:I},
"
SELECT *
WHERE Col1 > DATE '" & TEXT(TODAY()-365, "yyyy-MM-DD") & "'
ORDER BY Col1
")
*I don’t actually have a jar of coins buried in my backyard.
Wow @tim . Mind kind of blown by this.
Didn’t realize arrayformula, query, and arrays could be combined like this.
I see it lets you extend Google Sheet’s version of SQL so you can query from 2 or more tables (sheets) which i didn’t think was possible. Or 2 sections of a single sheet. No need to make a temporary sheet.
I tried using that Col1, Col2 formatting and see it looks like it only works if you use an array in the Query data section. Just using Col1 in a single, regular data set gave me errors.
It’s good to have this tool in the toolkit.
Great stuff, Tim!
You can enter line breaks in a formula with Ctrl + Enter in the formula bar 