Spreadsheet Technical report. My task was to produce a grade book for primary school teachers; the aim of this spreadsheet was to allow teachers to input scores and record student progress.

Laura Jayne Ford 10JB

Spreadsheet Technical Report

Archbishop Blanch CE High School

Spreadsheet Technical Report

Teacher Grade book.

My task was to produce a grade book for primary school teachers; the aim of this spreadsheet was to allow teachers to input scores and record student progress. In this report I will attempt to explain the processes I went through to produce the grade book.

Step 1

In order to create a professional, user friendly grade book, I firstly wrote a list of the items I wanted to include in my spreadsheet using Microsoft Word, before I started creating it. I did this so that I could make sure I had included all the functions required, as I changed their colour from red to black once I had completed that function.

Step 2

Once I had completed this, I wrote down what functions/data each individual page would contain, and the order each worksheet should go in, in Microsoft Word.

I did this because it allowed me to have a chance to analyse the amount of information and functions which were needed on each worksheet. It also have me the ability to understand the order in which my worksheets should follow, for example, if I had put the maths report after the english worksheet instead of the maths one, then it would not have run in a logical sequence of events, and would have most likely confused the user (the teacher.) I did the worksheet layout before creating my spreadsheet, as if I had not prepared and gave a structure to my Spreadsheet beforehand, I may have made numerous mistakes and I would have had to of gone back and changed it afterwards, or more unfortunate, maybe I would have had to of started a new spreadsheet altogether.

Step 3

Once I had finished and knew what I was going to include, I was then ready to tie it all together and form a professional grade book in Microsoft Excel; the reason why I decided to create a grade book in Excel is because It is easy to use and can easily be changed, unlike Microsoft Access, where if you make a database and accidently make a mistake it is a long process to actually fix the problem, where in Excel it only takes a few steps. Additionally, the uses of formulas allow any changes in the original numbers to be automatically recalculated through-out the whole worksheet/workbook, unlike most Microsoft packages. This feature is highly useful to a grade book, as it means that student results can be entered into the spreadsheet, and the spreadsheet will automatically recalculate functions which you have included e.g. the average, max, min etc providing the calculations are correct, therefore making the process easier for the teacher, as she doesn’t have to change them herself. Furthermore, data can easily be formatted creating attractive, readable tables, charts or text, which is an advantage for the teacher as it allows her to easily see if her students are doing well or not. Moreover, text can easily be organised which is another advantage to the teacher, as she does not have to organise it herself, before entering the data into the spreadsheet. In additional to this, spreadsheets allow the teacher to easily enter student results, instead of having to write them out by hand. Additionally, Excel is a portable application; it can be sent through

e-mail, stored on a USB stick or synchronised with PDA’s, not only that but Excel can also be password protected for extra security; this can be created directly in the Excel file. This is also an advantage as the teacher may want to keep the data safe and protected, so nobody can change or delete data within the file by accident. Due to the advantages of Microsoft Excel, I believed that it was the correct package to use considering my task provides and my target audience.

Step 4

After deciding what package I was going to create the grade book in, I started to create my spreadsheet. Once I had created a new workbook, I started working on worksheet 2 instead of 1; this was because I had left the first worksheet blank to begin with, as I was later going to turn it into my front page and menu. The reason I did not create the front page first was because I could have changed the spreadsheet layout from what I had planned, during the process of creating it, therefore it would mean I would have had to of gone back and changed the front page and the hyperlinks on it, which would have created more work and wasted time. My first task was to rename the first worksheet so that I knew what that worksheet was going to be used for, I did this by:

Right clicking the tab where it says “Sheet 1” and then clicking on “Rename”

Once I had clicked on “Rename”, the tab turned black and allowed you to type in your preferred name. The tab then turned back to white, once I had finished typing the name “menu” and had pressed enter.

Step 5

Next, I decided I wanted three tables which included the headings I required, in worksheet 2. I decided this worksheet was going to record students’ progress in English, each table had its own purpose they were split up into; term test results for reading, term test results for writing and overall English grade.

