How to Organize and Review VAS Data in Excel

Collecting Visual Analog Scale (VAS) responses is only the first step. Before statistical analysis, the data should be organized carefully and checked for missing responses, duplicate records, inconsistent identifiers, and other potential errors.

This article explains the basic principles of reviewing and organizing VAS data in Microsoft Excel or another spreadsheet application.

Preserve the Original Export

Before editing an exported file, save an untouched copy as the original source data.

For example, you may keep separate files such as:

• StudyA_export_original.xlsx
• StudyA_export_working.xlsx
• StudyA_analysis.xlsx

Editing the original file directly creates a risk of losing information through sorting, overwriting cells, entering formulas, or deleting rows.

All cleaning, restructuring, and calculations should therefore be performed in a copied working file.

Including the project name, export date, and file version in the filename can also make data management easier.

Understand the Basic Data Structure

VAS data are often easiest to manage in a long format, where each row represents one response from one trial.

A row may include information such as:

• Participant ID or session ID
• Date and time of measurement
• Condition or stimulus
• VAS item or question
• Trial number
• Repetition number
• VAS value
• Actual presentation position
• Assigned sequence pattern
• Response time
• Missing, skipped, or interrupted status

For example, if a participant evaluates pleasantness and intensity for three conditions, with two repetitions per condition, that participant will generate multiple rows of data.

This format is suitable for filtering, pivot tables, statistical software, and grouped analyses by participant, condition, item, or trial.

Check Participant IDs and Condition Labels

Start by confirming that participant IDs and session IDs are consistent.

For example, the following values may accidentally be treated as three different participants:

• P001
• P-001
• p001

The same issue can occur with condition names and VAS item names.

Where possible, use stable internal IDs rather than display names as the primary basis for matching records. Display names may be edited later, while internal IDs should remain unchanged.

Display names help people understand the data. Stable IDs preserve the relationship between records.

Confirm That VAS Values Are Within Range

Check whether each VAS value falls within the minimum and maximum values defined for that scale.

For a VAS ranging from 0 to 100, all valid responses would normally be between 0 and 100. However, some projects may use different ranges, such as:

• 0 to 10
• −100 to 100
• 0 to 1,000
• A custom continuous range

Do not assume that every VAS uses the same range. Review the minimum and maximum settings stored with each item or project.

When combining data from scales with different ranges, consider whether direct comparison is appropriate or whether normalization is required.

Distinguish Missing Responses From Zero

A value of zero may be a valid VAS response representing the left endpoint of the scale.

A missing response means that no valid response was recorded.

These should never be treated as equivalent.

• 0: The participant selected the minimum endpoint
• Blank or missing: No response was obtained
• Skipped: The item was intentionally not answered
• Interrupted: The session ended before completion

These states can have different meanings in analysis. Always review both the VAS value and the response-status field before calculating summary statistics.

Replacing missing responses with zero can introduce substantial bias.

Separate Presentation Order From Condition Identity

In studies using randomized or counterbalanced presentation orders, the order experienced by the participant may differ across sessions.

For example:

• Participant 1: A → B → C
• Participant 2: C → A → B
• Participant 3: B → C → A

When reviewing the progress of a session, the actual presentation order matters.

When comparing responses across conditions, however, it may be more convenient to organize the data consistently as A, B, and C.

For this reason, the following information should be stored separately:

• The position at which the condition was presented
• The identity of the condition
• The assigned sequence or pattern

Do not delete presentation-order information after reorganizing the data for analysis.

It may later be needed to examine order effects, fatigue, adaptation, learning, or carryover effects.

Use Filters to Review the Data

Excel filters make it possible to inspect specific participants, conditions, items, or trial types.

Useful checks include:

• Does one participant have fewer trials than expected?
• Does each condition have the expected number of responses?
• Are missing responses concentrated in one VAS item?
• Are any trials duplicated?
• Was a session interrupted and later restarted?
• Are any response times unusually short or unusually long?
• Are there unexpected condition or item names?

An unusually short response time does not automatically mean that a response is invalid.

