Powerful Add-ons and Functions for Google Sheets

4.5K

Have you ever tried to cut a tree down with a hammer instead of a hatchet? If you know how these two tools work, you won’t even try. But that’s what we do with digital tools every day. Imagine if you knew a simple hack for turning a hammer into a hatchet. It could save a lot of time, effort, and money.

It was only last month that I found myself dealing with tons of data. What I wanted was simple, but I had no idea how to get Google Sheets to reveal the data. The answer? Use a function I had never heard of.  This experience convinced me to try and learn about new functions.

So now, I want to share some of my favorite tools and functions of Google Sheets– tools and functions that can help you avoid the frustration of trying to cut a tree down with a hammer instead of a hatchet. In other words, they can make your work a little easier.

Why We Use Google Sheets

I’ve been using spreadsheets since I was 13 years old, but I still know very little about them. That’s because I’m always focused on the problems I have to solve. But if you take a moment to look around, you’ll realize that you don’t always have to use the same tools to solve problems. And Google Sheets is certainly a tool for solving problems. Examples of problems Google Sheets can solve include:

• Making sense of complexity or representing data in easy-to-understand formats like graphs
• Sorting and filtering data to see relationships
• Calculating and summarizing
• Connecting more efficiently with others

Add-Ons and Extensions for Google Sheets

If you’re like me, you may have reached for some digital helpers like Google add-ons or extensions to Google Sheets. Some of my favorite solution helpers for Google Sheets include these power tools from Hymel Stayte’s Solutions:

1. Magic Make Copies
2. Magic Row Reveal
3. Magic Cell Notifications
4. Magic Class Tracker

Mail Merge Tools for Sheets

1. FormMule
2. Yet Another Mail Merge (YAMM)

Using Sheets as a Database

1. Google Data Studio
2. Watch Mike and Megan Video
3. Google Data Studio Resources: A Wakelet Collection
4. Awesome Table
5. Tweet Archiver
6. TAGS – Twitter Archiving Google Sheet

Generating Documents Using Google Sheets

1. Autocrat
2. Certify’em
3. Document Studio
4. Mapping Sheets

If you have more add-ons that you like for Google Sheets, please feel free to share those in the comments. But what if these power tools are too much for what you are trying to do? Or what if you need to dig into your data in a way that’s incongruent with these add-ons?

It’s time to explore some of my favorite functions below.

Google Sheets Functions

The following functions are ones that I use often. You can find a sample spreadsheet here (get a copy of it) that shows how each of these works.

One simple example that a lot of folks don’t know about that I use every day? Concatenate.

=Concatenate(A2,” “,B2,”<”,C3,”>”)

This allows you to put text together. My biggest use involves concatenating text such as firstname, lastname, and email. I use the result to enroll people in Canvas LMS.

Another one that I didn’t know about but is so powerful: IFERROR. It prevents showing a nonsensical error. Instead, it shows you a message you can customize.

Each of these expands your repertoire of what’s possible in Google Sheets. Take the time to learn them and practice them.

=join(“,”,A1:A5)

=IFERROR(value, [value_if_error]) (See more)

=IMPORTRANGE (sheet_url, range_string)

=QUERY(NAMEDRANGE, “SELECT A WHERE B=‘Google Sites’ “)

=COUNTIF (range, criterion)

=COUNTA (range, criterion)

=DetectLanguage(B2) with =GoogleTanslate(B2,C2,”en”)

Need a few more?

Get the “Make Magic with Spreadsheets” Wakelet

Want to get more examples in video format? You may want to check out this Wakelet where I’ve curated a few videos and tutorials.