First of all, I started to create the table for term test results for reading; I started by typing in the column headings I wanted. My first column heading was student name; I realised that it was important that I included their forename and surname, so I merged the first two cells in row 1 together by:

High-lighting the cells I wanted to merge.

Clicking on the icon which shows an image of an “a” inside 2 cells. If you hover over it, it should say “Merge and Centre.”

The two cells are then merged.

Step 6

After I had merged the two cells together I then wrote, “Surname and Forename” underneath as headings, and entered in pupil names. Once I had entered all the names, I decided to sort them into alphabetical order. To do this I followed these steps:

1) High-light the text you want to sort.

Click on “data” which is displayed in the top toolbar.

Then decide how you want your data to be sorted, either in ascending or descending order and which column you want to sort by. I decided to have my data sorted by the surname in ascending order, as student names are often organised in this way in most schools.

Step 7

I then added the headings I wanted in my table, by typing them into the specific cell. I chose to include test results from 3 terms; once I had inserted the test results, I decided to work out the overall percentage from the 3 tests. I knew that to calculate the overall percentage I must add the test scores up and divide by the collective amount all 3 tests were out of. To do this, I did the following steps:

1) Added the 3 test results together by clicking in the cell I wanted the percentage to be, and clicking “AutoSum,” which is represented by the oddly shaped E, in the top toolbar. I the entered the cell range the 3 test results were.

2) I then divided the added amount by 300, which is the amount which all 3 tests collectively, were out of (300.)

3) I then copied this formula into the cells below, to prevent me from having to write the formula in every cell manually. To copy the formula I hovered over the bottom right of the first cell, and waited till a black plus sign appeared. Once it had appeared I grabbed it and pulled it down, high-lighting all the cells I wanted the formula to be present in. To turn all the values in this column into percentages, I high-lighted the values and clicked the percentage button in the top right-hand corner of the toolbar located at the top of the worksheet; this then converted them into percentages.

Step 8

After I had completed that, I inserted the target grades for each child under the column heading, “Target Grade.” I did this so that I could later make a formula to tell me if the student had exceeded or met their target or if I had to inform their parents. To do this, I first had to create a nested If function to tell me what grade the child had actually achieved, depending on their percentage. To do this, I did the following:

1) I clicked on the cell I wanted to create the formula in, and then clicked the “Fx” icon next to the formula bar.

2) When the “Insert Function” box appeared, I found the “IF” function and clicked on it.

3)A “Function Argument” box then appeared. In the space next to the words, “Logical_test” I started to create the formula I needed.

I wanted to create a function which told me, if the person had achieved 80% or more, they had got a level 6, if the student had achieved 70% or between 70% and 80% they had achieved a level 5, if the student had achieved 60% or between 60% and 70%, they had got a level 4, and if they had got less than 60%, they had achieved a level 3. This took me a few attempts to get right, as one little mistake upset the whole formula, but in the end I had worked out the ...

This is a preview of the whole essay

I then put this formula into the “Logical_test” in the argument box and pressed ok.

Following that, I then created the formula which told me whether the pupil had exceeded or met their target or whether I needed to inform their parents. Again, I had to complete another nested if function; I followed the same steps as before, only changing the formula. The formula said that if target grade and achieved grade were the same it would say “met, if the target grade was greater than the grade achieved it would say “inform parents,” and if the grade achieved was greater than the target grade it would say “exceeded.”

Furthermore, I decided I wanted to add a merit system, which adds up all the merits the child has received depending on whether they have exceeded, met and not met their target, in each specific area, and totals them up on the annual report and says what gift voucher they will receive. In order to do that I must make a nested IF function to say how many merits the child will get depending on percentage gained. So, I followed the steps shown previously and wrote a formula which said, if the child exceeded their target they got 2 merits, if they met it they got 1 and if the teacher needed to inform their parents they got 0:

Step 9

I then put a border around the information I had just entered, to show that it was one table and to make it stand out. I completed the following steps to do this:

