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.
I have recently been introduced to a new add on feature called ‘Import Sheet’ – which can be found at https://www.sheetgo.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.
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.
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.
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).
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.
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.
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.
I am not sure quite how (as I am sure it worked when I originally wrote this post) but the method that I describe below doesn’t work completely as described. I am trying to work out how to get round this, as I think this would be a really useful technique. If I can solve this, then I will re-post to this blog and will update this post accordingly.
With the first of the above techniques – one problem with this is that you often end up with the pins that are plotted being invisible at first – meaning that you have to then edit each one in turn to choose the desired colour pin, but it is possible to add data to the original spreadsheet to automatically plot pins of your choosing.
To do this add a column and title it something like ‘image’ or ‘icon’. Then you need to add against each entry the URL of an image that you want to act as the marker for the point – you could use anything (e.g. your own company logo) as long as it is small in size. Or you can use the pins that Google provide – all we need to do is find out the URL of each of the pins, that you may want, which I have done for you here so
So having created our spreadsheet of data, and added an image URL against each URL – if we then go to http://www.batchgeo.com/ the site mentioned in my first blog post) – When you get to Step 2 – there is a button for ‘Advanced Options’ – if you choose this, then against ‘Image URL’ choose whatever column header you had in your spreadsheet.
With some clever understanding of Excel and the use of formulas, this could be a very powerful technique for creating maps.
My wife and I are currently campaigning to keep the midwife run birthing unit of St Mary’s in Melton Mowbray open – this was where we had 2 of our 3 kids, and think that it will be a real shame if it is closed.
As part of the campaign, my wife wanted to plot on a map the distances from all the birthing centres in the area – as there is a Government agenda that all people should have the choice of where to give birth (hospital, mid wife run centre or home) and this has been interpreted by some as travelling less than 15 miles. I did some searching and found out that Google Maps could represent this quite nicely – the end result being
(click on the image to see the map in Google maps)
In the past when I have done this, it gave each point a black and white icon, when I did it this time it applied no icon, so I had to click on each point in turn to format the icons – for this set it was OK as only a small number of points. I will blog again another day showing how you can get the pins to automatically add.
Then I needed to create the radius diagrams around each pin – there are various ways to do this with an exact circle, but the technique that I used actually worked out distances based on a car journey – for this I used http://www.freemaptools.com/how-far-can-i-travel.htm I had to go through my points in turn, and enter the postcode, I then chose my settings (in my case 24 km = 15miles) but I could have used a time. This then again produced a kml file, which I could add to my map exactly the same as with the earlier method.
This technique has obvious uses for geography students, but I can also see this being used by travel and tourism, leisure, business and many more.
On Friday I posted an entry to my blog about the sad news that Etherpad would be no more thanks to being acquired by Google, but less than 48 hours later I am pleased to report that Google have u-turned on the decision so it looks like Etherpad will survive – http://mashable.com/2009/12/05/etherpad-user-victory/
so what has been learnt through this little incident:-
we cannot rely fully on web 2 services. in the last 2 years I have used and promoted many services (Jaiku, veotag, Gabcast etc) only for them to die or start charging. now when I use and promote free tools I try to think about how the founding company is going to make money and work out the likelihood of the tool surviving. with Etherpad I thought it was a good bet, but the way that we so very nearly lost it shows if nearly wasn’t.
however from a bigger perspective this has shown how the power of the social web can be used by the masses to challenge the big boys like Google. this should make all companies (in any area of work) take note as some may have to rethink their customer service strategies. Also this fills me with confidence that as and when other sites are acquired by Google, Yahoo or Microsoft etc. the power of the people will stop them slipping into oblivion (like Jaiku did).