I was completely resistant to Google’s productivity software for several years (I know this is dangerous to admit as an educator). I didn’t like how clunky it felt when I compared it to Microsoft Office. That has completely changed in the past couple of years.

We all know the massive benefit to shared, real-time editing of documents, but it is the ability to have your spreadsheets share information between each other that has me boosting my productivity and saving me massive amounts of time.

In order to begin harnessing this powerful feature, you need to learn one simple formula. Here it is:

=IMPORTRANGE(“Key”, Sheet!Range)

If you would like to give this a spin in a safe space, you can  click this link and Create a copy to your Google Drive

Here is a simple example of this formula to show what it looks like in the demonstration file:

=IMPORTRANGE(“1bMCS4a0bjskeH3EHwyX0lZ8QO2gRAJsLwt2s1w0wwGA”, “Animal Info List!A1:A6”)

This formula is telling the sheet to:

  1. Find the spreadsheet from which the data will be pulled (the demo spreadsheet titled: Pull From Data Set)
  2. Locate the specific sheet from within the source spreadsheet (Sheet named: Animal Info List)
  3. Pull the data from the specified cells (Cells A1 through A6).

The big trick in this process is knowing where to find the reference spreadsheet key. The key is found in the URL address bar of the source sheet.  It is highlighted in red here:

key-screen-cap

The key is found in the URL address bar of the source sheet.

 

Once you have successfully pulled the data and you understand how to do this on your own, then you will be empowered to aggregate information from all those Google Sheets you have in your Drive. I am currently working on a single reporting page that will tell me the progress teachers have made towards providing student interventions as identified by our district and common assessments. This will be a dashboard for me.

Here is an overview of the process:

Let me know how you were able to apply this feature in Google Sheets. I am interested in learning from you all.