1) I high-lighted the area I wanted a border round.

2) I then clicked on the arrow next to the icon which showed a picture of a box cut up into 4 pieces. This then showed all the border options. I chose the border which showed all the lines coloured black; the reason I chose to put a black box around each cell in the table is because it made the table stand out more and made it easily recognisable, especially once I had included the other 2 tables.

Step 10

After I had created a table for “Reading,” I then created a small table underneath the term grades and overall percentage. In this table

I calculated the max, min and average of the scores. The reason for doing this is it allows the teacher to easily see at a glance what the lowest and highest score was, and the class average; from seeing this data she can then tell if the class is doing well or if extra work is required. To calculate the max, min and average, I completed the following steps:

2) I firstly wanted to calculate the max; I clicked on the cell I wanted to complete the function in, and clicked on the arrow next to the sigma symbol. A drop down list then appeared showing the different functions. I then scrolled down to where

it said “MAX” and clicked on it.

2) This then inserted the MAX function into the cell. Excel often inserts the cell range which it thinks you want to use, but it is important you check the range, as it could possibly be different to what you actually want it to be. To change the cell range just high-light the range you would like, or type the range into the function, either in the formula bar located at the top of the worksheet or into the actual cell itself. I then copied the formula across to the adjacent cell situated to the right, by using autofill.

I then followed the previous steps again to calculate the min and average; only clicking either min or average from the drop down list. I then put a border around the table to show that it was separate from the “reading” table, and to make it stand out.

Step 11

Following that, I copied and pasted the two tables and pasted them next to the “reading” table; these two new tables were for “Writing.” I then changed the scores for each term; fortunately all my functions were correct so they changed the values automatically. The only alteration I needed to make was changing the cell references inside the functions as they were now different.

Step 12

Once I had completed that, I then pasted the 2 tables again but this time next to the “writing” table; these tables were for the “Overall English Grade.” The only alteration I made during the copying and pasting process was by copying the student names also. I did this because the teacher would have to of kept scrolling across the worksheet to see which student got which grade, as you were unable to see the student names from were the “Overall English Grade” table was positioned; therefore it was easier for the teacher to have the student names next to the table. I then changed the formula for the overall percentage, as this time I had to add up the term scores for both reading and writing and divide it by 600 not 300. I did this by following the steps shown in “Step 7,” only changing the values. Again, I just needed to change cell references in certain functions. However, I did add an extra two columns, so I could analyse which area the child needed to improve if they did not reach a certain level, and the number of merits they had received from doing the tests. First of all, I wrote a nested IF function to tell me what area the child needed to improve (either reading or writing), depending on if it had said I needed to inform their parents. To do this, I followed the steps high-lighted in “Step 8,” only inserting a different formula. I wanted the formula to say, if it had said “inform parents” in the writing table, I wanted it to write “writing” in the new column, if it had said “inform parents” in the reading table, I wanted it to write “Reading” and if it did not say “inform parents” in either table then to write “not required.”

The only problem with this formula is that, if it said “inform parents” in both tables, it does not tell it to write “both”. If I was to re-write this formula again, I would add that section in, as there is a high possibility that a child may need to improve in both areas, and it may confuse the end user if it does not do so.

Step 13

I then added another column to calculate the total number of merits. I did this by:

1) I high-lighted the cell I wanted the formula to be in, and typed in an equals sign. This tells Excel that I want to write a formula. I then clicked on one of the cells I wanted in the formula, inserted an addition sign and clicked on the cell I wanted to add to it, and pressed enter; this then added the two cells together, and displayed the result in the high-lighted cell. The reason I did not use AutoSum is because, I was only adding 2 cells together and AutoSum is more often used if quite a lot of cells are to be added together; as it is quicker and more efficient.

Step 14

After that, I then added conditional formatting to my worksheet. This allows the teacher to see at a glance if the class is doing well, and the students who need help, due to the colour of the cell e.g. green if students got greater than or equal to 70%, amber if they got between 50% and 70%, and red if they got less than or equal to 50%. I did not want to conditional format the whole worksheet, as it would have made the worksheet untidy and unprofessional; therefore I only added it to certain parts of my tables. To complete the conditional format, I did the following:

I high-lighted the cell range I wanted to conditional format, and clicked “Format” in the toolbar above, a drop down menu appeared, and I scrolled down to where it said “Conditional Formatting…” and selected it.

2) A conditional formatting box appeared, asking for me to give a condition so it could format particular cells for example, I only wanted the cells containing the term test results for both reading and writing filled red if the person had got marks between 0 and 50, as this allows the teacher to see instantly who needs help. To format the specific cells, I clicked “format” on the right hand side and chose the colour/border I desired (red,) I then typed in my condition changing the text in the top boxes to suit my needs, I did this by clicking the arrows next to them and selecting the text which I required. Once I was happy and checked the condition, I pressed “Ok.” In addition to this, you can also format cells using a formula. I created a formula for the “Grade Achieved”, “Grade Met?” and “Merit” columns, each with their own formula; to create a formula I selected “formula is” from the first box, this then allowed me to type my own formula. In each of these formulas I only wrote one condition, as I did not want the worksheet to become chaotic with different colours; I only wanted to high-light important aspects of it e.g. cells which said “inform parents.” However, I decided to conditional format the percentage column, to show colours ranging from green to red, this was to show if the majority of the class were doing well e.g. the column was mostly green, or if the class needed more help and support e.g. the column was mostly reds and ambers. In order to add another condition/colour/range, you follow the steps above, but instead of clicking “Ok” once you have completed your first condition, you click “add” which brings up another condition which you fill in and re-format. Unfortunately, it will only let you have up to three conditions, but in my circumstance I only wanted to have three conditions, so this was not a problem. I added more conditional formatting through-out my spreadsheet, changing each one to suit shown in the steps above, but making sure I did not over the function to prevent it from looking unprofessional

Step 15

Once I had completed my conditional formatting, I was then able to count how many cells were green, amber or red in the overall English percentage column; I did this via a “COUNTIF” function. To do this, I did the following:

1) I high-lighted the cell I wanted to complete the function in, and clicked the “Fx” symbol next to the formula bar.

2) An “insert function” box appeared asking for you to select your preferred function. I selected “COUNTIF” and pressed “ok”.

3) Then a “Function Argument” box appeared on the screen, asking what I wanted it to count. In the range box, I clicked on the square icon in the far right corner; this minimised the whole box, so that I could high-light the range of cells I wanted it to look at, without difficulty. Once I had selected my specific range I clicked the square icon again, to maximise the box on the screen. In the criteria box, I typed in what I wanted it to count, in this case I wanted it to count all the percentage which were greater than or equal to 70%, so I typed in “>=70%” and pressed ok. This then displayed the amount of percentages which met this requirement, in the cell I selected at the start. I then followed these steps to count the number of ambers, and how many student met or exceeded their targets, and the amount of parents that need to be informed if their child is not doing as well as they should be.

Step 16

Following this, I made a VLookup table, which looked up the pupil surname and showed their percentage scores in each area and their overall percentage, it also included the grade they had achieved and if they have met/exceeded/not met their target. To start off with, I made a drop down menu listing all the student surnames, I did this by:

1) High-lighting the cell I want to create the list in, and clicking “data” in the top toolbar. A list then appeared offering me different options, I then selected “validation.”

Under the validation criteria, I clicked the arrow next to the “allow” box and selected “list.” Then I scrolled down to the source box and entered in the cell range which beheld the information I wanted in my drop down list, and pressed ok. My drop down list then appeared in my selected cell.

