Trans4mind Home Page
Home Article Library IT, Internet, Computers & Mobile Apps

8 Google Sheet Formulas I Wish I Knew Sooner

Google Sheet Formulas

Your manager needs you to put in an extra two hours. Your spouse needs you to return an item to UPS. And your dog just ate a pair of socks — guess it’s another visit to the emergency vet.

At the end of the day, don’t you just wish someone would make your life easier?

Well, Google Sheets might not be a person, but it is here to simplify your life. From managing household tasks to performing a little better at your job, there are a lot of Google Sheets formulas you might wish you knew sooner.

1. The TODAY() Formula

Google Sheet Formulas

Quick! What day is it?

If you couldn’t answer, you aren’t alone. A lot of us are working from home and, quite frankly, losing track of the days.

But Google Sheets never loses track of what day it is. Type in an easy “TODAY()” and you’ll get today’s date inserted for formatting. (You can also use formulas such as NOW() if you want the full date and time.)

2. The SUMIF() Formula

SUMIF() is very powerful. It adds together a column, but only if it meets certain criteria. And that criteria can span columns.

Let’s say you wanted to make a budget for the household and split costs. You can use the SUMIF() function to only add up a column if it’s applicable, like so:

Google Sheet Formulas

There’s also the SUMIFS() function, which provides for multiple selection criteria. If you learn how to use sumifs, you can create some pretty powerful templates — and never have to worry about dinner budgeting again.

3. The SPLIT() Formula

Your boss gives you a list of, well, just stuff to order.

Sprocket   Vendco   4/15
Spigot   ABCVend   4/18
Splicer   VendInc   4/20

Of course, when you put it into your Google Sheet, it’s just a single cell of unformatted data. But use the SPLIT() function, and you can split it apart into orderly data.

Google Sheet Formulas

Now you can bring it into your software and start ordering those parts.

4. The SPARKLINE() Formula

This one is just neat. The SPARKLINE() function can help you make a progress bar. That’s right, a progress bar. In Google Sheets!

Google Sheet Formulas

Let’s say you need to do your laundry (do your laundry!) and you want to track what you’ve done so far. In the above example, the progress bar adds up the percentage you’ve completed using the SUMIF() formula. It then produces a progress bar based on the amount you’ve done vs. the total job.

Progress bars are a great way to see what you’ve accomplished at a glance.

But seriously, do your laundry. 

5. The SUBSTITUTE() Formula

Your boss has given you another list. (So many lists.) This time, though, there’s another problem:

John, 18 Sprockets, 5/3/2000
Sally, 3 Sprockets, 5/9/2000
Jane, 24 Sprockets, 5/10/2000

What’s the problem? Your inventory system uses “5-10-2000” formatting for dates, not “5/10/2000” formatting. It’ll break if you try to import this.

Substitute makes it easy to substitute one text string for another.

Google Sheet Formulas

In the above, you use SPLIT() to split up your data and then SUBSTITUTE() to substitute the “/” symbols with “-” characters. A lot easier than trying to change it all yourself, especially for longer lists.

6. The IMPORTRANGE() Formula

This one is really cool. Let’s say you have a full calendar of meal options for a month. But you want to make your shopping list for the week.

Google Sheet Formulas

Here, I’ve imported my list of items from a “master list.” So, there’s a second sheet that I’m pulling the information from — so I can create a weekly list from my monthly list. IMPORTRANGE() will work with any other Google Sheet you have access to; you just need to connect the sheets.

Read this further helpful information on importing data from another sheet in Google Sheets.

7. The FILTER() Formula

It’s time for Christmas lists! Your children have given you a list of gifts. But, well, predictably…

PlayStation 5   $1,200
Lego Moon Base   $600
New Shoes   $200
Barbie Mad Scientist   $80

Google Sheet Formulas

And, of course, it just goes on like that. But what if you could filter out, say, everything that was over $500 and under $100?

It looks like you’re only buying three gifts for your kids at $600 instead of 6 gifts at $2,420. (But good luck with that new puppy!)

8. The UNIQUE() Formula

Great, it’s another list. This time, you were trying to export something from software at work. And, well, it exported, but the list looks like this:

Jane Doe
Jane Doe
John Doe
Jane Doe
John Doe
John Doe
John Doe
Jane Doe
Julie Doe
Sarah Doe
Jane Doe

It’s because it exported every individual instance of a record rather than just each individual record. What do you do? UNIQUE() makes it possible to just pull unique instances out of Google Sheets.

Google Sheet Formulas

Easy as that, you’ve got a list that’s significantly less of a mess. You can then use the formatted data to import everything into your customer relationship management suite, email blast, or whatever else it is your heart desires.

It’s Time to Learn Your Google Sheets

There are a lot of Google Sheets formulas. It’s OK if you usually just use SUM(), but it’s also OK to branch out. 

The more you learn about Google Sheets, the more likely it is to be able to help you with your day-to-day tasks. And if there’s something that you’re struggling with, it’s probably just a quick Google search away.

Are there any formulas that you wish you’d known sooner?

Internet IndexMarketingUse of Internet &MobilesSocial NetworkingWebsite Design & SEOComputers/TechnologyCryptocurrencies
You'll find good info on many topics using our site search:

+ Hypnosis Will Help Solve Your Problems!