15 Useful Google Spreadsheet Formulas and Functions You Must Know
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.
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.
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:
Need to know an average amount of particular cells? Use the corresponding function for that:
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.
The NOW (former googleclock) function adds current time and date to your document. The time is updated only when you are editing the spreadsheet.
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:
In case you were looking for the way to import any feed from a URL, look no longer: here it is.
This function will be useful if you need to change the layout of the information to better analyze it:
You no longer need to capitalize the first letter of each word manually, just use this PROPER function:
Need to capitalize all words in a cell? Then use UPPER function:
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:
In case you need to get rid of unnecessary spaces between words in a cell, use the TRIM function:
To join 2 values from different cells, you can use this magic function:
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:
Note that the arguments here are divided by a colon; this means we use a range here.
In case you needed to insert an image in a cell in Google Spreadsheet, here is the solution:
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.
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(“text to transform”)
If you use text or URLs in parenthesis, don’t forget to use quotation marks.
=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.
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!