Any exclusion rule should ideally be defined in the study protocol or analysis plan, and the reason for exclusion should be documented.

Use Pivot Tables for an Initial Overview

Pivot tables can provide a quick overview of the dataset.

For example, they can be used to calculate or display:

• Mean VAS values by condition
• Response counts by VAS item
• Missing-response counts by participant
• Mean values by condition and repetition
• Results grouped by presentation sequence
• Trial counts by session

However, summary values should not replace inspection of the source data.

The same mean value may be produced by very different distributions, missing-data patterns, or sample sizes.

A good workflow is to check response counts and missingness first, and then review means, standard deviations, and other descriptive statistics.

Converting Data to Wide Format

Some statistical analyses require a wide format, where each row represents one participant and each condition or item is stored in a separate column.

For example:

Participant ID	Pleasantness_A	Pleasantness_B	Pleasantness_C
P001	72	54	31
P002	68	61	29

This format can be convenient for comparisons between conditions.

However, it may be less suitable for representing repeated trials, presentation order, interruptions, and other trial-level information.

For this reason, keep the original long-format data and create the wide-format version in a separate worksheet or file.

When repeated measurements are available, decide in advance whether to:

• Analyze each repetition separately
• Average repetitions
• Use only a predefined trial
• Model repeated observations statistically

The choice should follow the study design and analysis plan.

Do Not Overwrite Recorded Values With Formulas

Excel formulas are useful for calculating means, transforming values, and reorganizing data.

However, formulas should never replace the original recorded values.

Add calculated columns to the right of the original dataset, or create a separate analysis worksheet.

Also note that formulas may not always be interpreted in the same way when the file is opened in another spreadsheet application or imported into statistical software.

Before importing calculated data, confirm that the formulas have been evaluated correctly and that missing values are handled as intended.

Watch for Automatic Format Conversion

Excel may automatically convert entered or imported values into dates or numbers.

This can affect identifiers such as:

• 1-2
• 3/4
• 00123
• Long numeric identifiers

Participant IDs and condition IDs are generally safer when treated as text.

Pay particular attention to identifiers with leading zeros, because Excel may remove them automatically.

Opening a CSV file by double-clicking it may trigger automatic type conversion. For important datasets, consider importing the file through Excel's data-import function and specifying the column types manually.

Document Every Data-Processing Step

Any correction, exclusion, conversion, or restructuring should be documented.

A processing log may include:

• Date of processing
• Name or role of the person performing the work
• Source filename
• Excluded participants, sessions, or trials
• Reasons for exclusion
• Missing-data handling
• Method used to combine repeated measurements
• VAS normalization or conversion
• Formulas or scripts used
• Version of the analysis file

Reproducible research requires more than a final spreadsheet.

It should be possible to trace how the analysis dataset was produced from the original exported data.

Working With Data Exported From rVAS

rVAS exports data in a structure that allows participants, sessions, conditions, VAS items, trials, and presentation sequences to be identified.

Before analysis, confirm at least the following:

1. The correct project was exported
2. All required sessions are included
3. Participant IDs are consistent
4. Conditions, VAS items, and repetition counts match the project settings
5. Missing, skipped, and interrupted responses are identified
6. VAS values fall within the configured range
7. Condition IDs match the actual presentation order
8. The app version and data-format version are recorded

In rVAS, each response remains linked to its condition even when presentation order is randomized.

This allows researchers to preserve the actual sequence experienced by each participant while also organizing the exported data in a consistent condition-based order for analysis.

Summary

The first step in working with VAS data is not immediately calculating an average.

Begin by confirming that:

• The original export has been preserved
• Participant, condition, and VAS item identifiers are consistent
• Missing responses are distinguished from valid zero values
• Actual presentation order is retained
• Missing, skipped, and interrupted trials are reviewed
• All processing steps are documented

These checks reduce the risk of transcription errors, incorrect grouping, and unintended data loss.

rVAS supports VAS design, administration, local data storage, and XLSX export in a single app. It is designed to reduce the manual work required for paper-based VAS studies, including ruler measurement and transcription into spreadsheets.