Spreadsheets: How to Sort Data Onto Sub Sheets based on values in a given column

One of the most popular posts on this blog, is one I published back in 2011, titled How to automatically pull data between different google spreadsheets  I am often asked by people, is it possible to filter the data based on the value in a column, before pulling the data across?

I have created a video which shows a technique whereby data is filtered internally within a workbook, so data is pulled onto subsequent sheets, based on values in a certain column. In this example I am using a class of students, and all the grade A students are copied onto a sheet called “A”, all the grade B students are copied onto a sheet called “B” etc. This principle could easily be used to organise a list of sales by sales rep, or by region.

The video is about 13 minutes long, but well worth watching, if you are interested in this technique.

For this example I have used Excel, but this would also work with other spreadsheet systems such as OpenOffice or Google Sheets.

The file used in the video example can be downloaded here, if you want to see or copy the formulas used.

Spreadsheet – sort data onto new sheets Shared

The mechanism also uses a technique to display the sheet name in a cell in the spreadsheet. More details on this can be found at https://davefoord.wordpress.com/2015/04/16/how-to-display-the-sheet-name-in-a-cell-in-an-excel-spreadsheet/

I hope that this helps people to make better use of Spreadsheets, whether it is in education, work, or for personal use.

Automatically pull an entire sheet of data between google spreadsheets

In November 2011, I wrote a blog post titled ‘How to automatically pull data between different Google Spreadsheets‘ – which was based on a feature called ‘ImportRange’. Although nearly 5 years old, this particular blog post is one of my most frequented and certainly the most commented on post that I have ever written.

I have recently been introduced to a new add on feature called ‘Import Sheet’ – which can be found at https://importsheet.com/ – and this does exactly what it says on the tin. It allows you to easily import (or export) a sheet from one file to another. So for example, I use google sheets to log the work that I do for each of my clients, with each client having a separate workbook (this means that I can share that workbook with them, without them seeing other contracts that I work on). I can then have a master dashboard, which imports copies of these sheets into a single location, and using simple functions such as the = function, I can then pull key data out of each of the sheets into my dashboard. Using Importsheet rather than the ImportRange feature is much easier, quicker and less likely to have problems.

The add on is a commercial tool, that does have a pricing plan, however the free version does what most people will want. Obviously I have no idea how the pricing plan may change in the future – they may choose to get rid, or reduce the functionality of the free version, so I wouldn’t suggest that people invest lots of time creating high stakes activity with this add on (unless they are prepared to pay in the future) – but certainly for the moment, this looks rather neat.

Using Office 365 to create collaborative learning activities

Last week I was at the BETT show, working for The Tablet Academy who were running the interactive classroom on the Microsoft stand. We ran a series of 15 minutes interactive sessions, with one of my sessions being on the use of Office 365 to create collaborative activities. This session turned out to be very popular showing the interest from educators in this way of working.

The principle that I demonstrated wasn’t new – it was something that I have been doing for 8 or 9 years using the collaborative functionality of Google Drive (formerly known as Google Docs) which I have previously blogged about, however many education organisations are nervous about using Google Drive in this way, and the example that I used in my blog post, did involve the work being potentially visible to anyone in the World, which didn’t matter for what I was doing, but for other subjects would be an issue. If an organisation has adopted Google Apps for education then it could all be kept safely enclosed within the organisation, but most places don’t have this – but if they do have Microsoft, and now that Office 365 offers a real time collaborative functionality – I can easily set up similar activities in a way that the IT/Network manager will be happier with.

Before we progress we need to understand a bit about how Office 365 works in conjunction with OneDrive (Microsoft’s cloud storage option – formerly called SkyDrive). When a teacher creates a file using Word, PowerPoint Excel or similar – they can save this to their OneDrive – this will appear on their computer just like any other network drive, so behaviour wise it is very easy for staff. If they are offline, it doesn’t matter the work will save, and as soon as they are online again it will Synchronise with the OneDrive server.

The files are now stored on the computer but also in the cloud – this means that I can access them from any internet enabled computer by going to the OneDrive website and logging in as me.

Image showing the web view of Skydrive with the Words (Download, Share, Embed and Manage highlighted)

Example view of the OneDrive web interface

You will see in the  image that there are 4 options highlighted with the red rectangle:

  • Download – allows for a local copy of the file to be downloaded onto the computer.
  • Share – Is what we want here, as we can give students access to the file, without having to send them a copy.
  • Embed – allows for files to be embedded into something like a website, blog or VLE – this could be very useful for displaying a graph or chart following an experiment or survey.
  • Manage – allows options such as renaming, but also a version history – so if someone sabotages a collaborative file, you can roll back to an earlier version and find out who did the sabotaging.

These different options can be applied to individual files, multiple files or even folders. The folders options could be very useful, as you could set up sharing options with individual students at folder level once at the start of the year, then any file that is added into that folder will automatically be visible to the student – I can see lots of potential here for giving feedback after assessment, and an ability for students to make comments etc. on their feedback all with the same document. The history functionality gives me the data integrity that I need for assessment purposes which in the past caused us to produce lots of inefficient different files with no information moving between them.

If I were to set up a collaborative activity using these tools, there are 2 options for the students. They can either edit the file in the web app – this is great if accessing this activity via a device that doesn’t have Microsoft Office on it (e.g. an iPhone or iPad), and allows for real time synchronous editing (lots of people editing same document at same time) – but you don’t get the full Office functionality. Or students can access and edit the file in Office, which gives the full functionality and great for small scale non-synchronous collaboration.

The key to making any of this work, is changing the way that we behave with files – which will take time. Email although a great tool, has created a culture of sending files as attachments – which creates multiple copies of the same file in different location which then leads to problems. If a single file is stored in one place and a link to this file is shared then there is only 1 file and therefore less problems.

In the coming days I will release posts, giving examples of different collaborative activities using PowerPoint, Excel and Word.

Adding subtitles to YouTube videos using CaptionTube

YouTube is a wonderful resource, it works on just about all internet enabled devices, it hardly ever goes wrong, it is easy to use and although there is a lot of low quality rubbish on there (in my opinion), there is also huge amounts of really useful high quality videos that we can use in education to enhance our teaching and learning practices.

A feature of YouTube that many don’t know about, is the auto-captioning option – in other words YouTube creates a transcript of the video without you having to do anything. If you are watching a video on the YouTube page and you want to see the captions, then there is a button below the video (currently to the right of the where it says ‘add to’) which is the transcript button – this brings up the transcript as a timeline below the videos and automatically advances with the video. This can be great for learners that have a disability (e.g. are deaf), but can also be really useful to find a key point within a video.

For example I often use short sections of the excellent TED talk video of Ken Robinson talking about schools killing creativity. If I want to locate a certain section within that video, I use the automatic captions that appear below it to locate the section that I want.

Because the transcripts are computer generated, they do contain errors – and depending on the clarity of the voice and the background noise of the video will determine the accuracy of the transcript. For some reason my voice never does well with automated speech to text systems, including YouTube.

However if you do want to override the automatic captions that YouTube creates with your own ones, then this is very easy to do – and for this I use a service called CaptionTube This is a simple system where you sign in (using a Google Account) you locate the video you want to caption (which could be your own or someone elses) and then you play the video pausing it at intervals to add your captions. If the video is your own, then you can add the captions to it there and then, if it isn’t your video then you can send the transcript to them to see if they want to upload it.

The following video (by John Skidgel) introduces the basics of CaptionTube.

Here is a video of mine that I captioned using this method. This took me 12 minutes in total from opening the page to my captions appearing on the video on YouTube.

Adding Captions to a video is a simple way to increase the accessibility of a resource, as well as potentially increasing the number of people that see your video, as the contents of the captions will get picked up by search engines (if the video is set to being public and listed).

Using Google Docs (Drive) to create a collaborative learning activity

Google Docs or Google Drive as it has changed it’s name to, is a suite of office tools that work via the internet and store the different files in the cloud (on the internet) rather than locally onto the computer. This has huge advantages in terms of the files are backed up automatically, can be edited on a variety of different computers (including Smart phones) and they allow multiple people to contribute or view the files.

It is the ability to allow multiple people to edit that makes Google Docs an excellent collaborative learning tool, as it is possible to set up activities where different learners are accessing and editing the same document at the same time – this means that they can see and respond to what each other is doing in real time.

An example of such an activity is one that I ran recently used at a training event as part of the Advanced Teacher Learning Coaches programme. This took me about 10 minutes to create and set up, so nice and quick, and the learning experience was far greater than doing this in a non-collaborative way. If you want to use the activity above (possibly swapping in your own websites for your particular area), click on the link above, then save a copy of this (from the file menu) – you can then alter the sharing settings to allow other people to edit it. A video showing how to do this can be found below.

Using Google Docs for collaborative activities – is a great way of working with higher order thinking skills. What I will often do is set a simple task where each person or small group of people have to edit an area within the document answering a question or questions. What I then do is ask everyone to swap areas (e.g. so they are looking at someone else’s contribution) – I can then ask a more challenging question – such as critique the other person’s responses, or present a counter argument to their point, or ask them to identify which of the points made by the first group would also be examples of….. etc. and if time allows, then I sometimes set a third question where they look at a third different set of responses and answer another challenging task or question.

Another really useful feature within Google Docs, is that you can see the revision history – so you can identify which people have contributed most (and when) – which can be useful if doing this as part of an assessed activity – and you can roll back to earlier versions of a document, so if someone does something very damaging (e.g. deleting everything, writing something defamatory, or using it to cyber bully) you can roll back to an earlier version (or restore point).

The fact that these documents will work on most if not all Smart phones makes this a really powerful, versatile and truly mobile opportunity.

Making use of statistics from YouTube

A few years ago YouTube was seen by many in education as a source of evil that had to be blocked. banned and banished at all costs -because of the nasty things that learners may see there. This was a shame as alongside some possibly undesirable content is some excellent content, and the YouTube’s streaming capability is better than any others as works on all devices, is quick to load and in short just works.

Luckily the number of organisations blocking YouTube has reduced, especially within FE and HE, and even many schools. This pleases me as I have posted many videos to YouTube – most of which can be used by others, as they give simple clear step by step instructions on various elements of learning technology.

Something that I also find interesting, is the statistics that I get back – I can see how many people have viewed each video, where they are from, what sort fo device they have used and how they have found the videos. This helps me to plan future videos to meet my audiences needs, and if I was using these videos to support teaching and learning, I could use the analytical information to quickly see how effectively my learners are accessing these videos.

When I upload videos on behalf of one of the organisations that I work for, I almost always have the settings of making it public but unlisted. This means that the videos cannot be found by someone else searching for it – they can only be found if someone knows the link to it. If I embed one of these videos into an area the VLE to support a particular session or topic, a week or so later I can see how many people have viewed the video – (and when). Although not an exact science, this gives me a useful insight into the user behaviour – especially if I compare this with the usage data from the VLE.

In one instance I found that lots of learners had visited the area on the VLE but hadn’t played the video – which made me realise that I had embedded the video too far down the page – so I changed it’s position. On another occasion the video had been played many more times than the VLE area had been accessed, which I assume meant that the learners had watched the video multiple times – which as the video was directly related to the assignment task, I assume means they were using it to aid their completion of the task (which was its intention).

I appreciate that most teaching staff won’t have the time or inclination to look at things this way – I was mainly just looking out of interest – but if people do have the time/interest then this could be very useful information to confirm that they are doing things right, or give them pointers as to where they need to change things slightly.

Whilst looking at my own videos, I discovered that my most viewed video of all time is the one about adding countdown timers to PowerPoint.

This has had over 45000 views in 2 years, and currently gets viewed over 3000 times per month. This single video accounts for more than 65% of all views of the 67 videos on my channel. This video shows to me the power of YouTube – the fact that this attracts so many views means that it must be doing something right, and what a shame that there are still many educational organisations that are depriving their learners of this resource.

How to automatically pull data between different Google Spreadsheets

Edit 29/03/2017 – Please also look at another related post – https://davefoord.wordpress.com/2017/03/29/spreadsheets-how-to-sort-data-onto-sub-sheets-based-on-values-in-a-given-column/ 

Edit 26/10/2016 – Please also look at a more recent addition to this post  – https://davefoord.wordpress.com/2016/10/26/automatically-pull-an-entire-sheet-of-data-between-google-spreadsheets/

I have been using Google Docs for quite a few years now, and in particular Google spreadsheets.

One feature of Google spreadsheets is there is a function called ImportRange that allows you to pull data out of one sheet and into another. This can be really useful, if for example you have a spreadsheet that you are using to collaborate with others, and then somewhere along the line you want another person to be able to see some of the data in the sheet but not all of it. e.g. if you are using this to track student grades, you could have a master sheet that you and other tutors can see all of, you could then create a separate sheet for each student, and pull through only the data that refers to them (you then share that sheet with the student) and they have a live constantly updating record of what they have achieved etc.

Or you could have a mechanism where each subject tutor has their own spreadsheet to record class grades, then the course leader has another sheet which pulls all of this data into one place, so they can at any point in time see how the class is doing without having to ask tutors to email over their latest version of their standalone spreadsheet (which I see happening all to often).

Here is a video showing the basic principle of this idea.