What are Your Favorite Google Spreadsheet Party Tricks?
Here are some of the responses. You can share yours in the Tiller Money Community right here.
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.
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.
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 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.
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.
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!
Tom:
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!
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.
tim:
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 3
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.
Excited to be part of this community! Thanks Tiller team, I think this will be a super valuable resource for us all. Here are some of my favorite tips:
- Ctrl + \ (or Cmd on Mac) to remove all formatting
- Ctrl + Shift + V to paste as values
- Shift + S to create a new Sheet in your Drive folder
- Shift + F11 to create a new tab in your Sheet
- Type sheets.new into your browser to create a new Sheet
Hover over the column headers line to see a grab hand and drag that down to freeze the top row(s). Also works for columns.
Grab the base of the formula bar until you see the cursor change into a little double-ended arrow. Then click and drag down to make the formula bar as wide as you want.
The ArrayFormula, curly brackets {} and the QUERY function covered by Tim above are all phenomenal tools to have in your toolbelt.
Other functions worth learning are INDEX, MATCH, VLOOKUP, FILTER.
Knowing that spreadsheets store dates as 5-digit numbers. For example, today 9/13/19 is stored as the number 43721 (change the formatting to a number to see).
- Ctrl + ; inserts the current date into a cell.
- Double clicking on a date in a cell brings up the date picker.
(1) I like the Generate Reports under Add-ons/Tiller/Business. I create a P&L report every month which shows all the previous months in the year for comparison purposes.
(2) Also, I created a Portfolio tab that tracks all my investments by account, position, and lot using googlefinance formulae. It updates constantly throughout the trading day and lets me know instantly where I stand regarding every position I own, including my current paper gain/loss.
(3) I do not use it as often as I should, but the Yearly Insights tab is pretty powerful.
I use Tiller daily as it allows me to track my finances better than I could do before using Tiller. I have been a user for about 9 months.
