Creating a Spreadsheet question

IMPORTANT: Spreadsheet questions are only compatible with legacy tests. If you want to deliver spreadsheet content in standard tests, consider using a File Attach item. For more information about delivery types, read About Surpass test delivery options.

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 and word processor rows.

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.

Open Additional Options to add source material, assistive media, or a table to your question stem.

TIP: For more information about the formatting toolbar, read Using the formatting toolbar.

4. Edit and populate your spreadsheet

NOTE: Candidates have access to all spreadsheet editing and formatting tools. If you want to prevent candidates editing the data grid, use a Table item.

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.

TIP: You can override your instance’s separator format for this item by selecting either Always Use Decimals as Separators or Always Use Commas as Separators in the Decimal Settings menu. To learn about site-level separator settings, read Setting the separator format.

Spreadsheet toolbar options

Expand the following sections to see definitions of each setting on the spreadsheet toolbar.

Setting	Description
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.

Setting	Description
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).

Icon	Setting	Description
	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.

Using formulae

The Spreadsheet item supports basic formulae.

TIP: If you need more complex formulae in your question, consider using a File Attach item.

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.

Using decimal settings

You can decide whether numbers have decimals or if they are automatically rounded up.

Enabling spreadsheet export

You can control whether candidates have the ability to export the spreadsheet and their responses when taking a practice packaged test.

Setting the maximum number of rows and columns

You can limit how many rows and columns candidates can add in their response.

5. Set the question’s marks

Set the item’s total marks in the Mark field.

IMPORTANT: Spreadsheet items must be human marked.

6. Select a workflow status

Set the item’s workflow status in the Status drop-down menu. Only items set to Live can be used in a test.

An item's workflow status being set to Live.

7. Preview your question

WARNING: Flash is no longer supported by web browsers. Use the Surpass Viewer to preview items in the legacy test driver. For more information, read Installing the Surpass Viewer.

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.

WARNING: Saving changes to live items included in scheduled tests might affect candidate delivery.

Select Close to leave the Edit screen. If you have unsaved changes, you are prompted to either save or discard them.