The reason I made a drop down list was because it saves the teacher time and effort in typing a students name into the lookup table, every time she wants to search for a particular pupil. Furthermore, if two pupils had the same name, it would allow the teacher to look at both students by displaying the two surnames and allowing the teacher to click one depending on which child she wanted to look at, whereas if I did not have a drop down menu and just a VLookup table, then it would not have displayed both student records. The reason I did not create a student number for each child is because the teacher probably will not be able to remember the number each child is, and will have to go to the top of the worksheet to find out what number that particular child is, so it is easier to have the “Surname” as the main column and lookup value, as the teacher will know the child’s surname. Also the teacher may not know how to spell a particular name so instead of guessing or looking through the list of student names to try and find the spelling, she can just look at the drop down list and click the one she wants. The reason I decided to include a VlookUp table in my spreadsheet was because it allows the teacher to look at a particular student, without having to scan through the student names and look across each results table, she can just click the name she wants from the drop down menu and the VLookUp table will show student percentages, achieved grades and merits. Additionally, the VLookup table is more reliable, as it will show all the correct data a child has received, but if the teacher did not have a VLookUp, then she would have to scan across the different results tables and she could accidently mix up the scores. To complete the VLookup I did the follow:

I selected the cell I wanted the results to appear in, and clicked on the “Fx” icon situated next to the formula bar. This icon is used to represent formulas; formulas are often very useful as they work out specific calculations which speed up certain tasks which are required in your job for example, an if statement allows you to type in the criteria you want so that it can say a certain thing if something is proven right or wrong, and lets you copy it to numerous cells, whereas you may have had to see if something was right or wrong yourself, and type a specific thing in manually each time; therefore functions speed up certain processes.

Once I had selected the formula icon, a box appeared listing all the different functions you could choose. I selected “VLOOKUP.” If the function you are looking for is not in the list provided, then most likely it is not presenting the full list of functions, it is maybe showing “the most recently used.” You can change it to show all the function that are available by selecting “All” from the drop down menu, indicated by the arrow next to “select a category.”

Once I had chosen “VLOOKUP” a “function argument” box appeared, asking me to insert my specifc information so it could complete the VLOOKPUP.

I inserted the data into the specific criteria boxes: The “Lookup_value” is the cell you want to look up e.g. you may put a student surname in that cell and want it to look it up. The “Table_array” is the cell range your data is in, for it to look it up e.g. surnames, forename, test results. The “Col_index_num” is the row number you want your answer to be within for example, if I was looking up surnames, and I wanted the forename, and the forenames where in column B, then I would type in 2; the reason I would type in 2, is because you must type in the number of the row instead of the letter e.g. A would be 1, B would be 2 etc. However, if my cell range was from column G to J, then column G would be 1 etc. At first, I did not know that if you started your table range from a different column other than A, then the first column of the new range would still start at 1, so it took me a while to figure out this concept, as every time I kept doing the VLOOKUP, it displayed the wrong information, and I did not know why. I found this out, by researching VLOOKUPS on the Microsoft Office website, in order to help me. Once I had inserted the correct data, I clicked “Ok.” I then copied the “VLOOKUP” to the adjacent cells, using Autofill (by dragging the black cross situated in the bottom right cell corner, across the specific cells – I changed cell references within the formulas when necessary.)

Step 17

After that, I made a key showing grade boundaries, and what the different conditional formatting colours meant. This was to help the teacher understand and know which children needed help and which were doing well. Additionally, the reason I put the table showing grade boundaries into my spreadsheet, is so that the teacher knows, without having to look it up, what percentage each child needs to get, and will know which children will need help to reach it.

Step 18

Once I had done this, I decided to colour code the 3 different results tables, so that they were easily recognisable and so the teacher can easily locate the specific results table she wants to look at. Additionally, in my annual report, I have created a filter so that the teacher can easily see which children need help, and within this filter I have included a conditional format, which related to the result table colours, so that the teacher can easily relate back to them. To fill the top cells of each result table, you do the following:

High-light the cells you want to colour, and click on the icon showing a picture of a pouring paint bucket(the fill colour icon,) this is situated in the top right hand corner of the toolbar. Once you have clicked on the icon, a box displaying all the different colours appears, and then you just click your preferred colour.

Step 19

