We’re big fans of Google Sheets at Geckoboard, that’s why we recently enhanced our own Google Sheets dashboard integration. Google Sheets is an incredibly versatile and powerful tool that excels (pun intended) at organizing and calculating data in a spreadsheet format. Being cloud-based, it also offers many possibilities for collaborating, automating data collection, and even for pulling data in from third-party APIs.
If you’re familiar with other spreadsheet tools like Excel, iWork Numbers, Zoho Sheet or Open Office Calc, Google Sheets is extremely easy to pick up. If you’re getting started with Sheets, or just looking to up your spreadsheet game, here are three out of six of our favourite functions that can help you get the most from your data. We’ll also be following up shortly with three more advanced Google Sheets functions.
1. Joining text in Google Sheets
Sometimes it can be handy to use the values in the spreadsheet to construct a piece of text. Perhaps you’d like to summarise some key values or even generate some html.
By typing & you can concatenate cell values with other cells or, using quotes, any other text you wish to insert.
In the example below we’ll use the following formula:
=A1 & " " & B1 & " and " & A2 & " " & B2
to produce the following text “4 Apples and 5 Pears”
If you’d like to combine lots of values, the JOIN function is also useful. You specify what character you’d like to add between the values, and the cell values you’d like to join.
In the example below the formula:
Will join the values to produce:
2. INDEX: Return the first or last value in a Google Sheet
Spreadsheets are much easier to work with when you have a fixed data set. However, if you’re adding new data at regular intervals, perhaps a new row every week, there’s often some regular maintenance required to keep your functions working.
For example, imagine you always want to calculate the change between the bottom cell in your spreadsheet with the previous value. This is surprisingly difficult to do if you want the result to be calculated in the same cell every time. There’s no LAST() function, which would seem useful.
However, there is a solution. In Sheets INDEX() allows you to return the value of a cell by specifying which row and column to look at in the specified array.
=INDEX(A:A,1,1) for example will always return the first cell in column A.
Combining INDEX() with COUNTA() you can also create a formula that will always return the last value in a column.
You can find more info on this here.
3. Import data into Google Sheets
One of Google Sheet’s more unexpected features are its import functions.
These allow it to pull data into your spreadsheet from a variety of sources including XML, HTML, RSS and CSV - perfect for importing lists of blog posts, tweaks, product inventories or data from another service. For example, you can import a list of links from a specified URL with...
Import the contents of a list or table from a specified URL using:
=IMPORTHTML("http://en.wikipedia.org/wiki/Demographics_of_India", "table", 4)
Import an RSS or Atom feed using:
Import the contents of a CSV file from a specified URL using:
Coupled with Google Sheet’s scripting and automation features, Import functions become a very powerful tool for scraping and structuring data from public sources. Use responsibly!
For some more advanced Google Sheets tips, see Part 2 of this post.
You can also learn more about Google Sheets functions in our support center: