Useful Google Spreadsheet Formulas and Functions You Must Know

Most people use Google spreadsheets however not very clear about "how to do formulas in google sheets". Below are some heavily used spreadsheet formulas that can help you get going.

SUM

Wondering how to sum in google sheets? You can find sum of vales contained in different cells. To achieve that you can use the SUM function as seen in the example below:

=SUM(B2,B3,B4)

Alternatively, you can use a formula here: just add a plus sign to arguments:

=B2+B3+B4

AVERAGE

Need to know an average amount of particular cells? Use the corresponding function for that:

=AVERAGE(B2,B3,B4)

GOOGLETRANSLATE

If you need fast translate, use this function. It should look like this:

=GOOGLETRANSLATE(A1, "en" , "ru")
A1 is the cell containing text you need to translate; “en” is the source language; “ru” is the target language.

However, don’t rely on machine translation all the time, it is far from perfect.

NOW

The NOW (former googleclock) function adds current time and date to your document. The time is updated only when you are editing the spreadsheet.

=NOW()

Note that you don’t need any arguments here.

TODAY

If you always forget which date is today like I do, then this function is for you. It will help you to get to know the current date quickly:

=TODAY()

As you can see, the function doesn’t need any arguments just like the previous one. Unfortunately, there are no YESTERDAY or TOMORROW functions so you will have to figure out these dates yourself.

IMPORTFEED

In case you were looking for the way to import any feed from a URL, look no longer: here it is.

=IMPORTFEED(“http://feeds.feedburner.com/xyz”)

TRANSPOSE

This function will be useful if you need to change the layout of the information to better analyze it:

=TRANSPOSE (A1:F15)

PROPER

You no longer need to capitalize the first letter of each word manually, just use this PROPER function:

=PROPER(A5)

UPPER

Need to capitalize all words in a cell? Then use UPPER function:

=UPPER(A1)

LEN

Get to know the characters count of text in any cell by using LEN function. This is very convenient if you are writing meta tags or AdWords ads:

=LEN(G7)

TRIM

In case you need to get rid of unnecessary spaces between words in a cell, use the TRIM function:

=TRIM(G6)

CONCATENATE

To join 2 values from different cells, you can use this magic function:

=CONCATENATE(A1,B1)

As shown in the example, you can join domain names to the rest of URLs. This function saved me hours of work!

UNIQUE

Imagine you have many cells and some of them contain duplicate values. You need to get only unique values, so make use of UNIQUE function:

=UNIQUE(A1:Y6)
Note that the arguments here are divided by a colon; this means we use a range here.

The example is pretty straight-forward but it illustrates how the function works. It is similar to highlighting and removing duplicate values in Excel.

IMAGE

In case you needed to insert an image in a cell in Google Spreadsheet, here is the solution:

=IMAGE(“image URL”)

ISEMAIL

Those who are connected with email marketing and collecting emails, know that sometimes emails may be invalid for some reason (missing @ or dot, for example). So it’s a good idea check your list of email addresses before sending your email campaign.

=ISEMAIL(A1)
This check returns TRUE/FALSE values you can further analyze.

Things To Remember

All Google Spreadsheet functions and formulas can have either cells numbers you need to transform or just text (or numbers). For example:
=UPPER(T6)
=UPPER(“text to transform”)

If you use text or URLs in parenthesis, don’t forget to use quotation marks.
=IMPORTFEED(“example.blog/feed/”)
=PROPER(“words to use”)

Some formulas require particular arguments divided by commas; others need a range of cells. A range begins from the first cell you need to transform, then goes a colon followed by the last cell in the range. For example:
=CONCATENATE(A1,B1) – these are individual cells
=UNIQUE(A1:Y6) - this is a range of cells

Use Google Spreadsheet hints that are seen every time you begin to enter a formula or function. This means you don’t need to keep in mind all the numerous trivia.

Pages

Welcome To Techland