The Complete Guide to Simple Automation using Google Sheets Macros

Google Sheets 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 😀

Contents

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

Continue reading The Complete Guide to Simple Automation using Google Sheets 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 in Google Sheets: A Beginner’s Guide

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 in Google Sheets and give you the confidence to start using them in your own work.

Contents

  1. An Introduction to Pivot Tables in Google Sheets
    • 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 in Google Sheets

What are Pivot Tables?

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

Dataset for pivot tables in google sheets

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).

This Pivot Table summarizes the data for each property type. It counts how many of each property type is found in our dataset and then totals up the sales prices, to give a total sales price value for each property type category.

For example, the seven rows of data for Apartments are combined together into a single line in our Pivot Table (click to enlarge):

Explanation of Pivot Tables

In technical parlance, the Pivot Table aggregates our data.

Why use Pivot Tables?

Pivot Tables in Google Sheets are unrivalled when it comes to analyzing your data efficiently.

They’re flexible and versatile and allow you to quickly explore your data.

Pivot Tables in Google Sheets are generally much quicker than formulas for exploring your data:

This is lesson 3 of the Pivot Tables in Google Sheets course — a comprehensive, online video course covering Pivot Tables from beginner to advanced level.

How to create your first Pivot Table

Let’s create that property type pivot table shown above. I promised you eight clicks, so here you go:

1. Copy the data from this sheet into your own blank sheet (this doesn’t count towards the 5 pivot table clicks, ok?)

2. Click somewhere inside your table of data (I’m not counting this click 😉)

3. Click the menu Data > Pivot table... (clicks one and two)

This will create a new tab in your Sheet called “Pivot Table 1” (or 2, 3, 4, etc. as you create more) with the Pivot Table framework in place.

4. Click Rows in the Pivot table editor and add Property Type (clicks three and four)

Pivot Table editor

5. Click Values in the Pivot table editor and add Property Type (clicks five and six)

Pivot Table editor

6. Click Values in the Pivot table editor and add Sales price (clicks seven and eight. Boom! 👍)

Pivot Table editor

Here are the steps in sequence:

Creating pivot tables

And that’s it!

Eight clicks and you have a summary report of your dataset that gives you fresh insights into your data.

Now granted this is a super simple dataset, but even if we’d had hundreds, thousands or tens of thousands of rows of data, it would still be the same eight clicks to create this Pivot Table.

Let Google build Pivot Tables for you

Leverage the power of Google Sheets’ built-in AI!

When you create your Pivot Table, you’ll notice that Google automatically suggests some pre-built Pivot Tables for you in the editing window:

suggested Pivot Tables

With a single click you can then create a Pivot Table:

suggested Pivot Tables

It’s a neat way of quickly building them out as a starting point, and if it happens to answer your questions then even better.

Google Sheets also has the Explore tool, which can build Pivot Tables automatically for you. You can access the Explore tool from the star shaped button in the bottom right of your Google Sheet:

Explore button in Google Sheets

This opens the Explore window, where you can select from the suggested Answers (1) or even access a suggested Pivot Table (2). What is suggested and shown here is dependent on your data.

Explore tool in Google Sheets

Clicking on a suggested answer will take you to a second window from where you can insert your automatically-generated Pivot Table (3):

Explore tool in Google Sheets

I would absolutely still advocate learning how to build your own Pivot Tables however.

Even if you intend to always use the automatic Pivot Table builder, it’s still a good idea to understand how they work so that you know what the data is showing you.

So let’s take a look at building Pivot Tables in Google Sheets in more detail.

2. Pivot Tables: Fundamentals

When you create a Pivot Table from a table of data, all of the columns from the dataset are available to use in your Pivot Tables.

Rows, columns and values

At the heart of any Pivot Table are the rows, columns and values.

Rows:

Pivot Table rows

When you click Add under the Rows, you’re presented with a list of column headings from your table. When you select one, the Pivot Table will add all of the unique items from that column into your Pivot Table as row headings. Recall from the example above, it takes all the rows of property data and squashes it down to just four rows, which are the four unique property types we see.

Columns:

Pivot Table columns

Adding Columns produces the same effect as adding Rows, but in for the columns. The Values data is displayed in aggregated form, for each column.

Values:

Pivot Table values

When you click on Values, you’re presented with the same list of column headings. When you select one, you’re telling the Pivot Table to summarize that column. For example, if it’s a list of revenue you might want to sum it up, or average it. Or, if it’s a column of text values, you may want to count them. This is what happens when you add values: the data is summarized, i.e. all the individual values from each row are combined together into single value (they’re aggregated).

If you have anything in the Rows section of your Pivot Table, the aggregation will be done at that level (e.g. if we have property types in Rows, the Pivot Table will display the aggregated values for each Property Type).

You can also drag-and-drop the fields in your Pivot Table to easily move them around, for example from Rows to Columns, as shown in this GIF:

Rows and columns in Pivot Tables

Totals

You can easily toggle totals on or off for any of the Values columns in your Pivot Tables.

The button is in the Rows section:

Pivot Tables totals

Sorting

The sort options are found in the Rows section of your Pivot Table.

Each Row field can be used to sort with, and each one has their own sort options.

First, choose which Row field you want to sort with under the Sort by menu option (1). Then you’ll have a choice of sorting by the category field itself or any of the value fields that have been aggregated for this category column (2). This image shows this:

Pivot Table sort

The second option for sorting data is Order (3) where you specify whether you want it ascending or descending (4):

Pivot Table sort ascending or descending

In this example, I’ve elected to sort by the second column, the SUM of Sales Price, and then chosen to sort descending so the whole table is sorted so that the largest values in this column are at the top and the smallest values at the bottom.

3. Pivot Tables: Tips and Tricks

To show you a few more tricks with Pivot Tables, we need an extra column in our data table:

Pivot Tables dataset 2

Grab a copy of this dataset here (File > Make a copy…).

Multiple value fields

So far, we’ve just looked at a single values column, showing a sum of the sales prices.

However, you can add more value columns. For example, you might want to count how many properties are in each category or what the average sales price was for each category.

Click Add in the Values section of the editor to add as many value columns as you want:

Pivot Table values menu

By adding the property type column (which is all the text values in our original data), the Pivot Table will default to counting the number of times each property type occurs (using the COUNTA function).

Pivot Table with two values

You can drag the values fields to rearrange the order of the columns in your Pivot Table.

Changing aggregation types

You can add the Sales Price field again, so that you have it twice in your Pivot Table. Initially it’ll default to SUM in both cases, giving you identical total columns. Not particularly useful.

However, you can change the aggregation type of one of the columns, e.g. change the SUM to AVERAGE instead, and then you’ll get fresh insights:

Pivot Tables change aggregation

The aggregation options are accessed by clicking where its says SUM (or AVERAGE or whatever you’ve selected) and then choosing from the menu:

Pivot Table aggregation menu

The Pivot Tables in Google Sheets course goes into a lot more detail about the different aggregation options.

Adding Filters

Pivot Table filters are conceptually the same as ordinary filters we use with our data.

We add a filter to show only a subset of our data based on some condition. For example, maybe you want to only see data from 2018, or just the month of September, or from Region A, etc.

They’re easy to add and use in Pivot Tables.

It’s the last section of the Pivot Table editor that we haven’t talked about yet.

Consider this example showing a count of properties for each property type:

Pivot Table with no filter

It shows all 15 properties from the original dataset.

Add a filter by clicking Add in the Filter section:

Google Sheets filter menu

In this example I’ve chosen the Agent column to add as my filter. This means that I want to choose to look at the data from just one of the Agents in my Pivot Table, i.e. filter down to show only that Agent’s data.

To filter on Jenny only for example, click on the “Status > Showing all items” and uncheck the items you want to discard. Whatever is left selected (shown with a tick) is the data that will be used to create the Pivot Table.

Pivot Table Filter menu

The output shows only the six properties for which Jenny was the agent:

Pivot Table with filter

Looking back at the data, what’s happening is that the Pivot Table is only including the rows of data related to Jenny in the Pivot Table, i.e. these six rows:

filter data

Multiple categories

(Note: I’ve taken off the filter for this exercise.)

What happens when we add multiple fields to our Rows section? Let’s see.

The first field you add shows creates a unique list of items from that column. When you add a second row field, it appears as sub-categories, so that between the two columns in your Pivot Table, all the unique combinations of the two fields are shown.

Swapping the order of the row fields, by simply dragging and dropping them in the Pivot Table editor window, will swap the order of the categories.

For example, you can summarize the breakdown of property types for each Agent, or you can summarize the sales by each Agent for each Property Type.

Pivot Table categories with sub-category

Like everything with Pivot Tables don’t be afraid to just experiment here. Try different combinations and take a moment to understand what the Pivot Table is showing with each combination.

Copying Pivot Tables

Oftentimes you’ll find yourself wanting to replicate a Pivot Table, perhaps as a starting point for further data exploration.

There’s a quick trick for copying an existing Pivot Table, rather than starting over. It also gives you the option of moving your Pivot Tables to a different tab.

Click into the top left corner cell of your Pivot Table and click copy (Cmd + C on a Mac, or Ctrl + C on a PC/Chromebook). This adds the Pivot Table to your clipboard and you can paste it wherever you want in your Sheet (Cmd + V on a Mac, or Ctrl + V on a PC/Chromebook).

Note: You need to ensure there is enough space available wherever you wish to paste a copy of your Pivot Table (i.e. enough empty cells) or you’ll see the #REF! error.

4. Pivot Tables: Next steps

The nice thing with Pivot Tables in Google Sheets is that they’re really easy to experiment with. Just try adding different fields in different parts of your Pivot table editor to see what effect it has. You can always start over!

🔗 Google documentation on how to create & use pivot tables

🔗 Google documentation on how to customize a pivot table

🔗 Check out my online training course, Pivot Tables in Google Sheets course, for a complete look at Pivot Tables, from beginner through to advanced level.

🔗 Keep up-to-date with new articles, course launches and exclusive offers, by signing up for my Google Sheets newsletter, and get my free 80-page ebook on Google Sheets tips.

🔗 If all else fails, ask for help on the Google Sheets forum.

🔗 A novel use case: Pivot Table Maps!

Well, that’s it for this tutorial! Happy pivoting!

Pivot Table Maps in Google Sheets…yes, really! 🌎

Let me show you a unique use case for pivot tables – Pivot Table 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 Pivot Table 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?

I downloaded a dataset of DC crime data, which had latitude and longitude data, and plotted it as a pivot table with conditional formatting.

Pivot Table Maps of Washington DC

The dark areas in the pivot table map represent Rock Creek Park (huge wooded area with few people, hence few crimes) and two rivers, the Anacostia, which runs through the SE of the city, and the Potomac river, which delineates the W edge of the city.

Annotated Pivot Table Map of Washington DC

And here’s a close up of the Sheet, to convince you it’s really a pivot table (click to enlarge) 😉

Pivot Table map in Google Sheets

I first saw this technique implemented in Excel (see this example) but I haven’t seen anything in Google Sheets before.

How to make Pivot Table Maps

It’s actually pretty simple and can be performed on any dataset with latitude and longitude data, to varying degrees of success (some datasets are too large, some not granular enough).

  1. Download your chosen dataset.
  2. Round your latitude and longitude data columns to 1, 2 or 3 decimal places (depending on how granular your data is, trial and error).
  3. Highlight the whole data table and create a pivot table.
  4. Insert latitude as rows.
  5. Sort the latitude rows as descending, since larger positive numbers are further North.
  6. Insert longitude as columns.
  7. Highlight all the longitude columns and reduce the column width of all of them in one go, so all the cells are square.
  8. Insert your measure into the values section of your pivot report builder, in the DC map example above I inserted “Offense” and used COUNTA to count them.
  9. Highlight whole pivot table and choose Conditional Formatting –> Color Scale (I chose variations of yellow).
  10. Change the background of the pivot table to black.
  11. Zoom out on your Sheet as far as you need to see the full “map”.

Want to see this template?

Click here >>