I then wanted to create a report which the teacher can easily print off and send to parents; so I created a new worksheet by right clicking any worksheet tab already situated at the bottom of the screen, and selected “insert.” Once the insert box appeared displaying all the different options I clicked “new worksheet.” During the process of creating my report card, I decided I wanted to include a drop down menu, a VLOOKUP system and an If statement. I completed these tasks by following the steps described in previous steps, only this time, I had to work between sheets, as my data was on the previous worksheet. To do this, every time the formula asked me to insert the cell range, I put the name of the worksheet I wanted it to look at followed by an “!” mark, followed by the cell range; this then allowed me to work between the two sheets. However, I only needed to do this in certain formulas e.g. the VLOOKUP table, but I did not have to do it during my IF statement as it allowed me to select a different sheet whilst I was creating the statement.

Step 20

Following that, I decided to include charts, so that the teacher could easily see which children were doing well and which required help, without having to look through the results, which would consume time. As by using a chart you can instantly see whether the class as a whole is doing well, and which subjects the class is best in, and what areas need to be improved. To create a chart, I did the following:

I high-lighted the data I wanted my chart to display, however, my data was not situated next to one another, so I had to hold down the “Ctrl” button on the keyboard, and then high-light the data I wanted, as this allowed me to select data from different columns. I then selected “insert” from the top toolbar. Once I had selected that, a drop down menu appeared offering me different options; I clicked “chart.”

A chart wizard appeared, asking me what type of chart I preferred. I decided I wanted to display my data in a bar chart, so the teacher could see how each child did in each subject, and how they related to each other, and I could only do this in a bar chart, as it displayed the different percentage bars for each subject against the student name. Once I had selected the chart type I wanted, I clicked “next.”

A table showing a preview of your chart is displayed, so that you can check to see if it is correct before you produce it. Once I had checked my chart, and decided if I wanted it in columns or rows I clicked “next.”

After that, a box appeared, asking me what I wanted to call the chart, and the names of the axis. In addition to this, numerous tabs are located at the top of the Chart wizard box, asking me what settings and formats I wanted my chart to have. Once I had chosen each of these things, I selected “next” located in the bottom right corner of the box.

Finally, a box appeared, asking where I would like my chart to be located. I decided to have my chart as an object in an existing worksheet; I had decided I would have a worksheet, which would hold numerous graphs on it; showing percentages, number of student who had exceeded, met or needed to inform parents and how many students’ percentages were green or amber. I created each chart by following the steps above.

Step 21

After creating my charts, I decided to put a validation on my results tables, as this prevents the teacher from typing in a ridiculous number by mistake, and having to waste time later on correcting the mistake. The validation is a function which allows you to put a warning on particular cells which contain vital information which need to be within a certain range. Validation is useful as it automatically tells you if you have typed in a wrong value, this helps you to not insert incorrect data.

This warning is to let users know what type of data is to be entered e.g. specific range; it also allows you to put an error message on the cells if you enter a number which should not be entered in those cells. To complete a validation, you do the following:

High-light the cells you want to validation to be present within, and select “Data” from the top toolbar. It will then give you a drop down list offering you different options – select “validation.”

It will then ask for the validation criteria, you then select or enter in your requirements, either using the drop down menus, or your results table to insert your data ranges. The other two tabs located at the top of the box, allows you to type a message, either to tell the user what should be entered in the specific cells before typing in the data (this is referred to as the “input message”) or to show an error alert, which tells the user they have typed something incorrectly into the cells (this is referred to as the “error alert.”) Once you have typed in your input message (this one is optional) and your alert message, you press “ok.” I decided to only put an input message as well as an error message, on my first English results table, as I believe that it would annoy the user if the input message kept appearing every time you clicked on a cell, so I decided just to put one in the first table just to show the user initially what to enter into the cells.

Step 22

