|
Size: 2564
Comment:
|
Size: 3096
Comment:
|
| Deletions are marked like this. | Additions are marked like this. |
| Line 3: | Line 3: |
| = Using Excel's vlookup function to copy grades into a Learn@UW spreadsheet = | = Using Excel's vlookup function to copy grades into a packaged grade roster from the faculty center = |
| Line 5: | Line 5: |
| The scenario is that you have a spreadsheet in a format specific to your course that includes student IDs, grades, and perhaps other information. One way to submit these grades is to export a grades spreadsheet from Learn@UW, copy the grades from your spreadsheet into the designated column in the Learn@UW spreadsheet, and import that spreadsheet into Learn@UW. | This tip for submitting grades applies to the following scenario: You have a spreadsheet that includes student IDs, grades, and perhaps other information. This spreadsheet might have originated from Learn@UW. It doesn't matter where it came from as long as it contains student IDs and grades. One way to submit these grades is to export the packaged grade roster from the faculty center, copy the grades from your grade spreadsheet into the designated column in the grade roster spreadsheet, and then submit that in the faculty center. |
| Line 7: | Line 7: |
| The procedure below shows how to use Excel's vlookup function to copy the grades from your spreadsheet into the Learn@UW spreadsheet. | The procedure below shows how to use Excel's vlookup function to automatically copy the grades from one sheet to the other. |
| Line 13: | Line 13: |
| == Example Learn@UW sheet == | == Example packaged grade roster sheet from the faculty center == |
| Line 17: | Line 17: |
| == Use vlookup to copy grades from the class grade sheet into the Learn@UW sheet == | Note that the two sheets do not need to be sorted in the same way. == Use vlookup to copy grades from the class grade sheet into the grade roster == The vlookup function is used in a new column (here column N) in the grade roster sheet to get the grade for the student from the other sheet. |
| Line 23: | Line 27: |
| * The name of the cell in the Learn@UW sheet containing the student ID to match. | 1. The name of the cell in the grade roster sheet containing the student ID to match. |
| Line 25: | Line 29: |
| * The range of columns in the class grade sheet that contains the student ID and the grades. It doesn't matter if additional columns are included in this range. The first column in this range must be the student ID column. One way to enter the range in this column is by navigating to the other sheet and dragging the mouse over the column headers. | 1. The range of columns in the class grade sheet that contains the student ID and the grades. It doesn't matter if additional columns are included in this range. The first column in this range must be the student ID column. One way to enter the range in this column is by navigating to the other sheet and dragging the mouse over the column headers. |
| Line 27: | Line 31: |
| * The index of the column within the selected range that contains the grade in the class grade sheet. The first column in the range is index 1, the second is index 2, and so on. | 1. The index of the column within the selected range that contains the grade in the class grade sheet. The first column in the range is index 1, the second is index 2, and so on. |
| Line 29: | Line 33: |
| * Whether to do partial matches. Enter FALSE to get exact matches. | 1. Whether to do partial matches. Enter FALSE to get exact matches. |
| Line 37: | Line 41: |
| One way to do this is to hold the mouse over the lower right corner of the cell. The mouse pointer will become a plus. Double-click. The cell should get copied to the whole column. | One way to do this is to hold the mouse over the lower right corner of the cell. The mouse pointer will become a plus. If you double-click, the cell should get copied to the whole column. |
| Line 41: | Line 45: |
| == Copy the '''values''' of the vlookup cells into the grades column == | == Copy the values of the vlookup cells into the grades column == |
| Line 43: | Line 47: |
| Copy the cells and then use "Paste Special" and choose to paste the values. | Copy the cells and then use "Paste Special" and choose to paste the values. We do not want to copy the formulas, just the values. |
| Line 55: | Line 59: |
| == Import the completed sheet into Learn@UW == | == Submit the completed sheet in the faculty center == |
| Line 57: | Line 61: |
| In rare cases, an error may reported about the roster not matching. In this case, it may be necessary to import section by section. | In rare cases, an error may be reported during import about the roster not matching. In this case, it may be necessary to import section by section. |
Using Excel's vlookup function to copy grades into a packaged grade roster from the faculty center
This tip for submitting grades applies to the following scenario: You have a spreadsheet that includes student IDs, grades, and perhaps other information. This spreadsheet might have originated from Learn@UW. It doesn't matter where it came from as long as it contains student IDs and grades. One way to submit these grades is to export the packaged grade roster from the faculty center, copy the grades from your grade spreadsheet into the designated column in the grade roster spreadsheet, and then submit that in the faculty center.
The procedure below shows how to use Excel's vlookup function to automatically copy the grades from one sheet to the other.
Example class grade sheet
Example packaged grade roster sheet from the faculty center
Note that the two sheets do not need to be sorted in the same way.
Use vlookup to copy grades from the class grade sheet into the grade roster
The vlookup function is used in a new column (here column N) in the grade roster sheet to get the grade for the student from the other sheet.
The arguments to vlookup are:
- The name of the cell in the grade roster sheet containing the student ID to match.
- The range of columns in the class grade sheet that contains the student ID and the grades. It doesn't matter if additional columns are included in this range. The first column in this range must be the student ID column. One way to enter the range in this column is by navigating to the other sheet and dragging the mouse over the column headers.
- The index of the column within the selected range that contains the grade in the class grade sheet. The first column in the range is index 1, the second is index 2, and so on.
- Whether to do partial matches. Enter FALSE to get exact matches.
After entering the vlookup formula, pressing Enter should show the correct grade in the cell.
Copy the vlookup cell to the rest in the column
One way to do this is to hold the mouse over the lower right corner of the cell. The mouse pointer will become a plus. If you double-click, the cell should get copied to the whole column.
Copy the values of the vlookup cells into the grades column
Copy the cells and then use "Paste Special" and choose to paste the values. We do not want to copy the formulas, just the values.
The result:
Clear the vlookup column, now that it is no longer needed
Submit the completed sheet in the faculty center
In rare cases, an error may be reported during import about the roster not matching. In this case, it may be necessary to import section by section.