A Spreadsheet question lets candidates complete a spreadsheet in response to a prompt. Candidates have access to formatting tools and have the ability to create simple formulae.
This article explains how to create a basic Spreadsheet question and includes information on spreadsheet toolbar options and how to use formulae, word processor rows, and the diagram creator.
In This Article
1. Choose your item type
In your subject, select Create New Item to open the Question Types menu. Choose Spreadsheet to create a blank Spreadsheet item and go to the Edit screen.
2. Name your item
Enter the item’s name in the Name field.
3. Enter your question text
Enter your question text in the question stem. Select Add Text Area to add an additional question stem. You can have a maximum of five question stems per item.
4. Edit and populate your spreadsheet
Spreadsheet items contain a configurable 10 x 12 data grid that accepts alphabetical and numerical content as well as formulae. You can pre-populate the spreadsheet or leave it blank for candidates to create their own.
Select a cell to enter content. Text is automatically aligned to the left of the cell. Numbers are automatically aligned to the right of the cell.
Spreadsheet toolbar options
Expand the following sections to see definitions of each setting on the spreadsheet toolbar.
|Add Row Below||Inserts a new row beneath the selected cell.|
|Add Column Right||Inserts a new column to the right of the selected cell.|
|Delete Row||Deletes the selected row and its content.|
|Delete Column||Deletes the selected column and its content.|
|Row Height||Sets the height of the selected row in pixels. You can apply this value to all rows.|
|Column Width||Sets the width of the selected column in pixels. You can apply this value to all columns.|
|Merge Cells||Combines the selected cells into one cell.|
|Split Cells||Divides merged cells.|
|Decrease Indent||Moves text in selected cells one pixel to the left.|
|Increase Indent||Moves text in selected cells one pixel to the right.|
|Decrease decimals displayed||Removes one decimal place from cell numbers. Spreadsheets default to two decimal places.|
|Increase decimals displayed||Adds one decimal place to cell numbers. Spreadsheets default to two decimal places.|
|Border Colour||Edit the colour of cell borders (if set).|
Choose from the following fonts:
- Comic Sans MS
- MS Gothic
- Times New Roman
- Trebuchet MS
Select Add Symbol to add a special character to your spreadsheet. For a list of available characters, read About special characters.
Superscript On enables the Superscript option on the formatting toolbar. Highlight content in a cell and select Superscript to set the content above the line of type.
Superscript Off disables the Superscript option on the formatting toolbar and reverts any superscript content to normal.
|Undo||Reverses the previous action.|
|Cut||Removes selected content and adds it to the clipboard.|
|Copy||Duplicates the content to the clipboard.|
|Paste||Inserts clipboard data at the the current position.|
|Text Size||Choose the size of text.|
|Bold||Makes text darker.|
|Italic||Makes text slant to the right.|
|Underline||Adds a horizontal line below the text.|
|Superscript||Sets selected content above the line of type.
NOTE: To enable Superscript, go to Use Superscript > Superscript On.
|Text Colour||Choose the colour of your text.|
|Cell Fill Colour||Choose the colour of your cells.|
|Cell Borders||Choose the border for selected cells.|
|Text Margins (Left)||Moves selected cells’ left margin one pixel to the left.|
|Text Margins (Right)||Moves selected cells’ left margin one pixel to the right.|
|Decrease Decimals Displayed||Removes a decimal number from selected cells.|
|Increase Decimals Displayed||Inserts a decimal number to selected cells.|
|Text Alignment||Sets the alignment of text in selected cells.|
|Autosum||Calculates the sum of selected cells.|
|Export||Downloads a copy of the spreadsheet.
NOTE: Export is only available in packaged web delivery tests and can be enabled by selecting Show Export button.
The Spreadsheet item supports basic formulae.
Currently, the Surpass Spreadsheet item supports SUM formulae that returns the sum of numbers in selected cells along with basic mathematical functions (=A2*A4-A4). You can also use parentheses and subformulas (=(A2*(A2-A4)-A1)).
In a formula you may reference a single cell (A1), multiple cells (A1+A3), and a consecutive range of cells (A1:A10).
Use the following syntax to create a SUM formula:
=SUM([range of cells], [range of cells],...)
Formulae can reference cells that contain other formulae.
Adding word processor rows
Word processor rows let you add richly-formatted text to a spreadsheet.
Select any cell in column A and select the Word Processor button to turn the row into a single text entry cell.
Text entered in a word processor row automatically wraps and new lines (Enter) increase the row height.
Using decimal settings
You can decide whether numbers have decimals or if they are automatically rounded up.
The Rounding of numbers when added by a candidate setting determines how decimals work in your Spreadsheet item. Select 2 decimal places to enable decimal numbers. Select Rounded to display only whole integers.
Enabling spreadsheet export
You can control whether candidates have the ability to export the spreadsheet and their responses when taking a practice packaged test.
Select Show Export button to allow candidates to download the spreadsheet in a packaged web test.
Adding the diagram creator
You can choose whether you want to allow candidates to create a diagram as part of their response.
If you want candidates to provide diagrams along with their written response, select Allow candidates to add diagrams to their answers. This adds the diagram creator to the item in delivery. The diagram creator’s default label is “Diagrams”, but you can change this in the Button label field. For more information, read Using the diagram creator.
Setting the maximum number of rows and columns
You can limit how many rows and columns candidates can add in their response.
The Set maximum number of rows and columns setting determines how many rows and columns candidates can include in their response. You can choose from either 100 rows by 26 columns or 200 rows by 13 columns.
5. Set the question’s marks
Set the item’s total marks in the Mark field.
6. Select a workflow status
7. Preview your question
To see your item from a candidate’s perspective, select Preview .
If you need to continue working on the item, select Edit to return to the Edit screen.
8. Saving and closing your item
Select Save to save your item to your subject.
To see additional saving options, use the Save arrow to open the Save Options menu. Select Save & Close to save the item and return to the Subjects screen. Select Save & New to save the item and create another Spreadsheet item.
Select Close to leave the Edit screen. If you have unsaved changes, you are prompted to either save or discard them.
You can do much more with Spreadsheet items in Surpass. For more information, read the following articles: