8 Google Sheet Formulas I Wish I Knew Sooner
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
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:
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.
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!
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.
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.
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
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:
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.
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?