> ## Documentation Index
> Fetch the complete documentation index at: https://docs.coloop.ai/llms.txt
> Use this file to discover all available pages before exploring further.

# Excel Formatting for Open Ends

> This guide explains how to map your data before importing into CoLoop’s Open Ends tool. Find the best ways to format different types of open ended response data (e.g. survey, conversation, and chatbot data) as well as see downloadable examples for each data type. Below, you’ll learn best practice tips and common pitfalls to avoid.

### Why Mapping Matters

CoLoop’s Open End Analysis tool is designed to handle diverse data from various sources efficiently regardless of formatting. You are now able to simply select your corresponding columns to the corresponding fields in the dropdown menu. See more on auto-mapping [here](/docs/open-ends/open-ends-project-set-up). This works particularly well for survey data.

For more complex data e.g. chatbot data, please adhere to the standardized format outlined below to ensure your data is processed accurately. This approach allows us to provide a consistent, high-quality analysis experience for all users.

### **File Requirements**

* File format: .xlsx (Excel)
* Maximum responses: 10,000

Example: *10 respondents × 10 questions = 100 responses* (only the response cells count).

Files exceeding 10,000 responses will trigger an error. Contact support if your use case exceeds this limit.

## Data Types

### **Question Data (Survey-Style)**

Use this when you have **survey questions and answers**.

**Required columns:**

* **“Respondent ID”:** Links each response to a participant.
* **“Question Columns”:** Each column header is a question; each cell contains an open-ended response.

**Optional columns:**

* **\[Tag] / \[Tag] TagName:** Labels applied to responses for that participant(e.g., “Satisfaction”, “Round 1”).
* **\[Segment] / \[Segment] SegmentName:** Additional demographic or group info for filtering.

While this is optional, having your data formatted like this before importing allows CoLoop to detect these automatically.

*Example:*

| Respondent ID | What improvements would you suggest? | \[Tag] Satisfaction | \[Tag] Survey Round | \[Segment] Customer Type |
| :------------ | :----------------------------------- | :------------------ | :------------------ | :----------------------- |
| R001          | Add more integrations                | Very satisfied      | Round 1             | New                      |
| R002          | Improve Wi-Fi connectivity           | Somewhat satisfied  | Round 1             | Returning                |

### **Conversation Data (Chatbots & Transcripts)**

Use this for **chatbot interactions or interview transcripts with multiple turns**.

**Required columns:**

* **“Respondent ID”:** Matching the ID from the Respondent Data sheet
* **“Question”/”Answer” Columns:** During import, select the respective “Question” and “Answer” columns via the dropdown menu.

**Optional columns:**

* **“Turn Number”:** This represents the order of conversation turns. If a respondent appears on multiple rows, CoLoop assumes their turns are ordered from top to bottom on your sheet Sequential conversation turn (start at 1).
* **\[Tag]:** Apply labels to each turn (e.g., “Positive”).
* **\[Segment]:** Add segmentation (e.g., “Device”).

*Example:*

| Respondent ID | Turn Number | Question                 | Answer           | \[Tag] Sentiment | \[Segment] Device |
| :------------ | :---------- | :----------------------- | :--------------- | :--------------- | :---------------- |
| R001          | 1           | How was your experience? | It was great!    | Positive         | Smartphone        |
| R001          | 2           | What did you like most?  | The quick setup. | Positive         | Smartphone        |

### **Respondent Data (Demographics & Segments)**

You can also upload demographic information about participants.

**Required column:**

* **“Respondent ID”:** Unique ID for each participant.

**Optional columns:**

* **“Respondent Name”:** Participant’s name.
* **\[Segment]:** Demographics or grouping variables.

*Example:*

| Respondent ID | Respondent Name | \[Segment] Age | \[Segment] Gender |
| :------------ | :-------------- | :------------- | :---------------- |
| R001          | John Doe        | 25–34          | Male              |
| R002          | Jane Smith      | 35–44          | Female            |

## Excel Structure Overview

There are **two main ways** to format your excel files depending on how your study is structured.

**Option 1: Combine Respondent and Response Data in One Sheet**

**Best for:** Simple studies with one sheet of responses per file (such as open ended responses from surveys).

In this format, every row contains **both the response and the respondent’s segment (demographic) information**.

**Option 2: Separate Respondent Sheet + Question Sheet**

**Best for:**\
Studies with multiple sheets of responses (e.g. one sheet per product or condition), or when doing within-participants testing.

