How do I export a spreadsheet with assessment scores and student responses?

Tests & Quizzes offers two export options to download student responses, scores, and other details about an assessment as an Excel spreadsheet:

  • Classic Export: A basic text-only spreadsheet that contains the students' total scores for an assessment, their answers to questions, and comments you entered for the students while grading.
  • Formatted Export: A spreadsheet with multiple tabs that contain detailed information about the assessment and submissions. You can deselect various options to include less information and make the spreadsheet easier for you to read or analyze with your own software.

For information on entering assessment grades and comments, see How do I grade Tests & Quizzes?

Go to Tests & Quizzes.

Select Tests & Quizzes from the tool menu in your site.

Select the Published Copies tab.

Select the Published Copies tab to view the assessments that have been released to students in your site.

Go to the assessment submissions.

  1. Select the Actions menu for the assessment.
  2. Select Scores.
  3. Alternatively, you may select the number of student submissions in the Submitted column to view the submissions.

Select the Export tab.

Select your export options.

Classic Export

To download a basic text-only spreadsheet, select the Export button for Classic Export.

Depending on your browser settings, you may be asked to save or open the file.  Once downloaded, you can open the file in a spreadsheet program, such as Excel. Skip to the step describing the classic export spreadsheet.

Formatted Export

Screenshot shows Formatted Export Options (described below). Graphic link opens modal with larger image. Press Escape to exit modal.

By default, the Formatted Export spreadsheet includes four tabs (Overview, Student Responses, Question Details, and Item Analysis) with eight Export Options that you may uncheck to exclude unneeded information from the export:

  1. Info Block: A blue box that appears at the top of each of the spreadsheet's four tabs, displaying the assessment's title, ID, creator, creation time, UVACollab site from which it is being exported, and total point value.
  2. Separate Parts: If the assessment has multiple parts, uncheck this option to combine the tables for each part found in the Student Responses tab of the spreadsheet into a single table. Note: It is recommended that screen reader users uncheck this option to access the preview.
  3. Show Comments: Uncheck this option to remove the Assessment Comment column from the Overall Results table in the Overview tab, and the Comments columns from the tables in the Student Responses tab.
  4. Question Correlation: A random ID number assigned to each question. When the same question has been used in multiple assessments, you can use its correlation number to identify it and compare student results across assessments and UVACollab sites.  The Question Correlation option is only available when the Question Details tab is included in the export.
  5. Overview: The first tab in the spreadsheet, which displays a table of Overall Results with the following details:
    • Each student's last name, first name, and user name (UVA computing ID or non-UVA email address).
    • Submission Order - If a student made more than one submission or completed a retake, multiple submissions for the same student are listed with the order in which they were completed.
    • Each student's total score.
    • When each student started the assessment (Start Time), when the assessment was submitted (End Time), and how much time the student spent on the assessment (Time Taken).
    • Correct Autoscored Responses - How many automatically-graded questions each student answered correctly. True false, multiple choice, matching, fill in the blank, numeric response, and calculated questions are automatically graded.
    • Assessment Comment - Any comments you entered for students on the Total Scores screen.
  6. Student Responses: The second tab in the spreadsheet, which displays the students' answers to the questions. By default, if the assessment has multiple parts, each part of the assessment has its own table of results. The tables include the following information:
    • Each student's last name, first name, and user name (UVA computing ID or non-UVA email address).
    • Each student's part score.
    • The students' responses to each question.
    • Correctness - For automatically-graded questions, whether the answer was correct or incorrect.
    • The student's score for each question.
    • Comments you entered for students while grading individual questions.
  7. Question Details: The third tab in the spreadsheet, which displays the question type, instructions and answer options for each question, as well as a Question Correlation (export option 8 described below).
  8. Item Analysis: The fourth tab in the spreadsheet, which displays the same information as the Item Analysis tab found in the Scores screens. For details, see How do I view statistics about a completed assessment?

Note: When Anonymous Grading is enabled in the assessment settings:

  • A random submission ID number will display instead of the student's name and ID for each submission
  • The Submission Order column will not be included in the Overall Results table.

Preview formatted export. (Optional)

Screenshot shows example Formatted Export Preview (described below). Graphic link opens modal with larger image. Press Escape to exit modal.

The Export Preview is a display of the formatted export spreadsheet embedded directly on the page.  As you uncheck and check the boxes for the Formatted Export Options, the Export Preview will update automatically to reflect the changes.

To navigate the preview with a keyboard:

  1. Use the Tab key to enter the preview.  If this is your first time accessing the preview, you will be placed on the first spreadsheet tab that you have included in your export options.  If all Export Options are enabled, you will land on the Overview tab.
  2. While a spreadsheet tab is selected, you may use the left and right arrow keys to switch from one tab to another. The available tabs (Overview, Student Responses, Question Details, and/or Item Analysis) are determined by the Export Options you have enabled.
  3. From your selected spreadsheet tab, use the Tab key on your keyboard to navigate to the first table in the spreadsheet. You may use the Tab key and Shift + Tab to navigate from one table cell to another, and the arrow keys to scroll within the preview.
  4. To exit the preview, either Shift + Tab back to your active spreadsheet tab or Tab forward through the end of the spreadsheet.

Skip to the step describing the formatted export spreadsheet.

View spreadsheet.

Classic Export spreadsheet

