spreadsheet report

In order to make the database work, data is needed. The data needed for the first sheet are the surnames and forenames of the pupils, their expected marks, the marks obtained in the four module tests, and the marks needed to obtain each different grade. The second sheet requires the pupil’s surnames and forenames, their attendances, and the dates of the lessons. The data would be obtained from the science teacher himself, but as he does not really exist, I shall have to create the data myself.

Once I had created all of the data, I started to set up my spreadsheet. I started up the spreadsheet program we have at school, Microsoft Excel 2000.  A new workbook was automatically created, so I saved it to M drive as ssproject.xls. My first task was to rename the sheets. To do this, I right-clicked on the tab at the bottom of the screen called Sheet1. The menu to the left came up, and I clicked on rename. I could now type in the new name for the sheet, Tests. I then repeated this for Sheet2, renaming it Attendance. I then deleted Sheet3, as I had no use for it. The tabs at the bottom of the screen now looked like this.

I then started to construct the tables. I clicked on the Tests tab to select the Tests sheet. I entered the title of the sheet in cell A1, made the text larger and made the row larger by clicking on the line in-between Row 1 and 2 and dragging the line to where I wanted. I made the row large enough so that the title could fit into it. I then entered the column headings in Row 4. I then selected the three cells above the first group of column headings reading Marks, Grade and Attainment. I merged the cells by clicking   in the formatting toolbar. Merging the cells is when you turn several cells into one. I did this for each group of three cells above the Marks, Grade and Attainment. I then entered Test 1 in the first merged cell. I highlighted the cell, and then clicked on the small black square in the bottom right hand corner. I then dragged this to the right until all 4 merged cells were highlighted. I released the mouse button, and the 4 merged cells now read Test 1, Test 2, Test 3 etc.

Next, I highlighted all of the text, then clicked on Format>Cells. I centred the text both horizontally and vertically. This put the text in the centre of each cell.

I then moved to the other sheet, Attendance, and set it up in a similar way. I added a bold, underlined title, and made the row height larger. I then added the class name in a bold font. I then entered the column headings as shown below. In order to make the dates lie on their side, I selected the cells they were located in, and went to the Alignment tab in Format>Cells. To the right had side of the box, there is a section called orientation, a screenshot of which is shown to the right. To make the text rotate anti-clockwise 90 degrees, I typed 90 into the degrees box. If I had wanted the text to rotate in the opposite direction, I would have entered a minus (-) before the number. I also wrapped the text in the final two columns, by going into Format>Cells>Alignment and selecting wrap text. In this menu, I also centred the text in all of the column headings horizontally and vertically. In order to make the text fit perfectly into the cells; I again altered the column widths and row heights so that the text fitted snugly. Once all of this had been done, I returned to the Tests sheet. I saved the work, as I did very often thought the rest of the time that I was working; but I also saved a backup copy on a floppy disk. In order to differentiate between the original file and the backup file, I inserted the suffix of _bak, which is a standard way of doing this. An example of this is shown to the left.

The next task was to enter the names of the pupils. The names were made up, and they were first entered into the Tests sheet. To save me from typing the names into the Attendance sheet as well, I selected all of names, pressed the copy button on the task bar, and pasted the names into the correct place in the other sheet. Once this was done, I needed to set-up the grid lines that would make the sheets easier to read and more professional. To do this, I selected the cells, which I wished to have lines dividing them. I then clicked on the gridline button on the toolbar, and selected the type of gridlines that I wanted. The ones that were the most suitable were the ones shown selected in the screenshot above. This put gridlines on the borders of each cell in the selection. One I had done this a few times, due to the shape of my table, the sheet looked like the screenshot below.

I repeated adding the grids for the Attendance table.

With the layout mostly complete, I could now begin to add the data.

I entered the expected marks, as shown to the left, in the column labelled Expected. I then entered the marks that each pupil had received in heir tests, in the columns labelled marks, as shown to the right.

My attention was next focussed to the VLOOKUP functions in the Grade column. In order for this to work. I needed to set up a points table. I typed a bold, underlined title a few cells underneath the Results table. Below this, I set up a two by seven grid, with gridlines. The cells were formatted, by going into Format>Cells>Alignment, to be centred horizontally and vertically. The grades were entered into the right hand column in ascending order, as were the corresponding required marks in the left hand column.