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.

How to display the sheet name in a cell in an Excel spreadsheet

I use Excel a lot, not just for crunching numbers, but for creating teaching resources, lesson planning, managing my accounts and invoices and various other uses. One feature that I often use, is the ability to have the sheet name appearing inside a cell in the spreadsheet – so for example with my invoices – I rename the sheet name with the invoice number, this then updates the invoice within the sheet.

To do this I use the following formula below.

This may seem a complex formula, and it doesn’t matter if you don’t fully understand it (I don’t), you just need to copy and paste this into a cell in the spreadsheet, and the sheet name will appear. If you change the sheet name, the cell will change accordingly.

The only caveat is, that the workbook has to have been saved at some point for this to work – so if you do this with a new workbook, it won’t work until it has been saved.

This technique is unique to Microsoft Excel, it doesn’t work with other spreadsheet tools such as Open office, Google sheets or Apple’s Numbers.

Changing behaviour with Office 365 and Tablet Devices

Over the last week I have produced 4 posts around ideas of using Office 365 in education:

  1. Using Office 365 to create collaborative learning activities.
  2. Using PowerPoint and Office 365 to create a collaborative learning activity.
  3. Using Excel and Office 365 to create learning activities.
  4. Using Word and Office 365 to streamline assessment feedback.

The idea that I am trying to get across, is that Office 365 isn’t just a slightly newer version of the Office Suite, instead it works in a fundamentally different way, but to get the benefit from these improvements, requires people to behave differently, and with the arrival and (I predict) rapidly increasing number of Microsoft tablets within the education system – and the mobility that these devices offer – getting Office 365 to work properly is going to be key.

The main behaviour change, is reducing the number of different versions of the same file that exist in different.

For example: If I create a Word Document, and save it to ‘My Documents’ on my computer. There is one version of the file. I then email this to a colleague. There is now the version in My Documents, a copy in my sent items, and one in my colleagues inbox – so thats 3 versions. My colleague downloads the file (4 versions) and then makes some edits, saving the file to their network drive (5 versions) they email this back to me (Their sent items and my inbox gives 7 in total) – I Download this (8 versions), and save a copy (with a new name) back to my Documents (9 versions).

So – the very simple example above with me asking 1 colleague to proof read and edit a document has created 9 files without me thinking about it. If I had collaborated with more people this number could quickly run into the hundreds – which makes no sense, there is a risk of ‘old’ versions re-appearing a later date, and the total memory used up on the system (especially if a large file) costs money and slows everything else down.

If I use Office 365 and OneDrive (the new name for SkyDrive) the behaviour would be as follows:

I create a file, and save it to my OneDrive. There is now a version on my computer and a synchronised version ‘in the cloud’, I email a link to this file to my colleague, they edit this via the web interface in the cloud, and let me know when complete. I can check their edits, and if unhappy with them I can use the history option to roll back to an earlier version or discard the changes. Ultimately though – we still have the same file on my Computer and a synchronised version ‘in the cloud’. There are no extra versions loitering in mail systems or download folders.

One of the reasons why this becomes so important with tablet devices, is they rely on files being transferred via wireless – so we need to make the whole process of file management more streamlined. If I am working with a class and I want them all to access an image rich  PowerPoint file for example – for me to ‘push’ that file over the slightly ropey wifi in that classroom to 30 different devices could take 10 minutes or more, which isn’t realistic. For me to use the file sharing mechanism within OneDrive and  the students to access this via the web interface will effectively stream the content, as the students view it, rather than pushing the whole file out up front.

In the previous posts I talked about the collaborative teaching and learning opportunities this brings, and with the extra mobility and battery potential of tablets over laptops or desktops, means we can use these devices in any environment including the sports hall, kitchen, on field trips etc.

As with many of my blog posts – the key to changing these behaviours is an investment in staff development. Because Office has been around for so long, Office training is far less common – there is an assumption (which is an incorrect one) that people don’t need to be taught basic office skills. With Office 365 – there is a huge need to reinvest in training in this area, otherwise its potential will not be unlocked.

At the moment I am working with a company called The Tablet Academy who provide training and consultancy around the use of tablet devices (Apple, Android and Microsoft) in education. If any Schools or Colleges in the UK (including Scotland) purchase 20 or more Microsoft tablets (including ones made by other companies) they are entitled to free training, provided by someone from the Tablet Academy (only downside is the current offer is only for training that is delivered before the 31st March 2014). If you work for a school or college in the UK and have purchased such device recently and your reseller hasn’t mentioned the free training offer then contact the Tablet Academy who will chase this up for you.

Using Excel and Office 365 to create learning activities

In my previous posts I have:

  1. Introduced the idea of Office 365 and how it can be used to create collaborative activities.
  2. Given an example of how PowerPoint can be used to create a collaborative activity.

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.

Image of an excel sheet with a data entry table on the left, and the resultant grpah on the right

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.

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.

Using a colour combination chart when creating resources

In my early days of teaching, and just as I was starting to get my head around the tools that were available to me (PowerPoint) – I was faced with a multitude of colours that I could choose as background or font. The problem is that certain colours don’t go very well together. Some are obvious – such as having dark text on a dark background, or light text on a light background (but I am still suprised how I often I see this mistake made), and others are less obvious like using green and red or blue and red.

Then I created a very simple tool that helped me when choosing colours, and saved myself time in the process. I created a grid where I had a variety of combinations of backgrounds, and fonts in each of the different colour combinations – by glancing at this, I can then see which colour combinations work better than others, without having to keep changing the settings until I get something that works. This grid was stuck to the wall next to my desk.

I also used this when I had a student with a visual impairment in my class. I took the grid to him, and asked him which colour combination he found best – he looked at the grid and quickly said black text on an orange background. So I quickly changed the colour schemes of my presentations for that unit (which because I had used the Master Slide was very quick to do) – and as a result of that (and other simple changes I made to my teaching) – in my sessions he didn’t need to have the note taker that he needed in all the other lessons he attended – which for him, was a wonderful experience (as well as saving the College lots of money).

The grid (which I still use) is available for others if they want to use, and can be located on my website towards the bottom of http://www.a6training.co.uk/resources_powerpoint.php. Although I originally produced this for the use of PowerPoint, this works with any technology where you have the option to change colours, and can be a really useful way of increasing accessibility of learning resources.

The video below introduces this chart.

Using a spreadsheet to automatically create a list of dates

Something that I witness far too often for my liking, is people manually entering a list of patterned dates into something – e.g. if someone is creating a scheme of work, where they want every Tuesday for a year, they sit there with a paper diary in front of them, flicking through the pages and manually typing in the date of every single Tuesday in the year. This is very time consuming, and prone to mistake.

Personally, I let the technology do the work for me, and usually spreadsheet software (e.g. Microsoft Excel).

Here is a short video showing 2 different techniques of how you can get Excel to create a list of dates that follow a pattern.