15 Useful Google Spreadsheet Formulas and Functions You Must Know

Google spreadsheets are popular online tool for anyone who wants the power of excel on web. Google doc spreadsheet functions and formulas are not known to many people. We have a google spreadsheet function list that will help you get quick start on using google docs with spreadsheets.

We live in the century of information: we receive it, share it, we breathe it. In such conditions there is a strong need to store and transform this information. You can use your notebook to write down important things or your computer to store the information here but in any scenario you’ll need something fast and convenient.

Here are the most important factors for working with information:

• Fast saving
• Easy access from anywhere

It’s up to you which program or other means you choose. Personally I used to work in Excel as it has a good system of storing information in different cells as well as good tools to transform and visualize it. The main thing I like about Excel is its formulas and functions. But at the same time there is one huge flaw: Excel is a desktop program and you can’t access your files from different computers. That’s where Google Spreadsheets come into play.

Google Spreadsheets are quite similar to Excel but they are a little better as they enable you to edit your files online and grant access for viewing and editing them to other people.

What Are Functions And Formulas?

Functions and formulas let you calculate or transform data. Moreover, they increase your productivity and decrease time spent on editing info manually.
A Google Spreadsheet formula begins with an equal sign “=” and helps to calculate something with the help of mathematical operators: “+” is for addition; “-“ is for subtraction; “*” is for multiplication , “/” is for division.

One formula can contain a function or multiple functions. These are predefined rules that are used to transform cells. For example, =SUM, =LEN are functions.

Arguments show which cell you want to transform. They are contained in parenthesis. For example, =SUM(C5:C67).

Theory is over so let’s practice a little.

15 Useful Google Spreadsheets Functions and Formulas

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/Fromdev”)

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

A list of formulas and functions you need greatly depends on tasks you have. Experiment a little and you’ll build a powerful tools set. I hope these functions will help you with that!