Inspera Assessment (IA) provides user-friendly interfaces to create questions directly in the platform. However, if you already have a large question bank outside of IA, which are in formats inconvenient to copy and paste into IA, or which can be converted at scale into a structured format, it may be easier and more effective to have these questions imported via Excel.
For this purpose, Inspera provides a free service that will create questions in the IMS QTI 2.1 format based on an excel file, which can then be imported directly in Inspera Assessment.
The following question types are supported to import to Inspera Assessment from Excel:
Content
How to create questions in Excel
You can use Google Sheets as well as Excel. When you have created your Google sheet, save it as a .xlsx file to your computer, and you may import that file the same way as if you were to use Excel.
1. Create an Excel file or a Google Sheet.
2. Name the tab "questions".
3. Start with the first row in your sheet. The first row needs to contain information that says something about the question type, marks, and the specific options for the selected question type.
Column headers
Which columns you need depends on the question types you want to import:
1) QuestionType:
Use the following values in the table to specify your question type.
| Value | Question type |
| multiple_choice | Multiple Choice |
| mulitple_response | Multiple Response |
| essay | Essay |
| true_false | True & False |
| text_entry | Text Entry |
| numeric_entry | Numeric Entry |
| document | Document |
2) QuestionName:
- For administrative purposes, the question name is used for finding the question.
3) QuestionText:
- The question text is visible for candidates. Can contain HTML and even LaTeX if you are advanced, see Formatting and LaTeX. (While you could potentially add images as base64-encoded versions, the recommended approach for illustrations and other attachments is to use the Inspera Assessment authoring UI.)
For the question types Text Entry and Numeric Entry, you must add the value _entry_ to the question text where you want the interaction element to be. 
See also Example 3: Text Entry and Numeric Entry questions.
4) Randomize:
- This column is for the question types Multiple Choice, Multiple Response, and True/False only. Use a checkmark "x", "X" or the value 1 to indicate randomization is wanted for this question. If you do not want your options/alternatives to be randomized, leave this field blank.
- If a question has randomization enabled, the sequence of the options/alternatives will be different for each candidate that receives the question on a test.
- This column is disregarded for question types that do not offer randomization of options, such as essays.
5) OptionText:
- This column is required for the question types Multiple Choice, Multiple Response, and True/false
- OptionText can be used for the alternative in the question types Multiple Choice and Multiple Response.
- Can contain HTML and even LaTeX if you are advanced, see Formatting and LaTeX. You can enter up to 8 options/alternatives for the question types Multiple Choice and Multiple Response, each option with its own column. The question type True / False can only have two OptionText columns. See example below.
6) IsCorrect:
- This column is required for the question types Multiple Choice, Multiple Response, and True / False only.
- Use a checkmark "x", "X" or the value 1 to indicate this is the correct answer. Leave empty or use the value 0 to indicate wrong answer. Applies to the OptionText in the cell to the left.
7) CorrectAnswer:
- This column is required for the question types Text Entry and Numeric Entry.
The value, text or, number, for the correct answer(s):
| Text Entry | Numeric Entry |
|
The text for the correct answer. It is possible to add multiple correct answers by separating the correct answers with a semicolon. Example: If the correct answer is Anne, but you will also accept misspellings of names or multiple correct answers:
CorrectAnswer value: Anne; Ane |
The number for the lowest correct answer.  |
8) CorrectAnswerUpper
- This column is required for the question type Numeric Entry.
Set the number for the highest correct answer.
If this is the same as the lowest correct answer (CorrectAnswer), you can up the same value here.
9) IsCaseSensitive
- This column is required for the question type Text Entry.
- By enabling this, the automatic scoring mechanism will only mark the answer as correct if it matches the solution perfectly. If the answer is "task", then "Task" will not be considered correct. Use a checkmark "x", "X" or the value 1 to enable.
10) IgnoreWhitespace
- This column is required for the question type Text Entry.
If enabled, this option will remove the whitespace characters (" ") from the candidates' answer before comparing it with the correct answer. Use a checkmark "x", "X" or the value 1 to enable.
11) ExpandInputField
This column is required for the question type Text Entry and Numeric Entry.
Enables automatically expansion of the specified text box when the candidate types more than the expected length of the text box. Use a checkmark "x", "X" or the value 1 to enable.
12) RestrictCharacters
This column is required for the question type Text Entry and Numeric Entry.
This sets the maximum number of characters the candidate can type to be the same length as 13) ExpectedLength . A warning will appear if the length is shorter than the length of the correct answer. The question cannot be saved unless this is correct. Use a checkmark "x", "X" or the value 1 to enable.
13) ExpectedLength
This column is required for the question type Text Entry and Numeric Entry.
Select the length of the selected interaction element. The length is chosen by entering the number of expected characters to be answered by the candidate. Predefined as 20 characters for Text Entry and 3 for Number Entry (if the value is empty or set as 0). Add a value "number" to the specify the width with another number of characters than predefined.
4. Once you have filled in the sheet with all the questions, save it as an .xlsx file. Then you may import them to Inspera Assessment. When you are importing the .xlsx file to Inspera Assessment you may have to choose "All files" to find the .xlsx file. This depends on what kind of computer or browser you use.
Read Import questions and questions sets (QTI) to get more information on how to import. Behind the scenes, the .xlsx file is converted to a zip-file in the IMS QTI 2.1 format, which is then imported into Inspera Assessment.
Formatting and LaTeX
Formatting can be done by including basic HTML formatting tags like <b> in the Excel cell content.
- Bold formatted: <b>text</b>
- Italic formatted: <i>text</i>
- Underlined text: <u>text</u>
You can also leave the text plain in the import and format after importing in the editor in Inspera Assessment.
If you want to include LaTeX you write the mathematical expression inside of a parenthesis like such:
\( expression \)
Formatting tags that are supported:
[“b”, “strong”, “i”, “em”, “mark”, “small”, “del”, “ins”, “sub”, “sup”, “ul”, “ol”, “li”]
LaTeX examples:
- \( \sqrt{4x^2} \) will result in
- \((2x\sin (xyz)+x^2yz\cos (xyz))\mathbf {i}+x^3z\cos (xyz)\mathbf {j}+x^3y\cos (xyz)\mathbf {k}\) will result in
Marks
By default the different question types have the following mark settings:
- Multiple choice: 1 mark for correct and 0 marks for wrong answer.
- Multiple response: 1 mark for correct and 0 marks for wrong answer.
- True / False: 1 mark for correct and 0 marks for wrong answer.
- Text Entry: 1 mark for correct and 0 marks for wrong answer.
- Numeric Entry: 1 mark for correct and 0 marks for wrong answer.
- Essay: maximum 10 marks.
- Document: Non marked question type hence non marks to set.
If you want to change the scoring options this has to be edited in Inspera Assessment after import.
Examples
We will provide you with some examples of how your excel file should look like.
Example 1: True / False, Multiple Response and Multiple Choice questions
This is an import example for three different types of questions:
- True / False - Two alternatives, where one of these is the correct answer (marked with value 1 in the cell to right of the correct option). The wrong answer is marked so by leaving the cell to the right of the option empty.
- Multiple Response - Three alternatives, where two of these are correct answers (marked with value 1 in the cell to the right of the correct option). The wrong answers are marked with value 0 in cell to the right of the option.
- Multiple Choice - Three alternatives, where 1 of these is the correct answer (marked with value 1 in the cell to the right of the correct option). The wrong answers are marked so by leaving the cell to the right of the option empty.
Your Excel file should have the following rows and columns:
Example 2: Document and Essay Questions
This is an example of an import of one document and two essay questions where only the first three columns (QuestionType, QuestionName and QuestionText) are needed, as the other columns will be ignored.
Your Excel file should have the following rows and columns:
Example 3: Text Entry and Numeric Entry questions
This is an example of an import of one Text Entry and one Numeric Entry question. As Text Entry and Numeric Entry do not have alternatives, the columns OptionText and IsCorrect are not needed.
IsCaseSensitive, IgnoreWhitespace, ExpandInputField, RestrictCharacters and ExpectedLength are the specific options for the question types. See the question type articles for more information:
IgnoreWhitespace is applicable for the question type Text Entry only.
Example 4: All supported question types
If you are going to import all of the supported question types in the same file, you need to add all the columns as described under How to create questions in Excel. You can also download the example 4 template at the bottom of this article to help you get started.
- The tab has to be named "questions" (the other tabs which are not named "questions" are ignored).
- You can name the xlsx file to whatever you want. When imported to Inspera Assessment, the generated question set will get the name "Test from Excel" regardless of the name of the file.
- Remember to check and set the correct number of marks on each question.
- If you copy and paste from another source (also another Excel sheet), excess cell formatting may occur which can stop the conversion. If you get the message that no questions or question sets have been imported, this may be the case. To solve this, you can try to clean excess cell formatting in the Excel sheet that you are going to import. Please see Microsoft's guide on how to Clean excess cell formatting on a worksheet.
Downloadable templates
Template Example 1 - TrueFalse, Multiple Response, and Multiple Choice
Template Example 2 - Document and Essay questions
Template Example 3 - Text Entry and Numeric Entry questions
Template Example 4 - All supported question types
Comments
0 commentsArticle is closed for comments.