Custom Search

Lesson 2: Making a Simple Spreadsheet

We are going to make a simple spreadsheet of the type that a teacher might use to keep record of test scores.

Here is a copy of the results of the last three tests taken by Professor Snape's Advanced Potions Class

When you click on each of the scrolls you will open a tab with a magnified image of each scroll so that you can read the data easily.

Our task is to help the good professor assess the progress of his students by putting the data from the scrolls into an Excel Spreadsheet.

We will use the spreadsheet to organsise the names into alphabetical order.

In the next lesson we are going to get the spreadsheet to work out the average percentage for each test and the average percentage for each student.

So, let's get started!

 

Open a new spreadsheet and call it Potions_NEWT

Now take a look at the first scroll of data. We have three types of data there - the name of the test, the names of the students and the marks the students attained in the test.

If we look at the next two scrolls we see a similar set of data. The common data is the names of the students - that is the same in all three scrolls - as they are his 'NEWT' class.

We have to think how best to lay the data out - with practice you will do this without thinking about it very much - but at this stage lets put thought and planning into the task!

We will put the names in the first two columns - forename then surname. So head those columns up as Forename and Surname. To make the heading stand out make it bold. Select the cells A1 and B1 and then click the B for bold in the header.

Then using the first scroll type in the student names.You will find that you have to adjust the width of the columns to fit in the names.

Adjusting column width

Position your cursor arrow on the line between the column headings (the A B C D etc). You will see that the cursor changes shape from a pointer arrow to a vertical line traversed by a double headed arrow. Left click on the dividing line and drag it to make it the width you want.

We now need to sort the names into alphabetical order. We want the A and B data to stay together, we therefore have to select ALL of the name block.

Sorting data into alphabetical order

Highlight the cells you wish to sort. Click on Data in the heading bar and select 'Sort'

... another window will open...

We want to sort by surname - then forname and we want the data to list in ascending alphabetical order. There is a header row (the heading names we have inserted) - we want that to remain where it is and not be sorted with the names - we therefore have to select the radio button that says there is a header row.

Click 'Okay' and the data will sort itself for you!

Now let's head up the next three columns with the names of the three tests. These are quite long, but the data we are entering is only a few digits in size. We will have to wrap the text - make the text occupy several lines within the cell. The problem we will then encounter is that the row will not be high enough for us to see all of the text. We will therefore have to adjust the row height so that all of the name is visible in a narrow column.

Wrapping Text

Highlight the cells you want to format as 'wrapped text'.

Click on 'Format' in the header bar and select 'Cells' and a window will open. It has several headers within it. You need to select 'Alignment'

Tick the Wrap Text selection and choose the centre alignment within the cells for your text. You can then adjust the row height and column width to suit your purpose.

Adjusting row height

Position your cursor arrow on the line between the rows (the 1 2 3 4 etc). You will see that the cursor changes shape from a pointer arrow to a horizontal line traversed by a double headed arrow. Left click on the dividing line and drag it to make it the height you want.

Beneath each test name put the result that each pupil obtained for that particular test.

Select them and centre to data.

Now save your spreadsheet - you will need it for the next lesson!

Here is a copy of my spreadsheet: Click here to download