NOTE: These videos were prepared when the Census at School Project was managed by Statistics Canada. Most of the information is still relevant.
Duration: 9:23 min.
Description:
This episode focuses on importing your Census at School class dataset with the spreadsheet program Microsoft Excel and editing it for classroom use.
To access the Rich-Text Format (RTF) version, use the document conversion features available in most word processing software, or use a file viewer capable of reading RTF.
Download Transcript:
Video Transcript
Hi, I am Angela McCanny. This episode focuses on importing your Census at School class dataset into the spreadsheet program Microsoft Excel and editing it for classroom use.
There are a number of other spreadsheet programs similar to Excel, such as Open Office Calc, Star Office Calc, Corel QuattroPro and AppleWorks Spreadsheet. The steps shown here for using Excel will be very similar in these other programs. Similarly, the various versions of Microsoft Excel are largely the same, but you may find slight differences from the version that you are using to the version I am using here, which is Excel 2002. Experiment a little and I am sure you will be able to find the correct location and sequence of commands for your program. It’s not too difficult.
This episode will cover three topics in Excel:
importing the class dataset into Excel,
editing the appearance of the spreadsheet and
creating a subset of the data for printing or other learning activities.
Let’s start by importing the dataset into Excel. There are three methods for this.
Data import method 1: Download directly into Excel
The first method is downloading your class results directly into Excel from the Results page on the Census at School website. So, let’s go to the Results page now.
Go to www.censusatschool.ca. In the left hand menu, select Teachers and on the top of the page, click Sign in. Use your e-mail address and the teacher password you received from Census at School when you registered as a teacher.
At the bottom of the page, find the class whose data you wish to download and in the right hand column, select Results.
Now select: View or download results in Microsoft Excel format. You will have the choice to open or save. Click Save and a Save As box will appear.
In the Save in window, select a location your students can access, such as a shared folder for your school.
In the Save as type window, make sure you select the Microsoft Excel format rather than leaving it as a web page. Now click Save. You have now saved the data so that your students can begin to work with it.
This method is the simplest way to import your data into Excel, but it doesn’t always work, perhaps prevented by the downloading restrictions in your school. So method 2 – importing the data using the URL – is another method you can try.
Data import method 2: Using the URL
Start by going to the class results in Census at School, but this time do not click Download, instead, click View.
For this method, we are going to need to copy and paste the URL from the Internet address box. So, highlight the web address, or the URL, by clicking once in the address box – this should highlight the full address. Now, use “Control C” – hold down the control (Ctrl) button and the letter “C” at the same time. This will copy the address.
Now, we need to open Excel. Likely, you will find it on the Start menu, but if not, search your computer files or ask the IT person for your school where to find it.
At the top of the spreadsheet, select the Data tab. Pull down and select Get external data or Import external data (depending on your Excel version).
Pull down and select New Web Query. Paste the web address in the window’s address bar by clicking in the address bar and holding down the “Ctrl” key and the letter “V”. Then click Go.
At the bottom of the screen, click Import. You will see the question Where do you want to put the data? Click Existing worksheet, then OK. It may take a few seconds, but the dataset should appear shortly in spreadsheet format, organized into rows and columns.
As before, you will need to save the spreadsheet to a location your students can access. So, on the File menu, click Save As then choose a location in the Save in box, and save it in Microsoft Excel format. This is the end of the second method for importing data.
Data import method 3: Import from CSV file
The third method for importing your data is saving it first as a comma separated values file, or a .CSV file, and then opening it in Excel. This method works very well for the international data on the Census at School website, so it is a useful one to know.
We will start method 3, once again, on the Results page of the Census at School website. This time, click: Download results in CSV format.
In the dialogue box, select Save. Then in the Save in box, select the Desktop or another folder. Do not open this file, as it is not yet in a readable format. We now need to open it in Excel.
Open Excel on your computer. At the top of the spreadsheet, select the File menu. Pull down and select Open.
We are now going to find the .CSV file we just saved. So, in the Look in box, pull down and select the location where you just saved the file; for me, that is on the Desktop. If you cannot see the file there, click in the Files of type box, and select All files. And now click on the .CSV file you saved earlier. In the dialogue box, click Open.
Finally, use the Save as command to save this file to a location your students can access from their computer log-in number, perhaps your school’s shared drive. The file is now ready for your students to work on.
After the class dataset has been imported into Excel, it can be customized for your teaching needs. You may wish to edit the spreadsheet by eliminating columns or rows that contain unimportant information. Let’s see how we can do this.
Edit the spreadsheet
Open the saved Excel spreadsheet. Spreadsheets are made up of little boxes containing data, called cells. These are organized into columns and rows, with letters at the top of each column and numbers at the beginning of each row. Each cell is named by its column and row, so this cell is B6 and this one is E3.
Some of the columns have information that is not important for student analysis such as the Student ID number in column A. To delete this column, click on the letter name at the top of the column. This will select the entire column. Then, go to the Edit menu, and click Delete. I usually delete the Province column as well, since all the students in your class go to school in the same province, so this one is not necessary. I also delete the last column containing the Date submitted.
As for rows, this version of the spreadsheet contains some blank rows. These will cause difficulties with some of the graphing techniques, so it is best to delete them. Click on the number at the beginning of the row, go the Edit menu and click Delete. Also, be on the look out for rows that contain completely duplicate data, which would indicate that a student submitted his or her information more than once. You can delete these duplicate rows as well.
If any of columns are too narrow to display the data completely, you can resize the columns by clicking in the blank box in the top left corner of the spreadsheet, between column A and row 1. This will select the entire spreadsheet. Now, go to the Format menu, click Columns and choose Autofit Selection. The columns will automatically resize. There – that looks a little better.
Create a subset of the data for printing
For some student activities, I like to create a subset of the large class dataset on another worksheet.
First of all, we need a place to create the new spreadsheet. At the bottom of this spreadsheet are various tabs. The first one contains the class dataset and it can be renamed to reflect this. Click on the Sheet 1 tab, then right-click and choose Rename. Type “Class dataset”. Sometimes your spreadsheet will have additional tabs like this one. If so, click on Sheet 2 and there is a blank worksheet ready for creating the data subset. If you do not have additional blank worksheets, then go to the Insert menu and click Worksheet: a blank spreadsheet will appear. To place it after the class dataset, just click on the tab and drag it to the right spot.
Now choose the data you want to copy onto the new worksheet. Suppose we start with gender. Click on the letter A to select the entire column, then the Edit menu and Copy (or use “Control C”). Click on the Sheet 2 tab, click in cell A1 and on the Edit menu, click Paste (or use “Control V”). Voila! There’s the data.
To copy more columns of data, click on the Class Dataset tab again, click on the letter name of the next column you wish to copy, and paste it into column B on Sheet 2. Repeat until you have the desired data on Sheet 2. Rename this if you wish and save it. You can print this page or leave it as a file for your students to use on the computers.
This is just one of the many ways that spreadsheet programs can help our students with data exploration.
