5 Simple Google Spreadsheet Formulas You Should Know

Google Spreadsheet is a powerful and free tool to manage complex spreadsheets. Its advanced features make it a viable alternative to desktop programs like Excel. This tutorial illustrates five simple formulas that help you simplify the way you work with this application.

Image()

Image() returns a picture from a specific URL contained in a cell or #N/A if the URL doesn’t contain any image. This formula can be useful when you have to check a large quantity of links to verify. The use is very simple: if you want to convert into a picture the URL in A2, select the cell B2 and type the following formula: =IMAGE(A2), where A2 must contain a valid URL. This is the result:

GoogleTranslate()

GoogleTranslate() help translate text from and into one of over 40 languages using Google Translate. This formula takes 3 parameters: =GoogleTranslate(“text”, “source language”,”target language”). If you want to traslate a text from english to italian use this formula: =GOOGLETRANSLATE(A2, “en”,”it”). This is the result:

Split()

This formula splits a string based on the given delimiter (for example “;”, “,”, “|”, “-”,…) and put each section into a separate column in the row. This formula gives two parameter as input: the string you want to split and the separator. For example if you need to split the string in the cell A2 you can use this formula: =SPLIT(A2; “;”) and this is the result:

Hyperlink()

Hyperlink merge an URL address and a string into a clickable hyperlink. This function gives in input two parameters: =hyperlink(“URL”,”Title”). If you want to merge into a hyperlink A2 and B2 use this code in B3: =HYPERLINK(A2, B2). This is the result:

Days360()

Days360() returs the difference between two dates. If you want to show the difference in days between A2 and B2 use this formula: =DAYS360(A2,B2) and this is the result:

  • Reply
    Annemieke
    December 19, 2010 at 10:33 am

    Thank you Antonio! I was in a meeting with a customer only last Thursday and we found we needed something like the hyperlink function but I wasn’t aware something like that even existed! Now I am going to score some major points tomorrow… Thanks again!

    • Antonio Lupetti
      December 19, 2010 at 10:38 am

      It’s a pleasure :)

  • Reply
    Alex MacCaw
    December 19, 2010 at 5:31 pm

    This is one I’ve found useful – live exchange rates: GoogleFinance(“CURRENCY:USDGBP”)

    Thanks for the post!

    • Antonio Lupetti
      December 20, 2010 at 1:28 pm

      I like GoogleFinance. It’s a great formula!

  • Reply
    Midasbrand logo design
    December 21, 2010 at 8:33 am

    Thanks for the post, a couple of these features are Really nice and I didn’t know about.

  • Reply
    justin
    January 5, 2011 at 7:36 pm

    might anyone know how to get an options dropdown within a cell with submit functionality?

  • Reply
    ashwin
    January 8, 2011 at 1:07 am

    hey, u made a mistake in image function. you forgot to include double quotes. its =image(“URL”)

    • ZunnaR
      February 22, 2011 at 1:54 am

      As you see =IMAGE(A2), A2 is cell reference. But if you write URL like text you must write like you said =IMAGE(“URL”).

  • Reply
    Michael Martin
    February 7, 2011 at 11:35 am

    Had no idea about some of these. The GoogleFinance and Image() in particular sound awesome and I’m going to start using them right now!

  • Reply
    Julio's CoRRp!
    February 17, 2011 at 12:07 pm

    Good job… thank you very much!!

    Muchisimas gracias :D