The complete guide to simple automation in Google Sheets, using Macros

Macros are small programs you create inside of Google Sheets without needing to write any code. They allow you to automate repetitive tasks. They work by recording your actions as you do something and saving these actions as a “recipe” that you can re-use again with a single click.

Sound good?

They sure are! Read on to learn how to use them, see some examples, discover their limitations and also see how they’re a great way into the wonderful world of Apps Script coding 😀

This video is the third lesson from my free Apps Script Blastoff! course: an online course teaching Google Apps Script from scratch.

Contents

  1. What are macros?
  2. Why should you use macros?
  3. How to create your first macro
  4. Other options
  5. A peek under the hood
  6. More macro examples!
  7. Resources

Continue reading The complete guide to simple automation in Google Sheets, using Macros

How we manage our family finances with Google Sheets

“If you can’t measure it, you can’t improve it.” – Peter Drucker

Disclosure: Some of the links in this post are affiliate links, meaning I’ll get a small commission (at no extra cost to you) if you signup.

Trying to have conversations about saving, spending and planning for retirement is infinitely more difficult and more stressful without accurate numbers in front of you.

You fall back on anecdotes and feelings because you have nothing else to go on.

Conversations start with phrases like: “it feels like we haven’t spent much on eating out this month” and they don’t get any better from there.

My wife and I have two beautiful boys, aged 1 and 3, and we’re both ambitious with our careers and work full-time. Life is crazy, crazy busy for us right now.

We’ve found it challenging to find time to manage our family finances, so we’ve been in this position of flying blind without a financial tracking plan in place. We’ve had those frustrating conversations, knowing that if we had better insights into our financial habits we could do a much better job at financial planning.

I want to show you how we changed that.

How we created a system in Google Sheets for tracking our spending habits.

It now only takes us about 10 or 15 minutes each week, so we can focus on understanding our financial situation better, and maximize our saving.

Enter Tiller

Tiller is an amazing tool that connects our bank accounts and credit cards securely to Google Sheets (or Excel), and automatically updates them on a daily basis.

It means we can see all of our financial transactions in one place and do our own custom analysis in Google Sheets.

Tiller Google Sheets

It’s been transformative for our family’s sanity and helped us get on top of our spending and hit our saving goals.

Tiller has a suite of Google Sheet templates available too, covering spending, saving, budgeting and net worth tracking, so that you can visualize your financial data immediately.

Of course, you can also build your own solutions to answer whatever questions you have.

It costs $59/year, which is tremendous value since you’re getting a fully customizable, automated personal finance tool.

How to setup Tiller with Google Sheets

Tiller is a third-party tool so you have to create an account with them, which is done securely through your Google account credentials.
Continue reading How we manage our family finances with Google Sheets

Pivot Tables 101: A Beginner’s Guide

How to use Google Sheets A Beginner’s Guide to Pivot Tables in Google Sheets

If you use Google Sheets, or any spreadsheet application for that matter, but don’t use Pivot Tables, then you’re missing out on one of the most powerful and useful features available.

This tutorial will (attempt to) demystify Pivot Tables and give you the confidence to start using them in your own work.

Contents

  1. An Introduction to Pivot Tables
    • What are Pivot Tables?
    • Why use Pivot Tables?
    • How to create your first Pivot Table
    • Let Google build them for you

  2. Pivot Tables: Fundamentals
    • Rows, columns and values
    • Totals
    • Sorting

  3. Pivot Tables: Tips and Tricks
    • Multiple value fields
    • Changing aggregation types
    • Adding filters
    • Multiple row fields
    • Copying Pivot Tables

  4. Pivot Tables: Next steps

1. An Introduction to Pivot Tables

What are Pivot Tables?

Let’s see a super simple example, to demonstrate how Pivot Tables work. Consider this dataset:

Dataset for pivot tables

You want to summarize the data and answer questions like: how many apartments are there in the dataset? What’s the total cost of all the apartments?

Now, this would be easy to do with formulas, using a COUNTIF and a SUMIF, but if you change our mind and now want to summarize “Condo” you have to modify all your formulas, which is a pain.

Enter the Pivot Table:

Basic pivot table in Google Sheets

This took me eight mouse clicks and I didn’t have to write a single formula (in a few paragraphs I’ll show you those exact 8 clicks so you can build your own version).
Continue reading Pivot Tables 101: A Beginner’s Guide

Mapping data with Pivot Tables in Google Sheets…yes, really! 🌎

Let me show you a unique use case for pivot tables – building maps!

Pivot Table Map of Washington DC

Can you guess which city this is?

It’s Washington D.C. and it’s also a pivot table in Google Sheets. The image on the left is the map built with a pivot table. The image on the right is a screenshot of Washington D.C. from Google maps.

Look closely and you might just be able to see the Google Sheet row and column headings around the map.

Wait, what?
Continue reading Mapping data with Pivot Tables in Google Sheets…yes, really! 🌎

Format your Google Sheet tables so they look good GREAT

A collection of formatting tips for tables in your Google Sheets.

Google Sheets formatting for tables

Formatting Tables

Let’s start with a simple table, completely devoid of any formatting:

Table no format

Header rows

Go for bold, center-aligned and wrap the text, so it all shows.

Google Sheets formatting column headings
Continue reading Format your Google Sheet tables so they look good GREAT