• ## Email Subscription

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Join 1,624 other followers

## Using Excel and Office 365 to create learning activities

In my previous posts I have:

In this post I am going to look at using Excel to create a collaborative learning activity.

Many people struggle (some are scared of it) with Excel which is a shame as it can be a superbly powerful learning tool – and it can be used in any teaching area not just maths, accountancy etc. There are many possible uses that I could list here, but I will stick with a couple of simple examples.

If I was teaching research methods or statistics, and wanted to investigate the idea of 2 sets of data and what the correlation is between them, then a simple way to do this is for each student to work out their height (m) and their shoe size (European) record these in a table, then everyone plots a scatter graph of everyones results – and we look at the line of best fit, standard deviation etc. Yes the constructing of a graph on paper with a ruler is an important skill to learn, but on this occasion I want to focus on the way that the correlation changes as more points are recorded, and for that I want to speed up the plotting process, so I am going to use Excel to help me.

Example of an excel sheet teaching the principle of data correlation

So I have created  a simple spreadsheet which has a data entry table – I have identified each student with the letters A-K, and I have colour coded the area they need to enter their data into with a green shading. I have unlocked these cells and then protected the whole sheet, so when the learners add their data, they cannot accidentally alter or delete any of the workings. On the right you will notice that I have pre-created a graph that will plot the data as it is added, and then underneath the table I have created some simple statistical functions to identify mean, mode, median etc of the data as it is entered.

So having created the above sheet, all I need to do is save it to my OneDrive (new name for SkyDrive) – share it with my learners (see previous post), and then allocate each learner a letter between A-K. They edit the sheet in the web browser, entering the 2 pieces of information against their letter. All of the students will be editing the same resource at the same time, so they will see the graph and the numbers change in real time as the amount of data added grows. The beauty of this is that by speeding up the capturing and plotting process, I can spend more time helping the students to understand the significance of what the graph and the numbers mean, and I could reuse the same template to add my own dummy data to show the effect of anomalies, different types of correlation etc.

Any form of class experiment which involves the capturing and sharing of class data can be achieved with this method. If the data entry is more complex than my example, then you could create a sheet within the workbook for one student, then duplicate this lots of times so each student has their own sheet.

Another useful feature is that it is possible to embed sections of the file (e.g. the results table, or the final chart) back into a piece of web space (e.g. the VLE, or a blog) – so below is an example of the final chart – as the file is used and data is entered this will automatically update.

With the above example all students could see (and potentially alter) all other student’s entries into the resource. This may not be desirable – e.g. if you are capturing sensitive data (e.g. if working out Body Mass Index and asking students for their Weight and Height) or you could ask students to feedback what they have learnt, what they have found difficult, what they would like to recover in a revision session etc.

What you could do is create a single Excel file asking for the data that you want. Once happy with this, you duplicate that file however many times you have students. You then share each file with the individual student. This may sound complex but is a lot easier and quicker than you would imagine. You now have a mechanism for students to give individual feedback to you – you as the teacher can see all of the files, so easy for you to see the information – and if you are really keen, then you could create an ‘overview file’ which uses simple excel formula to pull the data from each students file into a single dashboard type file. This last suggestion doesn’t update the data live as it is entered, but everytime that the file is opened it will pull through the latest data at that point.

If we are using Excel to create learning objects then it is beneficial to make the appearance of the file as tidy and uncluttered as possible – which I have discussed in a previous blog post – https://davefoord.wordpress.com/2010/05/19/simple-formatting-tips-in-excel-to-improve-quality-of-learning-materials/

In my next post in this series I will give an example of using Word to create a collaborative activity.

## 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.