You are here:
- Home »
- Video gallery »
- Gathering and using evidence for learning »
- Cleaning, sorting and merging data (using Excel)
Related videos
Cleaning, sorting and merging data (using Excel)
« Return to Cleaning, sorting and merging data
Key content
This tutorial will show you how to clean, sort and merge data using Excel. Making sure that data is clean before you start to work with it will help prevent misinterpretations, or having to go through the process again if you discover problems further down the track.
When working with data to analyse results and draw conclusions, it is essential that the data with which you are working is "clean". This means that it is:
- consistent – is all the data in the same format and does matched data refer to the same students?
- accurate – are these the right records, the right results? Does anything look out of place?
- complete – are there students missing? Are there NSN numbers missing?
You should be alert to any potential problems, either within the numerical data itself or the demographic information attached to it. For example, a student number may be missing, or a cell has no information, or a year level for one student is incorrect.
Transcript
Cleaning, sorting, and merging data using Excel.
This short tutorial will show how you can prepare two sets of data. For example, two sets of results for two classes into one so that you can then report on the total set and create graphs.
This spreadsheet contains two data sheets, one for each class.
You will see they are different in structure and that class two has some extra columns.
Names show as First Name – Last Name rather than Last Name – First Name. And names are not sorted alphabetically.
In order to merge them, they need to be structured the same. So we need to do some preparation of class two data to be able to merge it with class one data.
Before starting, it’s a good idea to expand all columns to fit the width of the data. For example, you will see that the Ethnicity column is currently too narrow to show all the values and the dates only display as hashes.
You can expand data in a column by double clicking on the right hand border of the column header cell.
The first thing that needs to be done is deleting some of the columns to get the data in line with class one.
So in class two, we will delete all columns to the right of the Score column.
Select all columns, right mouse click and select Delete.
Secondly, we need to switch the name columns so that the last name comes before the first name. To do this, you will first need to create an empty column to the left of the First Name column to paste the Last Name data into.
Highlight the first name column by selecting on the header.
Right mouse click and select Insert. This will insert an empty column to the left.
You can now copy the Last Name data by selecting the column and cutting and pasting it into the empty column. Alternatively, you can drag the whole column.
To finish off, delete the empty and redundant column.
The data is now ready to be merged with class one. To do so, highlight the block of data, being careful to exclude the header cells and right mouse click to select the Copy option.
Go back to the class one worksheet and select the first row of cells after class one data.
Right mouse click and select the Paste option to append the class two data to class one.
You should now have a combined list.
Lastly, the list will need to be resorted to have the whole list ordered alphabetically by last name.
To do this, highlight the Last Name column, then go to the data menu and select the A–Z Sort option.
In the sort warning dialogue box, keep the option 'Expand the selection selected' and click on the Sort button.
This means that all data will be sorted by last name, not just the Last Name column.
You can rename your worksheet to reflect that the data has been merged by double clicking on the worksheet name and renaming it.