The Classic Export spreadsheet includes the following columns:

  1. Last Name, First Name, and User Name (UVA computing ID or non-UVA email address) for each student.
  2. Order of Submission: If a student made more than one submission or completed a retake, multiple submissions for the same student are listed with the order in which they were completed.
  3. Final Score: A student's total score for each submission.
  4. Grader's comments: Any comments entered for students on the Total Scores screen.
  5. A numbered Part, Question, Response (e.g., Part 1, Question 1, Response): A student's answer to each question.
  6. A numbered Part, Question, Grader's comments (e.g., Part 1, Question 1, Grader's comments): Any comments entered for students while grading individual questions.

Note: When Anonymous Grading is enabled in the assessment settings:

  • A random Submission ID number will display instead of the student's name and user name for each submission.
  • The Order of submission column will not be included in the table.

Formatted Export spreadsheet

By default, the Formatted Export spreadsheet includes the data described below.

Info Block

Screenshot of info block described below.

The Info Block is a box with a light blue background that appears at the top left of each tab in the spreadsheet.  It includes the following information:

  • Title: The assessment's title.
  • Id: The published assessment ID number.
  • Creator: The person who authored the assessment.
  • Creation Time: The date and time when the assessment was published.
  • Site: The title of the UVACollab site where the assessment is located.
  • Total Points: The assessment's total point value.

Overview

Screenshot shows example Overall Results table (described below). Graphic link opens modal with larger image. Press Escape to exit modal.

The Overview tab includes an Overall Results table with the following columns:

  1. Last Name, First Name, and User Name (UVA computing ID or non-UVA email address) for each student.
  2. Submission Order: If a student made more than one submission or completed a retake, multiple submissions for the same student are listed with the order in which they were completed.
  3. Total Score for each submission.
  4. Start Time: When each student started the assessment.
  5. End Time: When the assessment was submitted.
  6. Time Taken: How much time the student spent on the assessment.
  7. Correct Autoscored Responses: How many automatically-graded questions each student answered correctly. True false, multiple choice, matching, fill in the blank, numeric response, and calculated questions are automatically graded.
  8. Assessment Comment: Any comments entered for students on the Total Scores screen.

Note: When Anonymous Grading is enabled in the assessment settings:

  • A random Submission Id number will display instead of the student's name and user name for each submission.
  • The Submission Order column will not be included in the table.

Student Responses

Screenshot shows example Student Responses table (described below). Graphic link opens modal with larger image. Press Escape to exit modal.

By default, the Student Responses tab includes a separate table for the students' responses to questions in each part of the assessment (see the section on export options above to combine the tables into one).  Each Student Responses table includes the following groups of columns:

  1. Overall results for the assessment part (titled with the part number and Student Responses, e.g., Part 1 Student Responses):
    • Last Name, First Name, and User Name (UVA computing ID or non-UVA email address) for each student.
    • Submission Order: If a student made more than one submission or completed a retake, multiple submissions for the same student are listed with the order in which they were completed.
    • Part Score: For each submission, the student's total score for questions within the part.
  2. Results for each question (titled with the part and question number, e.g., Part 1, Question 1):
    • Response: The student's response. For questions with multiple answer options, e.g., multiple choice, multiple selection and fill in the blank questions, answer options are indicated as follows:
      • Multiple choice, matching, and survey - matrix of choices: A selection number and the response, e.g., Selection 1: A - Georgia O'Keeffe.
      • Fill in the blank and numeric response with multiple blanks: A blank number and the response, e.g., Blank 1: 6.
      • Calculated question: The variable and corresponding value, e.g., z - 5.
    • Correctness: For automatically-graded questions, whether each response was correct or incorrect. True false, multiple choice, matching, fill in the blank, numeric response, and calculated questions are automatically graded.
    • Score: The student's score for the question.
    • Comments: Any comments entered for the student while grading the question.

Question Details

Screenshot shows example Question Details table (described below). Graphic link opens modal with larger image. Press Escape to exit modal.

The Question Details tab includes a separate table for the questions and answer options in each part of the assessment. By default, Question Details tables include the following information for each question:

  1. Question Correlation: A random ID number assigned to each question. When the same question has been used in multiple assessments, you can use its correlation number to identify it and compare student results across assessments and UVACollab sites. If the correlation number is not needed, you may remove it from the export. See the section on export options above.
  2. Question Type: The type of question, e.g., True/False, Multiple Choice, or Multiple Choice (Multiple Selection).
  3. Question Instruction: The question text.
  4. Answer Details: For questions that have answer options specified, e.g., true/false, multiple choice, and fill in the blank, columns identifying the answer options display underneath the question text. The columns shown depend on the type of question, and may include:
    • Option: The text of each answer option, e.g., true or false.
    • Label: Examples of labels include A, B, C for multiple choice or matching questions and letters of variables for calculated questions.
    • Answer: The text of each correct answer to a fill in the blank question.
    • Value: The numeric values of each correct answer to a numeric response or calculated question.
    • Model answer: Text entered as a Model answer for a short answer/essay question.
    • Selection: The text of each column choice for a survey - matrix of choices question.

Item Analysis

Screenshot shows example Item Analysis table. Graphic link opens modal with larger image. Press Escape to exit modal.

The Item Analysis tab presents the table found on the Item Analysis page you may access while viewing the assessment's scores in Tests & Quizzes. For a description of the data in this table, see How do I view statistics about a completed assessment?