Once I had included all the formulas and data I wanted in my English worksheets - including reports and charts, I then copied all the formulas and data I had created, which are shown in the previous steps, onto new worksheets, including reports and charts. This work was then used to create data for maths and science. I changed the results for each subject, and because I had included formulas which recalculated each function every time new data is entered, it automatically changed the rest of the worksheets. The reason why I copied and pasted this information, is because I needed all worksheets to be the same for each subject so that they would synchronise and would not confuse the user, as if I had different calculations on each worksheet, then the teacher would have to change her method of inputting data each time, therefore it is not very professional and efficient to have different worksheets doing different things especially if they all need to be doing the same task. I changed the names of the new worksheets appropriately so that the teacher knows what each worksheet is used for and can easily select the worksheet she specifically wants, I also inserted worksheets accordingly.

Step 23

After I had completed worksheets showing data for each subject including reports and charts, I decided to include an annual report, which summarised which students required help; number of merits each child had got and the gift voucher amount each child had received depending on how many merits the child had gained; how many students had exceeded targets, and the bonus amount the teacher will receive depending on the number of exceeded targets; comparisons of class averages from this year and previous year to compare the two, and the percentage differences including the bonus amount depending on a positive percentage difference; charts showing the differences between the two years; and finally a table showing the school budget and the amount the school have left to spend after paying for bonuses and gift vouchers. The reason why I summarised each of these items is because it is easier for the teacher to look at and see how her students are doing without having to search through the results tables, and it also breaks it up for the user so they know exactly what is going on. I included IF functions, filters, subtraction and charts to display this data by working between different sheets. I have explained how to complete each of these functions and working between sheets in previous steps, so I just followed the steps stated above to include them into the worksheet. The reason I decided to add a filter into my spreadsheet, was so that the teacher was easily able to see which students needed help in particular subjects, as it allows the teacher to filter particular subjects and know which students need help in that area. To add a filter into your spreadsheet, you do the following:

Step1

Firstly high-light the data you want to filter. In my spreadsheet, I made a list containing all the areas needing improvement, by copying and pasting the “need to improve” column from each subject worksheet, I also copied and pasted student names, so that the teacher knew which student needed academic improvement.

Step 2

Once you have high-lighted the data you want to filter, click “data” from the top toolbar. Once the drop down menu has appeared, select “filter.” A side bar should appear. Giving you a list of options, select “Autofilter.”

Step 3

After you have selected “Autofilter,” an arrow icon should appear at the top of the data list you selected earlier in Step 1. This arrow allows you to select a specific item, which you can filter within the list; this will therefore allow the teacher to filter specific subject areas.

The only problem with my filter is that the user will have to keep going back to the top of the data list to undo the filter.

Step 24

Once I was happy with the data displayed and the formulas, I decided to make the spreadsheet user friendly by inserting hyperlinks, so the teacher can work through the spreadsheet easily and efficiently, without having to search for a particular worksheet. Additionally, the hyperlinks are included to make the spreadsheet look more professional. To add hyperlinks you do the following:

1)Choose a picture/icon you want to hyperlink. I decided to have a picture of a house, on each results page and on my annual report, to show that if you clicked on that particular icon it would take you back to the menu system. The reason I chose an icon of a house, is because that icon if often used to take you back to your starting point, and the user would recognise this, whereas if I had made a picture of a computer my hyperlink back to the menu, then it is not easily recognisable, and the user would possible just think it was a picture. Therefore, it was essential I made my hyperlinks relevant and recognisable for the target audience. I also added text on hyperlinks to reports, charts and subjects, as there was no relevant image which would make the user understand where the hyperlink took them, so I decided to use text to tell them instead. I did this by, right clicking and selecting “text,” and inserted my specific text, however you can only do this on certain icons.

1)Once I had chosen my preferred icon, I right clicked it, and selected “hyperlink” from the selection provided. This then displayed a box asking my where I wanted the hyperlink to take me, so I selected the area I wanted it to take me, and clicked “ok.” I did this process for each hyperlink I created.

Once I had created all my hyperlinks, I then checked to see if they all worked and that all the worksheets connected smoothly, as it is vital that every page can be accessed via a hyperlink and can also be traced back to the menu.

Step 25