**In this format:**

* You include a **Respondent Info sheet** with metadata like age, gender, or market segment.
* Each **Question/Response sheet** contains open-ended feedback, **linked by Respondent ID**.

# Downloadable Examples

## Open Ended Survey - One Sheet

This sheet is an example of the Option 1 formatting style, where respondent information and open-ended responses are combined in a single sheet.

[Download Here](https://coloop-public.s3.eu-west-2.amazonaws.com/open-ends/example-data/example-open-ends-survey-data-one-sheet.xlsx)

*Example data is a simple open ended survey about the taste and texture of crisps.*

## Open Ended Survey - Respondent & Question Sheet

This sheet is an example of the Option 2 formatting style, where respondent information and response data are stored in separate from sheets.

[Download Here ](https://coloop-public.s3.eu-west-2.amazonaws.com/open-ends/example-data/example-open-ends-survey-data.xlsx)

*Example data is a simple open ended survey about the taste and texture of crisps.*

## Open Ended Conversation Data (Between Participants)

This sheet is an example of open ended conversation data, so each respondent has multiple turns.  Respondent information and response data are stored in separate from sheets for clarity.

[Download Here](https://coloop-public.s3.eu-west-2.amazonaws.com/open-ends/example-data/example-open-ends-conversational-data-between-participants.xlsx)

*Example data is a chat-bot conversation where each participant responded to a series of questions about their experience using a specific painkiller. This is a between-participant example so participants trialled either Ibuprofen or Paracetamol.*

*Each participant had 5 conversation turns about their experience, with responses tagged by the product (e.g., Paracetamol, Ibuprofen).*

*The respondent data sheet contains demographic segments; they enable analysis by user group.*

## Open Ended Conversation Data (Within Participants)

This sheet is an example of open ended conversation data, so each respondent has multiple turns.  Respondent information and response data are stored in separate from sheets for clarity.

[Download Here](https://coloop-public.s3.eu-west-2.amazonaws.com/open-ends/example-data/example-open-ends-conversational-data-within-participants.xlsx)

*Example data is a chat-bot conversation where each participant responded to a series of questions about their experience trying 4 different chocolate products. This is a within-participants example so participants all tried and answered questions about all 4 chocolate bars.*

*Each product has its own sheet which contains the conversation data per product and a \[Tag] for that product, and a separate respondent data sheet includes participant demographics.*

## Best Practices and Tips

1. **Focus on Open-Ended Data**: Ensure that your Excel file only includes open-ended questions and responses. Close-ended questions should be formatted as tags or segments instead.
2. **Data Cleaning**: Before import, thoroughly clean your data:
   * Remove any fully blank rows or columns
   * Ensure consistent formatting across cells
   * Check for and remove any hidden sheets or data
3. **Validate Respondent IDs**: Use Excel's data validation tools to ensure Respondent IDs are unique and consistent across sheets.
4. **Check Turn Numbers**: For Conversation Data sheets, verify that Turn Numbers start at 1 and increment sequentially for each Respondent ID.
5. **Use Clear Naming Conventions**: Choose descriptive names for your segments and tags to facilitate analysis.
6. **Keep It Simple**: If a column doesn't apply to your data, leave it out rather than including empty values.
7. **Review Before Import**: Double-check your file for accuracy and completeness before attempting to import it into CoLoop.

## Common Pitfalls to Avoid

1. **Mixing Open and Closed-Ended Data**: Don't include closed-ended questions as separate columns. Instead, use tags or segments to represent this information.
2. **Inconsistent Respondent IDs**: Ensure that Respondent IDs match exactly across all sheets where they appear.
3. **Non-Sequential Turn Numbers**: In Conversation Data sheets, make sure Turn Numbers are sequential without gaps for each Respondent ID.
4. **Exceeding Response Limit**: Remember that projects with more than 10,000 responses will by default trigger an error.
5. **Using Unsupported File Formats**: Only .xlsx files are supported. Convert your data if it's in a different format.
6. **Incomplete Data**: Ensure all required columns are present in your sheets.
7. **Inconsistent Naming**: Use the exact column headers specified in this guide, including the brackets for \[Tag] and \[Segment] columns.

By following these guidelines, you'll ensure that your data is correctly formatted for import, enabling smooth and accurate analysis of your open-ended responses.

If you encounter any issues or have questions about formatting your data, please don't hesitate to reach out to our support team.