Understanding Excel/Google Sheets Test Cases in Testlify

Testlify offers Excel-based simulation questions that allow employers to test a candidate's ability to work with spreadsheets in real-world scenarios. To simplify evaluation, these questions use auto-scoring based on a set of predefined test cases. This article explains how these test cases work, what identifiers like S1C1  mean, and how scoring is calculated.

What Are Excel Simulation Test Cases?

When creating an Excel-based question in Testlify, you provide:

• An input sheet – The spreadsheet a candidate will work on.
• A model answer sheet – The correct version with expected outputs.
• A set of test cases – Specific checks the system will use to auto-score the candidate’s response.

Understanding Identifiers Like S1C1

Each test case is linked to a specific cell or sheet in the spreadsheet. The identifiers follow this format:

So, S1C1  means Sheet 1, Cell A1. Similarly:

• S2C3  = Sheet 2, Cell C3
• S1C10  = Sheet 1, Cell J1
• S1C23  = Sheet 1, Cell W1

These identifiers are used to map where the system should check for the correct value in the candidate’s response.

How to Set Up and Evaluate Test Cases in Excel/Google Sheets Questions in Testlify

Testlify’s Excel/Google Sheets-based questions allow you to create auto-evaluated simulations that check both the correctness and formatting of the candidate's input.

Step 1: Add a google sheet/ excel question

Step 2: Add the sheet link and then add the required test cases

When creating a Google Sheets question, you can add structured test cases to validate specific cell outputs.

Each test case includes:

• Cell Identifier: e.g., Sheet1!D2  
• Expected Value: which can include content and formatting rules using special tags (see below)

What Does a Test Case Check?

Each test case verifies:

1. Correct Value in the specified cell.
2. Correct Formatting, if applicable (e.g., formulas, data types, or visual styling).
3. Cell Location — whether the response is in the exact expected cell.

✅ If all three criteria match, the test case passes.

❌ If any of them fail (wrong value, wrong cell, or missing), the test case fails.

Example Test Case

Let’s say your model answer has a value 4 in Sheet 1, Cell G7.

You’ll define a test case for:

• Sheet: 1
• Cell: G7
• Expected Value: 4

If the candidate enters 4 in a different cell or sheet, this test case will not pass.

Step 3: Candidate Report — Auto-Evaluated Output

Once the candidate submits their work, you’ll see a detailed test case result as part of their report

🏷️ Format Tags & Use Case Dialog Examples

These tags allow for more granular formatting checks as part of your test case definitions.

What's included:

• Live view of the candidate's sheet
• Each test case's expected vs actual result
• Auto-scoring based on passed validations
• Time taken per question

This setup ensures clear, objective scoring and saves reviewers from manual spreadsheet checking — while also ensuring formatting consistency when it matters.

How Auto-Scoring Works

• Each test case is worth a certain number of points (usually 1 mark).
• The system compares the candidate's spreadsheet against each test case.
• The final score is the total number of test cases passed.

Example:

• 10 test cases = 10 total marks
• Candidate passes 7 = Score: 7/10

Common Reasons for Failed Test Cases

• Value entered in the wrong cell
• Correct result, but missing formula
• Correct output in the wrong sheet
• Incorrect data type or formatting (e.g., number stored as text)
• Candidate skipped the step

Tips for Employers

• Design test cases that clearly map to a specific cell
• Always test your model answer before publishing the assessment
• Keep naming consistent across sheets and cells to avoid mismatches

If you need further assistance setting up or troubleshooting your Excel-based questions, feel free to reach out to our support team.

🔍 Filter Tags & Use Case Dialog Examples

In addition to formatting tags, Testlify supports filter validation tags for Google Sheets/Excel questions. These tags let you check whether a candidate correctly applied AutoFilter on a sheet — including which values were filtered, and which rows ended up visible or hidden as a result.

Unlike cell-based test cases (Sheet1!D2), filter tags are written using the format SheetName![TAG] and don't reference a specific cell — they validate the state of the sheet's filter instead.

Example

Example Filter Test Case

Let's say your candidate's sheet (Sheet1) has a Status column (C) with values Active, Inactive, and Pending across rows 2–6. The candidate is asked to filter the sheet to show only "Active" records.

You'll define test cases for:

• Identifier: Sheet1![FILTER_APPLIED] → Expected Value: 1
• Identifier: Sheet1![FILTER_VALUES]C → Expected Value: Active
• Identifier: Sheet1![VISIBLE_COLUMN_UNIQUE]C → Expected Value: Active

If the candidate forgets to apply a filter, or filters to the wrong value, these test cases will fail — even if the underlying data itself is untouched.

💡 Tip: You don't need to add every filter tag for every scenario. At minimum, combining [FILTER_APPLIED] + [FILTER_VALUES] + [VISIBLE_COLUMN_UNIQUE] gives reliable, low-maintenance coverage.

Common Reasons for Failed Test Cases

• Filter applied to the wrong column or wrong value
• Filter not applied at all when required

Tips for Employers

• For filter-based questions, avoid spaces in multi-value expected outputs (e.g. use Active,Pending not Active, Pending) — the system trims spaces but exact formatting reduces ambiguity