Feel free to make your own copy (File > Make a copy…).

When could we use Pivot Table Maps?

It’s a quick and dirty way of visualizing geographic data sets.

It’s not really suitable for deep analysis or final charts, where a tool like Tableau would really prove it’s worth, but for a quick look with only a few minutes work, it’s not too bad!

Here’s a few more Pivot Table Maps of my own:

A pivot table map of 7,000 breweries and brew pubs in the USA:

A pivot table map of breweries and brew pubs in the USA

Houston, Texas as a pivot table map, created from 311 data:

Houston pivot table map

Subway stations of New York City pivot table map:

NYC Subway pivot table map

Let me know if you try this yourself! I’d love to see any creations you come up with.

How To Make a Table in Google Sheets, and Make It Look Great

A collection of formatting tips for how to make a table in Google Sheets.

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

Table no format

How to Format a Table in Google Sheets

The goal with this article is to show you how to make a table in Google Sheets look great, like this:

How to make a table in Google Sheets and make it look great

Header rows

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

Google Sheets formatting column headings

Aligning columns

Let’s align those columns, they’re messy!

Table alignment GIF

Center column headings, ID numbers, or other standardized entries.

Left align text.

Right align numbers (which they are by default). Really the only exception to this rule is for numbers that are not really numbers, i.e. they’re not being compared against each other or being used in any sort of calculations. For example, the ID numbers above can be center aligned.

Right align dates (which they are by default). However, if you’re working with just a year, as in the example above, you can get away with center aligning, just be consistent.

Formatting data

Choose appropriate formatting options for the data in your tables.

Add thousand separators to big numbers above a thousand. Add currency signs to financial numbers to add context.

Choose an appropriate number of decimal places. For example 2 decimal places if you need that level of detail to show cents on the dollar, but remove decimal places if they’re not needed for large numbers:

Google Sheets formatting format as currency

I could stop the article here, as the table is now sufficiently formatted to maximize legibility.

However, for tables that are part of dashboard reports or are being presented to clients, you can add colors to match your branding, or bring additional context to your numbers.

How to make a table in Google Sheets with Alternating colors

Sure you can do this manually, but it’s way easier and quicker to do with the Alternating Colors tool under the formatting menu.

Simply highlight your whole table and then open up the Alternating Colors option sidebar. Select the color scheme you want, whether you have a header or footer row, and even choose custom colors if you wish:

Google Sheets formatting alternating rows

How to make a table in Google Sheets with colors and indicator arrows

Hidden in the Custom Number Format menu is a conditional formatting option for setting different formats for numbers greater than 0, equal to 0 or less than zero.

It’s a great tool to apply to tables in your Google Sheets dashboards for example, where the data is changing. By changing the color of a table cell’s text as the data changes, you can bring it to the attention of your user.

Consider the following sales table which has a % change column:

Google Sheets percent table

Now take a look at the same table with colors and arrows added to call out the % change column:

Google Sheets table

It’s significantly easier/quicker to read and absorb that information.

How to add this custom formatting

1. Somewhere in your Sheet, or a new blank Sheet, copy these three CHAR formulas (you can delete them later):

Google Sheets Char formula

=char(A1)

=char(A2)

=char(A3)

Now, copy and paste them as values in your Sheet so they look like column C and are not formulas any longer.

(You copy as values by copying, then right clicking into a cell and select Paste special > Paste values only…)

You’ll need to copy these to your clipboard so you can paste them into the custom number format tool.

2. Highlight the % column and go to the custom number formatting menu:

Google Sheets custom number format menu

3. Change the 0.00% in the Custom number formats input box to this:

[color50]0% ▲;[color3]-0% ▼;[blue]0% ▬

as shown in this image:

Google Sheets custom number format

What you’re doing is specifying a number format for positive numbers first, then negative numbers and then zero values, each separated by a semi-colon.

Copy in the symbols from step 1 (you’ll have to do this separately for each one).

Use the square brackets to specify the color you want e.g. [color50] for green.

Read more about custom number formatting here: Excel custom number formats

(It’s an Excel article, but the rules are the same.)