Category: Spreadsheets

Introduction

I firmly believe that one of the most effective and rewarding ways to learn a skill is through practical application.

Solving problems you don’t know the answer to is arguably the best way to do this.

And that’s the idea behind these Formula Challenges.

I’ll post a challenge in my Monday newsletter — a question to be solved with formulas in Google Sheets — and a week later share solutions, both my own and those submitted by readers.

I’ll archive the challenges and solutions on my website here.

This first Formula Challenge originally appeared in my Google Sheets Tips newsletter, on 25 February 2019.

Sign up here so you don’t miss out on future Formula Challenges:

Find all the Formula Challenges archived here.

The Challenge

Start with a straightforward IMAGE function in cell A1, like this:

Your Challenge

Your challenge is to modify the formula in cell A1 only, to repeat the image across multiple columns (say 5 as in this example), so it looks like this:

Rules

You’re only allowed to use a single formula in cell A1.

The problem is that the IMAGE function can’t be nested inside a REPT function, so you have to get a bit more creative.

The Solution

Solution One: using ROW or COLUMN counts

How does this formula work?

The combination of ArrayFormula with COLUMN(A:E) will output an array of numbers 1 to 5: {1,2,3,4,5}

The IF statement treats the numbers as TRUE values, so prints out the image 5 times. For brevity, we can omit the FALSE value of the IF statement, since we don’t call it.

Solution Two: using REPT inside the IMAGE formula!

How does this formula work?

As mentioned, the REPT function doesn’t work when wrapped around the IMAGE function. However, flip them around, with the REPT inside the IMAGE function, and it does work!

In other words the IMAGE function accepts arrays of URLs as an input.

Start with this formula in cell A1, which creates a single string of joined URLs, with a pipe ( | ) delimiter between them:

Now, split these into an array of 5 separate URLs:

Finally, wrap this with the IMAGE function to get the five images in a row:

What I like about this solution is that you could put the number 5 into a different cell and reference it, so that you can easily change how many times the image is repeated.

You could even embed another formula to calculate how many times to repeat the image 😉

See the other Formula Challenges here.

Let’s kick off this post with one of the all-time top Google Sheet productivity tips:

1. Shortcuts

Use keyboard shortcuts to work more efficiently. It might feel clumsy at first, but persevere and it’ll pay off in spades as you become more efficient in your work.
Continue reading 6 Killer Productivity Tips for Google Sheets

Have you ever wanted use the VLOOKUP function with multiple criteria?

For example, you may want to use first name and last name combined to search for a value using Vlookup.

In this post, you’ll see how to Vlookup multiple criteria in Google Sheets, with three different scenarios.
Continue reading VLOOKUP Multiple Criteria in 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 2 and 4, 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.

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 $79/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.

This is what your homepage looks like, and where you add accounts or create new Google Sheet templates:

Once you add the bank accounts and credit cards you want to track, you can go ahead and create a new Google Sheet:

Click Create and the magic happens! ?

After a short while, you can click over to your new Google Sheet, populated with all of your transaction data!

How we use Google Sheets to track our spending habits

We’ve setup categories to group our transactions, so that we can see how much we’re spending on different things at a high-level. For example, we group all restaurant expenses together into an “Eating Out” category, which allows us to see how much we spend eating out.

You want enough categories to differentiate items in a meaningful way, but not too many that you end up with too much granularity. The whole idea is to summarize transactions into something more manageable.

Each week my wife or I will jump into the Tiller Sheet and categorize any new transactions. It’s as simple as selecting the category from the drop-down menu in Transactions tab in the blank cell next to the transaction name:

You can even use Tiller’s new Autocat tool to now automatically categorize transactions for you.

Creating custom reports with Tiller and Google Sheets

I’m going to share our solution for tracking our spending habits.

It allows us to understand how we’re spending our money and identify ways to reduce it.

I created a summary table in our Tiller Google Sheet, which shows our family spending by category. It takes the data we’ve categorized in the transactions tab, which is automatically updated by Tiller, and summarizes it.

The transactions are summarized by categories in the rows, and by months across the columns. Columns A and B contain checkboxes, which I use to control which spending categories to show in my charts.

This table alone gives us more insight into our spending habits than anything the bank gives us. Every single transaction is included and categorized, by us not the bank.

You can create a table like this with the Google Sheets QUERY function, or using Pivot Tables in Google Sheets.

In my case, I’ve used a QUERY function in cell C3 to retrieve, aggregate and pivot my transaction data:

Visualizing our spending habits in Google Sheets

I added the checkboxes in columns A and B, so there’s a way for my wife and I to choose categories to focus on.

The checkboxes can be individually checked or unchecked and they feed into other data tables that only show the data for the checked items.

Our mortgage, car lease and utility payments remain largely the same month-to-month. We know we have to pay them every month, so we don’t necessarily need to see them every time. (That’s not to say they’re not important, but trying to visualize all your categories at once will just clutter your charts to the point of being useless. )

However, seeing our discretionary spending — things like travel and eating out for example — helps us understand our spending habits and find ways to save more money in a healthy way.

We’re currently using two charts to track our spending habits:

Chart 1: Current monthly spend vs. Average monthly spend

The first is a monthly breakdown by category, showing actual spend this month (blue) against the average amount we spend in this category each month (red):

(Chart shows fictional data.)

Chart 2: Discretionary Spend by Month

The second is a look at our discretionary spending over the past few months, so we can see how selected categories are trending (chart shows fictional data):

The combination of the monthly category breakdown table and these two simple charts gives us tremendous insight into our spending habits.

Knowing how we’re spending our money gives us tremendous peace of mind.

It’s helping us to minimize our unnecessary spending and maximize our saving.

See Also:

Tiller homepage

10 techniques to use when building budget templates in Google Sheets

A guide to the super useful QUERY function

Pivot Tables in Google Sheets: A Beginner’s Guide

How to use checkboxes in Google Sheets

Note: I’m not a financial expert and this post does not provide financial advice. It simply shows some techniques for working with and presenting data in Google Sheets.