Google Spreadsheet is a powerful and free tool to manage complex spreadsheets. This tutorial illustrates ten useful formulas you must know to help you simplify the way you work in Google Spreadsheet.

### Vertical Look Up

This formula searches for value in the left-most column of array and returns the value in the same row based on the index.

`=vlookup(search_criterion, array, index, sort_order)`

For example, if you want to find the population of the value contained in the cell C2, from all values contained in the range A2:B4, copy and paste the following formula into the cell D2:

`=vlookup(C2;A2:B4;2;false)`

### Filter

This formula returns a filtered version of the given source array.

`=filter(range; condition1, condition2,...)`

In this example I filtered all values contained in the column A (range**A2:A12**) greater than **37**.

Copy and paste the following formula into the cell B2:

`=filter(A2:A12;A2:A12>37)`

You can also use multiple conditions to filter a source array:

`=filter(A2:A12;A2:A12>37; A2:A12<60)`

In this case the formula returns all values contained in the range A2:A12 greater than 37 AND less than 60.

### Concatenate

This function concatenates several text strings, contained in different cells, into one string.

`=concatenate(text1, text2, text3,...)`

If you want to concatenate all values contained in the cells A2, A3, A4 into one string, copy and paste this formula into the cell B2:

`=concatenate(A2,A3,A4)`

If you want to separate each single value with a space (cell B3), use this formula:

`=concatenate(A2," ", A3," ", A4)`

### Find

This formula looks for a string of text within another string and returns an integer.

`=find(find_text, text, position)`

For example, if you want to find the “space” within the string contained in the cell C2 (which contains this text: Jack Bauer), copy and paste the following formula into the cell B2:

`=find(" ", A2, 1)`

This formula returns “5” that means the “space” is found within the source string after 4 characters.

### Left and Right

**Left** extracts a substring from a string, starting from the left-most character.

`=left(text, number)`

**Right** extracts a substring from a string, starting from the right-most character.

`=right(text, number)`

If you want to extract the name “Jack” from the cell A2, copy and paste the following formula into the cell B2:

`=left(A2;4)`

If you want to extract the surname “Bauer” from the cell A3, copy and paste the following formula into the cell B3:

`=right(A3;5)`

You can also use the following formula to extract dynamically the name from an array of multiple values (Jack Bauer, Gregory House, Christian Troy, …):

`=left(A2; find(" ", A2, 1)-1)`

### Split

This formula splits text based on the given delimiter, putting each section into a separate column in the row.

`=split(string, delimiter)`

For example, if you want to split the values contained in the column A, using the delimiter ” ” (space), copy the following formula into the column B (the column C will be populated automatically):

`=split(A2," ")`

### Substitute

This formula substitutes new text for old text in a string.

`=substitute(text, search_text, new text, occurrence)`

If you want to substitute “Jack” with “Mike” from the text contained in the cell A2, copy and paste this formula into the cell B2:

`=substitute(A2, "Jack", "Mike",1)`

Occurrence (optional, in this case “1”) indicates how many occurrences of the search text are to be replaced.

### Unique

This formula returns only the unique values in the source array, discarding duplicates.

`=unique(array)`

If you want to obtain an array of unique values from the values contained in the column A, copy and paste the following formula into the cell B2:

`=unique(A2;A9)`

As you can see, this formula returns only unique values in the column A, discarding all duplicates.

### Google Finance

This function returns market information from Google Finance related to a specific company.

`=GoogleFinance(symbol, attribute)`

For example, if you want to know the Google stock price, copy and paste the following formula into the cell B2:`=GoogleFinance(A2,"price")`

You can use the same formula for the other companies in this example (NVidia, Intel, Cisco,…). Other attributes are volume, earning per share, opening price and so on (take a look at the official documentation for a full list).

### GoogleLookup

This formula attempts to find the values for straightforward facts about specific things.

`=GoogleLookup(entity, attribute)`

For example if you want to find the countries of cities listed in the column A, copy and paste the following formula into the column B:

`=googleLookup(A2;"country")`

You can access some types of entities such as countries and territories (population, largest city), rivers (origin, length), actors and musicians (date of birth, nationality), planets and so on. For a full list of popular entities, take a look at the official documentation.

Originally published by Antonio Lupetti

#### Get More Stuff Like This

Marketing, WordPress, Blogging Tips, SEO and Reviews, __twice a month__.

Thank you for subscribing.

Something went wrong.