1 / 48

USING EXCEL FOR KEEPING STUDENT RECORDS

USING EXCEL FOR KEEPING STUDENT RECORDS. WHAT THIS PRESENTATION COVERS. What Excel looks like Cells, rows and columns Renaming sheets Minimising the ribbon Copying and pasting content Sorting content Keeping records Formulas for adding, % and grades Other types of cell content.

brendan-morgan
Download Presentation

USING EXCEL FOR KEEPING STUDENT RECORDS

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. USING EXCEL FOR KEEPING STUDENT RECORDS

  2. WHAT THIS PRESENTATION COVERS • What Excel looks like • Cells, rows and columns • Renaming sheets • Minimising the ribbon • Copying and pasting content • Sorting content • Keeping records • Formulas for adding, % and grades • Other types of cell content

  3. WHAT EXCEL LOOKS LIKE

  4. ROWS – DENOTED BY A NUMBER This is Row 7

  5. COLUMNS - DENOTED BY A LETTER This is Column D

  6. INDIVIDUAL CELLS - DENOTED BY LETTER AND NUMBER, EG, J8 Cell Name Eg, this cell is called J8. Both the row and the column are highlighted at the sides

  7. SHEETS A new file will have three pages, called Sheet1, Sheet2, Sheet 3. You can rename them, move them add them and delete them.

  8. CHANGE THE NAME OF A SHEET Right-click on the sheet name and select Rename. Type in a new name.

  9. MINIMISE THE RIBBON The Ribbon If the “ribbon” with all the tools takes up too much space, right–click anywhere on it, then click on “Minimise the Ribbon” in the box that appears

  10. INSERT A NEW ROW ...then click Insert Select the row directly beneath where you want to insert a new row ...

  11. A NEW ROW WILL APPEAR ABOVE THE ONE YOU SELECTED

  12. INSERT A NEW COLUMN ...then click Insert Select the column directly to the right of where you want to insert a new column...

  13. A NEW COLUMN APPEARS TO THE LEFT

  14. ENTER CONTENT INTO A CELL ...or you can click on a cell then type (or edit) its content up here You can click on a cell and type content straight into it...

  15. COPY CONTENTS OF A CELL ...Secondly, click the little double page symbol here on the Home tab Or Control + C Or Right-click and choose copy The cells to be copied will become surrounded by a dotted line. First, click on the cell you want to copy, or drag-select multiple cells to copy them...

  16. PASTE CONTENT INTO NEW CELLS ...Secondly, click the clipboard symbol here on the Home tab Or Control + V Or Right-click and choose Paste First, click on the cell you want to paste into, or drag-select multiple cells to paste multiple times into them...

  17. AUTO-COMPLETE Type the first few letters and Excel will sometimes attempt to auto-complete the word, based on words you have already entered. To reject the suggestion, press Backspace.

  18. SORTING DATA Drag-select the content you want to re-order, eg by House. Only the data you actively select will be moved.

  19. Click on the “Data” tab... ...then Click “ Sort”

  20. IMPORTANT! If your columns have headings, (called headers) tick this box first... Secondly, drop this list down and select the header you want to sort by, eg, House... ...lastly click OK

  21. Note that the names are now in House order

  22. We could have sorted them into year-level order within the House as well. Let’s go back...

  23. Click “Sort” Control + Z to undo a move. List reversed to alphabetical order...

  24. Add an extra level of sorting here As well as House as the first level, select Class as the second level You can add as many levels of sorting as you wish .

  25. Note the students are now sorted into House and then Class. Q: What would we see if we had sorted by Class then by House?

  26. ANSWER Now the year 10s appear first in House order, then the Year 11s in House order

  27. PEDAGOGICAL ALERT!! • The following examples of record keeping are very simplistic! • They are intended for the purposes of demonstrating Excel only • They do not constitute Best Practice in assessment techniques and may not be VELS compliant! • These techniques should be adapted to your individual classroom situation

  28. KEEPING SIMPLE RECORDS Enter a name for the task

  29. SPACE SAVING WITH VERTICAL TEXT On the Alignment tab under Home, choose the angled “ab” for orientation. Select “Rotate Text Up”

  30. CHANGING COLUMN WIDTH You can drag the column narrower or use Format

  31. ENTER STUDENT RESULTS You could also sort students by their results

  32. TOTALLING NUMERICAL RESULTS Numerical results for some sample criteria for an essay have been entered for each student First student’s total will go here

  33. SELECT ALL CELLS INCLUDING TOTAL ... secondly, click the E-like symbol (Greek Sigma, standing for Sum) on the Editing section of the Home tab. The Total will appear in the free cell. First drag-select all cells including the empty TOTAL cell ...

  34. =SUM(G2:I2) is the formula that Excel creates for the total in J2. • This means you have added the contents of cells G2, H2 and I2. Note: • The EQUALS sign at the start • The brackets • The colon

  35. COPY THE FORMULA DOWN THE COLUMN ... Secondly, drop down the arrow box and choose Down First, drag down....

  36. THE TOTALS FILL DOWN THE COLUMN

  37. COMBINING RESULTS Here are results for three projects: an Essay, an Oral Report and a Model. The darker columns are the individual totals. We want an overall total. Overall total will go here

  38. =SUM(J2,N2,R2) is the formula for the sum total of cells J2, N2 and R2 OVERALL TOTAL Add these totals to get the overall total

  39. FILL DOWN THE COLUMN AS BEFORE

  40. The Overall Totals have filled down the column

  41. TURNING SCORES INTO % For a percentage, the formula in this example is =S2* 100/40. This means cell S2 times 100 divided by the total possible, which was 40 marks

  42. FILL DOWN AS BEFORE

  43. GRADES FORMULA MARKING SCHEME Greater than 35 A From 31 to 35 B From 26 to 30 C From 21 to 25 D Less than 21 E

  44. GRADES FORMULA IS COMPLEX! =IF(S2>35,"A",IF(S2>30,"B", IF(S2>25,"C",IF(S2>20,"D","E")))) MEANING IF(S2>20,"D" , “E“) This means if the score in cell S2 is greater than 20, the grade is D, otherwise it’s an E However... IF(S2>25, "C" ...if the score is also greater than 25, the grade goes up to a C However... IF(S2>30, "B" ...if the score is also greater than 30, the grade goes up to a B However... IF(S2>35, "A" ...if the score is also greater than 35, the grade goes up to an A

  45. ONCE YOU WORK IT OUT, FILL DOWN

  46. OTHER TYPES OF CONTENT

  47. NOW YOU CAN EXCEL AT EXCEL!

More Related