It’s 30 bite-size tutorials, each focusing on an advanced formula or technique.
If you feel like your spreadsheet knowledge has plateaued after the VLOOKUP, then this is the course for you. You’ll learn everything from classics like the INDEX-MATCH through to more exotic advanced formulas like SUMPRODUCT or MMULT.
Best of all, it’s free! That’s right, this one’s on the house. 😀
Start today, and up your spreadsheet game over the next 30 days.
In this post we’ll look at how to calculate a running total, using a standard method and an array formula method. We’ll cover the topic of matrix multiplication (take a deep breath, it’s going to be ok!) using the MMULT formula, one of the more exotic, and challenging formulas in Google Sheets.
If you like video tutorials, here’s the one on MMULT:
It probably won’t surprise you to hear that I use Google Sheets to build financial/budget templates and track my incomings and outgoing, both at home and for my business.
The dashboards available through online banking sites are pretty rudimentary and don’t give me much insight into what’s happening with my finances, particularly over longer time frames.
I like using Google Sheets, as opposed to another third party service like Mint, because it’s fully customizable, it’s easy to use and I can share any spending or budget templates easily with my wife.
I’m not a financial expert, so I won’t be dispensing any financial advice here. I won’t opine on what you should or shouldn’t show in your spending and budget templates in this post, nor will I talk about what your financial goals should be or how to get there.
What I will do in this post however, is show you some useful techniques in Google Sheets that you can use for building your own budget templates. Techniques to make them more insightful and more helpful for reaching your goals. They are:
Data Studio is relatively new dashboard tool from Google, launched in mid-2016. It’s a superb tool for creating professional looking reports, easily and quickly, and it connects seamlessly to other Google data sources (e.g. Analytics, AdSense, Sheets, …).
Do you work with data outside of Google’s ecosystem though?
I’ll go out on a limb here, and say, yes, most likely you do.
Perhaps you’re a digital marketing analyst looking at Facebook, Twitter, Instagram, MailChimp data (etc…) for example.
Many of us work with other web services and that data has been unavailable or difficult to display in Data Studio (requiring a sub-optimal workaround of importing it into a Google Sheet and connecting that to Data Studio).
In a nutshell, the problem occurs because dates in Google Sheets are actually stored as serial numbers, but the Query function requires a date as a string literal in the format yyyy-mm-dd, otherwise it can’t perform the comparison filter.