After creating my hyperlinks and checking they were in the right position and worked efficiently, I decided to make my spreadsheet look more professional, by designing a front page/menu system, so that when the user opens up the spreadsheet, it looks user friendly and presentable, as if user opened up a spreadsheet which lacked in design and presentation, then they probably would not bother using it or decided not to buy it. I decided to colour the background black, so that it was eye catching, and looked professional, as if I had colours the cells bright pink, then it would not have been suitable for my target audience. To colour format the cells, I did the following:

High-lighted the cells I wanted to colour and clicked on the fill colour icon situated in the top right hand corner of the screen. I then chose the colour I wanted (black.)

To make the front page look presentable and to add colour, I decided to add pictures relevant to the spreadsheet, on to the menu system. To do this I collected numerous pictures associated with schools from Microsoft office online, and inserted them into the spreadsheet by clicking “file”, “insert” and “from file.” Once I had inserted them, I arranged them so that they formed a border at the top of the worksheet, to make it look professional and eye-catching. Although, the teacher will know what the name of the school is, I have included it to show that it does belong to that particular school, and if I was to sell this spreadsheet to a school, the school will be able to include their own school name. The reason why I have colours the school name white is to make it stand out, this is the reason why I have colours the hyperlinks also. However, the reason why I coloured the subject hyperlinks one colour and the annual report another, is to separate the two. However, originally I had coloured the tabs all one colour, but I later decided to colour code the different sections e.g. colour all worksheets to do with “English” one colour, all worksheets to do with maths another, all worksheets associated with science a different colours and finally a different colour for the “menu” and “annual report” worksheets. The reason for this is it allows the teacher to easily distinguish which pages are linked and which are not. To change the colour of the tabs, I right clicked each individual one, and selected “tab colour,” I then chose the preferred colour.

To receive feed back on my spreadsheet, to see how I could improve it, I decided to create a questionnaire for my peers to complete, so that I could improve certain areas of my presentation. Here is a copy of my questionnaire.

From doing this questionnaire, I decided to colour code the tabs, so you can easily distinguish each of the categories. I believe that questionnaires are a good way to evaluate you presentation and understand how you can improve it also.

In conclusion, I thought my spreadsheet was appropriate for my target audience. The only area, during the production of the spreadsheet, which I found difficult, was the VLookUp, as I did not realise that the first column of my table array started at 1, I thought that column A was always 1 even if it was not the first column in the table array. If I was to create an improved spreadsheet, I would use INDEX/MATCH functions instead of a VLOOKUP, as an index/match function is stronger than a VLooup and it allows you to search for more than one criteria, whereas a Vlookup does not, and the teacher may want to look up more than one student. INDEX/MATCH functions are used together as a tool to extract data from a table, so this would be more suitable than a VLOOKUP. Moreover, I am glad I later created a macro so that the teacher did not have to go back to the top of the filter on the annual report worksheet, every time she wanted to undo the filter; the macro allowed the teacher to easily undo the filter without consuming time. Furthermore, I am also pleased that I chose to use an Autofilter rather then an advanced filter, as with an Autofilter it easily allows you to change what you want to search for via a drop down menu, whereas on an advanced filter you would have to keep changing the criteria for the filter. So I believe the Autofilter is the best option considering the target audience, as it is easier to use and you need functions which are user friendly. Additionally if I was the improve the spreadsheet, I would make the subject worksheets look more presentable with colour as at the moment the background is plain white, so I would format the cells to a particular colour, although I would still make sure it still looked professional. Finally, I believe that the spreadsheet is user friendly and fit for purpose.

Spreadsheet Technical report. My task was to produce a grade book for primary school teachers; the aim of this spreadsheet was to allow teachers to input scores and record student progress.

This is a preview of the whole essay

Document Details

Related Essays

Flight Spreadsheet Report. The task we had to complete was to find prices o...

computerised spreadsheet for pupil attendance data

Produce a spreadsheet-based system that would allow a cost estimate for an...

Payments Spreadsheet task. - formulas and fomatting