10 Useful Google Spreadsheet Formulas You Must Know

Posted by Tux De Luxe at 08:24 20/02/2010
10 Useful Google Spreadsheet Formulas You Must Know: "

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.





"