Getting Nerdy With Google Sheets

The first session I went to was advanced uses of Google Sheets, details are here: https://docs.google.com/document/d/1imLhB6RVEF1ozSbsAc12NhH93DVgUd99Ow0Mx5hsBWY/edit
Jonathan (@Schmidjon) went over many Google Sheets functions starting basic to more advanced:

  • Common functions - sum, average, count, countif, find more under the function (sigma) button.
  • Freeze Rows or Columns
  • Split example - on the example sheet, split example tab. Concatenate will join text
  • Paste Values
  • ISEMAIL( )
  • You can capitalize the first letter in each word using the PROPER function. This is useful if you need to clean up values to make them consistent.
  • Inserting charts, SparkLines
  • Conditional formatting allows you to change formatting based on a criteria
  • Link sheets together - ImportRange Function
  • Validation - see above example
  • Flippity.net - Create a quiz show and more from your spreadsheet
  • As it’s online, so it can tie into online data with importXML - example, live stocks data
  • GOOGLETRANSLATE() (language codes (ISO 639-1 Code))
  • To place an image straight into a cell type =image(“URL of the image you want to add”).
  • QR Codes - =image(“https://chart.googleapis.com/chart?chs=150×150&cht=qr&chl=”&A2).
  • Webscraping - =IMPORTHTML(“https://en.wikipedia.org/wiki/List_of_highest-grossing_films”,”table”,1)
  • Random Choice - =CHOOSE(RANDBETWEEN(1,5),”London”,”Berlin”,”Rome”,”Madrid”,”Lisbon”)
  • Add-ons - Random Generator
  • Explore Feature