Graphic Designer: me
For a lot of people Google Sheets might look like a simple enough tool. You have a spreadsheet of cells, you can store things in said cells, and you can even reference things in other cells (and even other sheets!) if you’re feeling fancy. However, I’m here to tell you that Google Sheets is not a tool to be underestimated.
I’m an astronomy-physics major, and part of that is because I really like things fitting together. In the case of physics/astronomy this is more so in the form of physical systems, where net quantities are preserved and all parts of the system are interrelated in one way or another. On a more barebones level, though, this extends to the numbers just working out. This has extended, as of late, into me taking an interest in keeping a close eye on how I spend my money –as well as how I go about saving it.
My first “The Horror” sheet (as I’ve grown to refer to it) was relatively simple. There was one sheet for my checking account, and one for my savings account. In the checking sheet there was a column for expenses and deposits, another for the date, and another for where/what the transaction was from/for. There was also a cell that summed all the transactions in column 1 together, and thus gave me my total balance.
Pretty straightforward. Half the fun of a spreadsheet, though, is making it look nice. So some quick additions (seen above) were the use of alternating colors, conditional formatting highlighting a box yellow if it’s above a certain amount, highlighting a box gray and coloring the text purple if it contained a certain word (in this case “example 3”), adding a color scale on the balance (hence why it’s red), as well as changing the number format so it displays as a currency and all negative transactions are colored in red. Rummaging around I found a type of graph called a timeline graph, which –with the creation of a column keeping track of my balance over time– (seen below) can visually display my balance’s history.
Then with some rearranging and tinkering:
The real fun, though, was in making a sheet for my savings account.
It started off simply enough: I just want to be able to write how much I want to save in the span of two dates (whether that be in total or per month), and figure out how much I’d have to save in total each month. My first attempt was particularly disastrous:
A few things wrong (to put it lightly) were:
I was manually calculating how many months there were by dividing the amount of days by 365.25
I was accounting for months I might not be working by just multiplying the unscaled amount by some manually inputted and frankly somewhat arbitrary constant
I had no way of telling how much I actually had saved
I had no way of conveniently changing the date I wanted to look at the sheet from, as it was all set to Today()
It looked awful.
This is where my real descent into the depths started. So as to make this article readable and sensible and not just a multi-paragraph rant, I’ll try to make it as brief a list as possible:
There is a DATEDIF() function that lets you calculate how many days, months, or years have passed between two dates. This is built into Google Sheets (as is everything else that follows) and is as simple as saying =DATEDIF(start_date, end_date, “D/M/Y”)
There is an IF() function that, as it may suggest, lets you perform if-else statements as well as gain access to boolean logic in general. (AND(), OR(), NOT())
There is a SUMIF() function that lets you sum all the values in a range that match certain criteria (as defined in possibly a separate range from the first). This, for example, lets you add all the transactions that have the word “Grad School Apps” in it, letting you know how much you’ve saved for that specific thing. Another use would be to add up all the transactions that happened on a given date.
There is a CONCATENATE() function that lets you string strings together
There is an INDIRECT() function that lets you reference cells based off of text. This means that if you have the text “A2” in cell E4, and in cell A2 you have the word “cat”, INDIRECT(E4) = “cat”. This –in combination with functions like ROW() and CONCATENATE()– can be really handy.
There is an IFERROR() function that lets you output some given value if your formula runs up against an error. (i.e. maybe if you flipped the start and end date, you can output a specific message that tells you you’ve made that mistake.)
There is a GOOGLEFINANCE() function that can pull exchange rates on currencies as well as stocks (and a lot of other things I didn’t delve into), which can be quite convenient.
There is a Sort() function that lets you sort ranges of data according to a given criterion (which can be applied along a different range of data than the one you are sorting).
And more. So much more. You can even write scripts in an actual programming language if you want to. (Apps Script)
I’d also like to note that if you look up any given function on google (+ google sheets, of course), you’ll find a very handy and succinct help page for each one. This allowed me to get the hang of all of the above really quickly and easily.
So, with all of this in mind, the newly improved sheet looks like:
All the inputs this needs are how much you want to save, whether that’s a monthly or total amount, the two dates, and that’s all! Then the sheet does everything else automatically. There’s also a column to account for if you might not be working on a given month(s) (like winter break, for instance), as well as a cell that lets you pick from what date you want to look at the sheet from. Additionally, there’s a lot of extra stuff that I haven’t mentioned. For example, if the two dates you inputted aren’t even a month apart (think Feb. 13 and March 12), DATEDIF() would typically output a 0, so an exception has to be coded in (since saving over 0 months makes no sense). Then if there’s any months in between where I don’t intend on depositing money (primarily because I might not be working), then the amount of money I have to deposit every other month should scale automatically with that information, so that also has to be coded in. For when I visit home, it’d be nice to be able to convert the sheet (which is in USD) to MXN, so I added a setting for that in a separate sheet. I also ran up against an issue on whether or not the sheet should be date-inclusive or not (basically whether DATEDIF(1/20/2023, 2/20/2023, “M”) should equal 1 or 0), so I added a setting for in the same sheet as the currency conversion setting, and so on and so forth. (And that isn't close to everything, but it really isn't worth getting into in this article.)
All in all I ended up with this disaster:
Which, so far at least, is working as intended. This is great, since I never want to touch it again! (I’m mostly joking, it’s just that trying to fix that would be a not-insignificant amount of work if anything were to go wrong.) And don’t let this scare you off, there’s definitely better ways of doing this but —since I am still learning— this is the best I can do for now.
Now, obviously there’s plenty of pre-existing software and apps out there that are specifically meant for things like this (and the code up above isn’t exactly state of the art), but the point wasn’t to make the best thing in existence: instead it was to tackle this small, but fun, challenge I’d laid out for myself. To make it work, however that may be. Learning the ropes of a new piece of software can be a fun experience, and that’s what this was: just a fun venture into learning what one can do with Google Sheets. So think about it. It doesn’t have to be anything crazy, but if you ever want to learn how to use Google Sheets (or Excel, which is significantly more powerful, albeit less convenient) there are plenty of resources online that can help make that a surprisingly easy process. (And that last part goes for any major piece of software available today, really.)
Fun fact: If you ever want to tinker a little bit with a number format (Format -> Number -> Custom Number Format) that has different colors, colors 1-56 are ones that google sheets has available: http://dmcritchie.mvps.org/excel/colors.htm