If you have a large question bank outside of Inspera Assessment, which are in formats inconvenient to copy and paste into Inspera, or which can be converted at scale into a structured format, it may be more effective to have these questions imported via Excel.
For this purpose, Inspera provides a 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.
This feature requires activation. Please contact the Service Desk to activate it for your Inspera Assessment tenancy.
Supported question types
The following question types are supported to import to Inspera Assessment from Excel:
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.
- Create an Excel file or a Google Sheet.
-
Name the tab questions.
-
Rows: The first row needs to contain information that says something about the question type, marks, and the specific options for the selected question type.
-
Columns: Which columns you need in the Excel file depends on the question types you want to import.
Multiple Choice Multiple Response Essay True / False Text Entry Numeric Entry Document QuestionType x x x x x x x QuestionName x x x x x x x QuestionText x x x x x x x Randomize x x x OptionText x x x IsCorrect x x x CorrectAnswer x x CorrectAnswerUpper x IsCaseSensitive x IgnoreWhitespace x ExpandInputField x x RestrictCharacters x x ExpectedLength x x
Information on each column:
-
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 For administrative purposes, the question name is used for finding the question.
-
The question text is visible for candidates.
It 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.
- 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.
-
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.
The OptionText column(s) cannot be pure digits. Digits in combination with text works. You should also avoid the following special characters as they will block the import: “, < and >. -
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.
Tip: If using Randomize, you can always author the first option as the correct one without it affecting question security or integrity.
-
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. -
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.
-
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.
-
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.
-
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.
-
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.
-
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.
Make sure that this is greater or equal to the number of characters in the correct answer. If it is shorter, the candidate will not be able to answer correctly.
-
Once you have filled in all the questions, save the sheet as an .xlsx file. To import the file into Inspera Assessment, you may need to select "All files" in the file selector window to locate the .xlsx file. (This depends on your computer or browser settings.
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 in Excel
HTML
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.
LaTeX
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
If you want to change the scoring options this has to be edited in Inspera Assessment after import.
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.
Examples
We will provide you with some examples of how your excel file should look like.
At the bottom of this article you can find Excel files that you can use as a templates and to import questions to Inspera Assessment.
-
True / False, Multiple Response and Multiple Choice questions
This example illustrates the import structure for three different question types:
- 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:
-
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:
-
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. IgnoreWhitespace is applicable for the question type Text Entry only.
See the question type articles for more information:
Please note the Option: Maximum number of decimals is not currently supported in Excel to QTI conversion for Numeric Entry. After import, decimals can be manually adjusted within the user interface. -
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 in the section 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.
Information
- 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