10 techniques to use when building budget templates in Google Sheets

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.

Credit Card budget template with Tiller

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:

  1. Why are you doing this?
  2. Invest time little and often
  3. Get basic formulas dialed
  4. Leverage power of more advanced formulas
  5. Don’t reinvent the wheel! Google has specific financial formulas
  6. Use comments to record specific details
  7. Get all your financial data into your Google Sheet with Tiller
  8. Set budgets and highlight spend over the budgeted amount
  9. Build drop-down menus to show different categories in your reports
  10. Use words!

Continue reading 10 techniques to use when building budget templates in Google Sheets

Data Cleaning and Pivot Tables in Google Sheets — My New Course has launched!

Data Cleaning in Google Sheets

There’s a joke in the data community that goes:

“You’ll spend 90% of your time cleaning data, and the other 10% of your time complaining about that…”

Do you deal with badly formatted data that takes you hours to tidy up, preventing you from making progress on your “real” work?

Ever wondered what the heck a Pivot Table is, and why you should care about them?

Do you ever find yourself repeating a process manually, like extracting zip codes from addresses?

This new course is designed to help you work with messy, real-world datasets. It’ll teach you techniques and best practices for cleaning data and preparing it for analysis and reporting.

This new course is aimed at beginner-to-intermediate Google Sheets users.

Continue reading Data Cleaning and Pivot Tables in Google Sheets — My New Course has launched!

Excel tutorial: build a dynamic bump chart of the English Premier League

Premier League team performance

Find your team: See the history of each of the 47 teams in the Premier League here.

In this post I’ll show you how to create a dynamic bump chart like the one above, using historical data for the English Premier League going back to 1992-93 when it was created.

Continue reading Excel tutorial: build a dynamic bump chart of the English Premier League

Save time with this custom Google Sheets, Slack & Email test scoring bot

You’ve marked your students test scores and recorded it all in a beautiful Google Sheet.

You’ve dotted your i’s and crossed your t’s, checked your spelling and made sure all your scores are ready to go.

Now comes the tedious part, copying and pasting each student’s scores and feedback into an email or Slack message to send back to them.

Wait, there’s a better way!

With a little bit of upfront effort using Google Apps Script, you can build yourself a custom Google Sheets Slack/Email app that can automate that whole final step for you.

Besides being useful, it’s also pretty fun to post stuff from your Google Sheet direct to Slack!

Send data from Google Sheets to Slack
Continue reading Save time with this custom Google Sheets, Slack & Email test scoring bot

Creating dynamic charts using Google Sheets drop down menus

Use this technique and much, much more in my new course: Build Dashboards with Google Sheets and Data Studio

In this post I’ll show you how to create Google Sheets drop down menus using the data validation method.

I’ve had a few questions recently on how to add interactivity to charts in Google Sheets, which is a great question that’s worthy of a detailed explanation.

Dynamic charts can really enhance reports and dashboards, allowing for more information to be conveyed in the same amount of screen space. This article will show you how to use the data validation method to make a Google Sheets drop down menu to control a dynamic chart.

How-to video

Continue reading Creating dynamic charts using Google Sheets drop down menus

10 techniques for building a Google Sheets dashboard

You’re probably familiar with Google Sheets and maybe you use it to organize and analyze your data. But did you know you can build a dynamic Google Sheets dashboard to really understand your data?

With a handful of powerful techniques, you can add some pizzazz and dynamism to the presentation of your data. Here are ten tricks to try next time you’re building a Google Sheets dashboard.

Google Sheets dashboard for digital marketing
Google Sheets dashboard for digital marketing, covered in my online course

Continue reading 10 techniques for building a Google Sheets dashboard

Setting up a Rails model to check for “http(s)” at the front of a URL

This issue arose when I asked my brother to test drive the Rails app I’m working on, UpLearn, without any supervision. It was really useful to have a second person use the software without any knowledge of how it was built, as issues surfaced that I might have otherwise missed.

One issue was the handling of URLs submitted by the user without an “http(s)” at the front. My brother had typed a link in to the submission form directly, rather than copy-pasting the URL, so it was missing an “http://” or “https://” at the front. As a result, my Rails app treated this as a relative path, rather than an absolute path, and the result was a broken link that didn’t take the user to the correct resource page.

Continue reading Setting up a Rails model to check for “http(s)” at the front of a URL

Creating a dynamic dashboard in Google Spreadsheets to track sales for a digital product launch

This article covers how to build a dynamic dashboard in Google Sheets, using formulas and data validation methods so that charts change dynamically based on user input.

Earlier this year I worked with The Write Life team to develop some behind-the-scenes sales data analysis during their 3-day digital bundle sale. The team wanted a simple way of gauging progress and seeing how the different sales channels fared as the sale took place.

Here’s the final dynamic dashboard:

dynamic dashboard in action
Interactive sales dashboard in a Google spreadsheet
Want your own copy of this dashboard?
Click here to access your copy of this template >>

Continue reading Creating a dynamic dashboard in Google Spreadsheets to track sales for a digital product launch