The Spreadsheet question assesses spreadsheet skills with automatic grading. The person being assessed gets the spreadsheet you prepared, fills in the open cells with values and formulas, and the platform checks the answer against the criteria you set.
Formulas use the English function names, such as SUM and VLOOKUP. People answering in Portuguese or Spanish get a reference table with each function's name in their own language.
How to create the question
Go to Assessment > Library > Create > Question.
Select the Spreadsheet format and click Continue.
Fill in the Settings tab with the brief and the rules of the question.
In Question spreadsheet, click Import .xlsx and upload the starting spreadsheet.
In Answer cells, choose which cells the person can fill in.
In Grading criteria, define what will be checked.
Fill in the Solution and click Check.
Save the question.
Settings tab
This is where the question gets its brief and rules:
Default language: Portuguese, English or Spanish.
Title and Description: the brief the person reads. Say which tab and which cells hold each answer and what each one should calculate.
Duration: time available to answer.
Weight: how much the question counts in the assessment.
Score limits (%): floor and ceiling of the question's percentage.
Skills, Type and Role/category: classification used by the Library filters.
Guidelines for revision: Goal of the question and How to evaluate, used by the review team.
In the Presentation tab you can turn on the presentation video. The person then records a video walking through the spreadsheet after answering it.
Question spreadsheet
Click Import .xlsx and upload the starting spreadsheet. The platform brings in every tab, with values, formulas, formatting, column widths and merged cells. The file can have up to 5 MB, 10 tabs and 20,000 filled cells.
After the import, the card shows two tabs:
Base spreadsheet: the spreadsheet as the person will receive it. Everything you edit here is locked for them, except the answer cells.
Solution: where you fill in the answer cells the way the person would. You use it to make sure the criteria are right.
Importing again replaces the base spreadsheet and keeps the answer cells, the criteria and the solution.
Answer cells
Choose the cells the person can fill in. Pick the Tab and enter the Range in A1 notation, without the tab name, such as F2:F10. Use Add range to open more than one area.
Every other cell is locked. If the person tries to change one of them, the screen explains that the cell is part of the question's data.
Grading criteria
Each criterion checks one or more answer cells and has a Weight. The available types are:
Value: the cell's result must match the Expected value. For numbers, the Tolerance sets the accepted difference. In Other accepted answers you list alternatives, one per line.
Contains a formula: the cell must hold a formula, not a typed value.
Exact formula: the formula must match the Expected formula. Spaces and letter case are ignored.
Uses functions: the formula must use the Required functions, by their English names, such as
SUMIFS.Recalculates with other data: the platform swaps base spreadsheet cells for the values in the Test cases and checks whether the cell reaches the Expected result. This is the criterion that tells a formula apart from a copied number.
With Depends on, you link one criterion to another. If the criterion it depends on does not pass, this one leaves the score, so the person does not lose points twice for the same mistake.
Generate criteria from the solution creates a Value criterion for each cell filled in the solution, plus a Contains a formula one when the cell holds a formula. Then adjust the weights and add the other types. The Description for the reviewer shows in the results in place of the criterion name.
Solution check
Click Check to grade the solution with the current criteria, even before saving. The result shows the expected value, the actual value and the status of each criterion.
To publish, the solution must score 100%. A criterion that fails your own solution also fails anyone who solves it the same way, which is why the check comes before publishing.
How grading works
The score is the sum of the weights of the passed criteria divided by the sum of all weights.
A criterion that leaves the score because of Depends on is not counted.
Grading reads only the answer cells. The base spreadsheet data is always yours.
The score comes out on submission, with no manual review. Human review is still possible from the assessment results.
What the person sees
They open the question with the brief on one side and the spreadsheet on the other, with the answer cells open and everything else locked. Above the spreadsheet there is a button that shows the function names in Portuguese or Spanish. The answer saves automatically after every change, and the question only opens on a computer.
The video below shows the question from the side of the person answering.
In the assessment results, you see the Candidate's spreadsheet as it was submitted and the Criteria tab, with the expected value, the actual value and the status of each one. The presentation video shows up when it is turned on.
Tips for a good Spreadsheet question
Say in the brief which tab and which cells hold each answer.
Use realistic data, enough of it that the answer cannot be worked out in your head.
Pair a Value criterion with a Contains a formula or Recalculates with other data one so typed numbers do not pass.
Prefer Uses functions over Exact formula when there is more than one right way to the result.
Click Check every time you change a criterion.
Frequently asked questions
Can people use function names in Portuguese?
No. The spreadsheet only accepts the English names. To help, people answering in Portuguese or Spanish get a reference table with the translation of each function.
My file was rejected. What should I check?
The file must be an .xlsx with up to 5 MB, 10 tabs and 20,000 filled cells. The error message says which limit was exceeded.
Why can't I publish?
Publishing requires the base spreadsheet, at least one answer cell, at least one criterion and a solution that scores 100% in the check. The message says what is missing.
Is grading really automatic?
Yes. The score comes from the criteria, with no manual review. Human review is still possible from the assessment results, as with any other question.
The question shows a Beta badge. Can I use it?
Yes. The badge means the format is new and still evolving, so it may get improvements over time.
See also: How to answer a Spreadsheet question