I’ve stumbled across some awesome Google Spreadsheet formulas and wanted to share them so others can find some enjoyment with them. Ok, so I guess that sounds kind of nerdy, but definitely take note of these as I am willing to bet you will find use for them in your Google Spreadsheets. As I continue to find these awesome Google Spreadsheet formulas I will update this article accordingly. So in short, check back for updates!

You can see these formulas in action on my Google Spreadsheet located here: Google Spreadsheet Formulas

In addition, for a complete list of Google Spreadsheet Formulas visit their official documentation.

GoogleFinance()

This formula retrieves market information from Google Finance relative to the specified company.

Syntax: =GoogleFinance(symbol, attribute)
Usage:
=GoogleFinance("AAPL","price")
=GoogleFinance(A1,"price")

Description:The Google Finance formula is pretty much what it sounds like. You can use this formula to look up financial information relative to the company you selected. There are several attributes that can be selected in this formula, which Google has so graciously provided in their official documentation for the GoogleFinance() formula. For ease of use, here are some of the common attributes from the Google Documentation that you can use with this formula.

  • price – market price of the stock – delayed by up to 20 minutes.
  • priceopen – the opening price of the stock for the current day.
  • high – the highest price the stock traded for the current day.
  • low – the lowest price the stock traded for the current day.
  • volume – number of shares traded of this stock for the current day.
  • marketcap – the market cap of the stock.
  • high52 – the 52-week high for this stock.
  • low52 – the 52-week low for this stock.
  • change – the change in the price of this stock since yesterday’s market close.
  • changepct – the percentage change in the price of this stock since yesterday’s close.
  • closeyest – yesterday’s closing price of this stock.
  • shares – the number of shares outstanding of this stock.

GoogleTranslate()

This formula translates text from one language to another.

Syntax: GoogleTranslate(text, fromLanguage, toLanguage)
Usage:
=GoogleTranslate("Hello","en","es")
=GoogleTranslate(A1,"en","es")
=GoogleTranslate("Hola")
=GoogleTranslate(A2)

Description:The Google Translate formula can take text and translate it from one language and in to another. The formula to and from language uses the ISO 639-1 language code and can translate into over 40 languages. The formula also allows for you to omit the toLanguage and have Google set the language automatically to your default language. You can find a complete list of usage and accepted language translations in Google’s official documentation for the GoogleTranslate() formula. In addition, below is a short list of some of the languages this formula will translate to/form.

  • ar – Arabic
  • zh – Chinese
  • en – English
  • es – Spanish
  • de – German

Unique()

This formula returns only the unique values in an array.

Syntax: =Unique(SourceArray)
Usage:
=Unique({1;3;5;3;7;1;2})
=Unique(A1:A10)
=Unique(A1:B10)

Description:The Unique formula will pull a list of unique values from a set of values in either a row or a column. This formula can be used both statically or dynamically, and once adding the formula it will create the Continue() in adjacent cells to display the array.

Split()

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

Syntax: =Split(string, delimiter, treat_delimiters_individually)
Usage:
=Split("ABCxyzDxEyFzG", "xyz") returns – ABC | D | E | F | G
=Split("ABCxyzDxEyFzG", "xyz", TRUE) returns – ABC | D | E | F | G
=Split("ABCxyzDxEyFzG", "xyz", FALSE) returns – ABC | DxEyFzG
=Split(A1,"xyz")
=Split(A1,"xyz", TRUE)
=Split(A1,"xyz", FALSE)

Description:The Split formula will essentially split the contents in a cell. In addition, this formula can be used both statically or dynamically, and once adding the formula it will create the Continue() in adjacent cells to display the array. You can find a complete list of usage in Google’s official documentation for the Split() formula.

Filter()

This formula returns a filtered version of the array, where only certain rows or columns have been included.

Syntax: =Filter(sourceArray, arrayCondition_1, arrayCondition_2, ...)
Usage:
=Filter({5;10;15;20;25},{5;10;15;20;25}>=15)
=Filter(A1:A5,A1:A5>=15)

Description:The Filter formula will take the contents of an array and filter it based on the conditions set in the formula. This formula can be used in conjunction with other formulas such as count, sum, and average to create conditional formulas. In addition, this formula can be used both statically or dynamically, and once adding the formula it will create the Continue() in adjacent cells to display the array.

Hyperlink()

This formula creates a hyperlink inside the cell.

Syntax: =Hyperlink(URL, cell_text)
Usage:
=Hyperlink("http://robertmcquaig.com","Robert McQuaig Blog")
=Hyperlink(A1,B1)

Description:The Hyperlink formula will take the contents of the cell and convert the contents into a hyperlink as defined by both the url and the display text. In addition, this formula can be used both statically or dynamically.

Image()

This formula inserts an image inside of a cell.

Syntax: =Image( URL, selection (optional) )
Usage:
=image("http://robertmcquaig.com/wp-content/uploads/2012/12/Logo.png")
=image("http://robertmcquaig.com/wp-content/uploads/2012/12/Logo.png",1)
=Image(A1)
=Image(A1,1)

Description:The Image formula takes the image URL provided in the formula and returns the image into the cell defined. Several options can be set to tweak the way the image is display (options 1-4) in the formula. You can find a complete list of usage in Google’s official documentation for the Image() formula. In addition, below is a breakdown of the available options as described by Google in their documentation.

  • =image("URL") or =image("URL", 1) – Inserting this formula into a cell will scale the image to fit inside of the selected cell. If the cell is bigger than image you’re inserting, the remainder of the cell will be white.
  • =image("URL", 2) – Inserting this formula will stretch the image to fit inside of the selected cell. The aspect ratio (height vs. width) of the image won’t be preserved.
  • =image("URL", 3) – This formula will insert the image into the cell at its original size. If the image is bigger than the cell, some of the image may be cut off.
  • =image("URL", 4, height, width) – Inserting this formula allows you to customize the size of the image by specifying the height and width of the image in pixels. The height and width parameters are required for this option.

[center-ad]

Tags: