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.

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.