10 Useful Google Spreadsheet Formulas You Must Know
- by Antonio Lupetti
- 42
- February 19, 2010
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.
L’involuzione del Web: come Google e Facebook stanno cambiando la rete e le nostre abitudini
Terremoto Yahoo! Cadono le prime teste dopo lo scandalo delle dichiarazioni false sul CV del CEO Scott Thompson
- 3
- May 8, 2012
L’involuzione del Web: come Google e Facebook stanno cambiando la rete e le nostre abitudini
- 18
- April 15, 2012
Thanks for the heads up.
There’s more to Google Spreadsheets than meets the eye!
Gj Antonio… May be some comparison between MS live docs and Google Spreadsheet will be nice.. Or some other services….
Most of them are comes from Ms Office Excel. I think we should respect to Microsoft in this case.
Most tricks probably work in one way or another on any spreadsheet…
I wonder what the MS Excel version of SPLIT is….
Wrong. Most of them come from Lotus 1-2-3. You’re probably too young to know.
Lotus 1-2-3 was released in 1983
I used SuperCalc in 1980
But all credit goes to Dan Bricklin.. VisiCalc 1979
i’m not yet trying this..!
Quite some functions Excel doesn’t know about. I was amazed to find out the filter function is unknown to Excel and Numbers. Seems to be quite useful, though.
wow..
very good post. GoogleLookup and GoogleFinance were new to me. Thanx specially these 2 formulas as they can very useful. I know other formulas u have describe in post as I use them frequently.
Thanx.
I don’t know much about Google spreadsheet. After reading your post I thought that I should try it. Thanks
That’s good stuff. But I’m still trying to figure out how to make row backgrounds alternate colors white and grey
in Excel I do that with conditional formatting and the mod function
=MOD(ROW(),2)=1
Ooh, I’d like to know the Google equiv of =MOD(ROW(),2)=1 as well!
That formula works in exactly the same in gSpreadsheet. But, Google has yet to allow formulas to change style properties of cells.
Fantastic I have been looking for these power features for ages. Thank you!
I think HYPERLINK is also a great one. Gives you the ability to have URLs with labels.
Good stuff guys. I will be using this in the future for sure.
Check out my site if you get a chance.. All about CMS’s, web development and other crap.
http://www.graphiclux.com
Thanks.
I had no idea that there was this much complexity available in Google Docs . Thanks for the headsup!
Thanks for the information, it’s a great reference to have available!
wow… i never knew that google docs have so much other functions…. i thought they only have excel functions… thanks for the info!
but what happen if i export it to excel???
thanks for this vital Information
Neat stuff.. never knew they embedded such power in docs.
Thank you for this information. I must confess that I knew about the VLOOKUP from Excel but never used it and have used CONCATENATE heavily but none of the other ones that you mention.
One last thing to note is that Google now allows Google Script on Google Apps Standard (as well as the rest of the Google App versions). With Google Script you can create a lot more functions to allow you to get the most out of your data.
http://www.google.com/google-d-s/scripts/scripts.html
Thanks again for the information.
Thanks really its very useful information.I never knew that Google docs has so much power.I applied these in my personal google doc & the results were really nice.
very useful for the office people
Great article, some very helpful short cuts in this post. Thanks for the resource.
good article……..
Thanks for the Spreadsheet formulas. They are good to know and extremely useful.
Thank You Antonio,
You have done a great job. it is really very useful. Wish you all the success. byeeeeeeeeeeee and keep up the good
finally some good, working formulas! i’ve been searching around for a while now! thanks for posting!
Thank you very much for the helpful introduction of the 10 most useful Google spreadsheet formulas. Your explanation on what the formula is for, and what it will work out is made in a way that a newbies like me can understand easily. Look forward to seeing your posts on more other formulas.
I have a rather unique question. I am trying to set up a spreadsheet for some Linkshell (guild) members in a MMORPG (FFXI). Basically, whenever someone obtains a type of armor, I want to replace the area that it can be obtained in with an “X” stating that they have gotten it. At the end of that job class, I want to display a total amount of armor that is obtained for that job (White Mage, Dragoon, Scholar, etc.). At the end of everything, I want an overall total of all armors. I would appreciate any assistance you can offer, and if you require an example of what I am trying to get, please let me know and I can e-mail it to you. I have tried asking a few forums around the web, but either no one knows what I am asking, or they are too hung up on themselves to be of any real assistance.
pretty similar to exel’s codes, I still prefer excel to Google
I much prefer Google’s formulas over excels (filter is very handy for example). It’s easier to have formulas that interact with external data/info. Also, if you know javascript, you can do some very cool scripting (http://code.google.com/googleapps/appsscript/)…That said, there’s no simple mail merge nor is there even a simple way to copy-and-paste the results of a formula out of google spreadsheets (when you copy, you get the formula and not the result of the formula. The work around is to copy the cell and then “paste value” into an empty cell which you then cut from…)
@Wolfe – use a form. players submit information. Have your spreadsheet use multiple sheets. each sheet can message the data along the way. You will most likely be looking at vlookup, sumif, unique, and filter type formulas.
Very Helpful Detail Given, Which we don’t know OR we are not using it.
Some of this information can help us in our daily wrok.
Please write more information like this.
Thanks..
I am having a problem trying to find what sort of formula is best to use! Can anyone help me please?
Ok i will try to explain what i am trying to do.
I have built a quote sheet and on the sheet i have an annual $ (this can cheange it is not always the same amount) then i have a limot $ (which can change) – say annual is G20 and limit is G22 on quote sheet. I then need to look these amounts up on sheet 6 (this is set out as a grid.
Colum headings are $1,500,000 limit,0 – C, $2,00,00 limit – D, $3,000,00 limit – E and $5,000,00 limit – F. HEADINGS ACROSS THE TOP
and horizontal column if annual revenues B4 is $50,000, B5 is $75,000, B6 IS $100,000, B7 IS $15,000 AND 200, 250, 500, AND LAST COLUM IS b11 OF $1,000,000.
I need to find and look up my annual $ and Limit $ on the gird and return the figure it match in the grid.
Say annual if $50,000 and look across the limit of $5,000,000 in the grid it will show me on F4 that the amount is $860.
How do i write a formula to do this and return the $860?
Thanks
thanx, I know now much more about Google Spreadsheet.
I have a Google form which collect fault reports form clienet, and I have create a new sheet where vLookup find action taken for a particular report ID, I want to have this online so users can see directly if anything has happened since they reported. Is there any way to allow users to add their ID and search for that action?
Update: googleLookup function is retired starting from Nov 2011.
How do I combine UNIQUE and FILTER for multiple arguments?
Eg. A B C D
1 REP COMPANY BOOKED ATTENDED
2 John ABC Sporting Yes yes
3 John ABC Sporting Yes yes
4 Alice Boost Juice yes yes
5 John Stellar Sound yes yes
I need to calculate the number of companies by rep who booked to attend and who attended. Not the individual people.
So in above, John would have a result of 2 booked, 2 attended and Alice would have a total of 1
An option for character counts for a specific character set (within other text) would be helpful if anyone can! =COUNTIF(A1:X1,”Y”) does not work…
What is XIRR replacement in google spread sheets any clues
Hey does anyone know if a formula exists where I can type in “Refund” or “Exchange” in a cell and have it kick out a number code in another cell?
For example. I type in Refund in row I1 and Exchange in row I2, code 0001 pops up in row Q1 and code 0002 in row Q2. Is this even possible? Or is Google spreadsheet not capable of doing this? Thanks in advance.