Save as PDF

Importing and Mapping Microsoft Excel Files

Overview

Excel-based questionnaires are a common RFx format.

For instructions on importing Microsoft Word documents, see Importing and Mapping Microsoft Word Documents.

For additional training, refer to the Academy course Advanced Excel Mapping.

Select the applicable tab for your edition of Responsive.

  • Importing Files from the Project Page

    You can import Excel questionnaires in two ways.

    1. Do one of the following to import files:
      • For a new project, click Upload Document and select the the file from your computer or any of the available storage integration options.
      • If you have already started a project:
        1. Click New Section and select Import File.
        2. When the imported file page appears, click the Menu and select Add New File.
        3. Select the the file from your computer or any of the available storage integration options.
    2. The file will be automatically identified and configured. Do one of the following:
      • If all sheets are auto-identified, the document will be imported and a message will be displayed as shown below. Click Go to Sections to review the sections.
      • When there are unidentified sheets, a message will display as shown below. Do the following:

        1. Click the drop-down arrow view the unmarked sheets. Click Review and Import to review the unidentified sheets.
        2. When the Import page appears, make any required changes and click Import to import the updated file.
        3. If the files are non-standard response templates, or cannot be identified and auto-configured, see the Manually Configuring Non-auto-identified Excel Files section below to manually configure the Excel file.

    Manually Configuring Non-auto-identified Excel Files

    A sample Excel file is displayed below:

    Users can ignore the sheet if it has no questionnaire by clicking Screenshot_2021-12-22_at_12.59.38_AM.png icon beside the sheet's name and selecting Ignore this Worksheet

    Identifying Header Rows

    Users can ignore the rows which will not be helpful in responding to the RFP. Click the row and select Header. The rows till the header rows will be excluded.

    Configuring Sections

    Click the section title and map with the Section from the right navigation. The title will be shown with the S indicator.

    Note: If the section title is not present in the worksheet cells, worksheet name will be taken as a section title.

    Users can also use Auto Identify Sections option so that the system will automatically map the title with the section. The system will auto-identify sections only when the section has a unique style. Select the sections and click Apply.

    Screenshot_2021-12-22_at_1.47.05_AM.png

    Configuring Sub-Sections

    You can configure a Subsection by selecting the row, clicking the Screenshot_2021-12-22_at_2.38.20_AM.png icon beside the Sections icon on the floating toolbar and selecting Level 1 - Sub Section as shown:

    Screenshot_2021-12-22_at_2.41.34_AM.png

    Users can also use Auto Identify option so that the system will automatically map the title with the section. The system will auto-identify sub sections only when the sub section has a unique style.

    Configuring Questions

    Select the question and map with the question by selecting the Question icon from the floating toolbar.

    Users can also use Auto Identify option so that the system will automatically map the title with the question. The system will auto-identify questions only when the question has a unique style.

     Click Apply.

    In a similar way, users can configure Sub-questions.

    Note: If there are identical questions with different answers, we consolidate those questions into one question with multiple answer types upon import

    Configuring Question Numbers

    Click the column containing the question numbers and click the Menu to display additional options as shown below:

    Click the Question No icon to set the column as Question Numbers. The indicator will be shown as N.

     

    Configuring Tags

    If there are several categories/department which has questions, the category/department can be considered as tags, which helps you to filter questions.

    Click the category/department column and map it as tags by clicking the three-stacked icon and selecting Tags. The whole column will be considered as tags.

    Configuring Answers

    Click the response column and select Answer on the floating toolbar. The system will auto-identify and display answer type suggestions based on questions in the document. These suggestions can be configured further manually.

    Answer Headers you choose for a question type will reflect on the Answering Page.

    Screenshot_2021-12-22_at_2.30.27_PM.png

    You can explicitly specify rows that the headers should apply to in the Applicable to Rows column.

    To change the Answer Type, click the drop-down arrow and select an option from the list.

    You can set Q&A pairs to be added to the Answer Library or mark questions as required by selecting the corresponding Index and Required checkboxes.

    Click Add Answer Type, to configure and add an answer type manually.

    To Delete a suggested answer type, click the corresponding trash icon on the far right.

    The system will automatically identify the drop-downs from the document. Click Auto-Identify Drop-downs to view/edit the fields.

     

    The application can handle different types of questions and responses. The following sections describe the available answer types and provide details on the best fit based on use case. 

     

    Answer Type: Radio/Drop-down

    If you respond to a question which has choices in a column. (Example: If Question A has answer choices "Yes" or "No" or "N/A", etc.) you can go ahead with radio/drop-down as an answer type.

    Choose the answer type Dropdown from the dropdown list.

    Add an answer options (displayed on the Answering page) and their corresponding value in a row.

    To add a row, click Add Answer Option.

    To remove a row, click the Trash icon.

    To save the options to the answer type, click Save.

     

    Now, to save the Answer Type, Click Save.

    To add additional options or edit the current answer options, click the Options icon.

     

    Answer Type: Basic Rich Text

    This answer type best suits responses that are expected to be brief but limited to a few words. To set a column to Basic Rich Text, select the column, then click ANSWER.

    Select the Basic Rich Text from the dropdown in the Answer Type and click Save.

     

    Answer Type: Full Rich Text

    This answer type best suits responses that are expected to be descriptive, format rich, include attachments etc., To set a column to Full Rich Text, select the column, then click ANSWER.

    Select the Full Rich Text from the dropdown in the Answer Type and click Save.

     

    Answer Type: Multi-Column Radio

    When the questionnaire calls for you to select one answer in a radio format from multiple columns, the appropriate answer type is a multi-column radio. For example, if question A has 3 responses in columns G, H, and I, as possible options, and the expected response is to fulfil the question requirements of any one of them.

    You can define these answer types as a multi-column radio.

    Select the Multi-Column Radio from the dropdown in the Answer Type.

    Configure the options as required and click Save to save the options.

    Click Save to save the answer type.

    The answer indicator will be shown on both columns.

     

    Answer Type: Date

    To set a column type as Date, select the column, then click Answer.

    Select the answer type as Date.

     

    Answer Type: Multi-Column Drop Down

    When the questionnaire asks that you select one answer from a row across multiple columns, the appropriate answer type is a multi-column drop-down.

    For example: Shown below, question 1 has 3 possible responses in columns K, L, and M.  You are being asked to provide your response in one of the columns to fulfil the question requirements.

    Users can define these answer types as a multi-column drop-down in the application.

    Select the answer type as Multi-Column Dropdown.

    Configure the options as required and click Save to save the options.

    Click Save to save the answer type.

     

    Answer Type: Checkbox

    When responding to a question which has answer choices in a different column and you can select multiple answers, you can choose checkbox as an answer type.

    Configure the options as required and click Save to save the options.

    Click Save to save the answer type.

    Importing Questions and Answers in the Same Column

    Below is an example of both questions and answers are in the same column "B":

    Screenshot_2021-12-22_at_9.02.13_PM.png

    To import the excel questionnaire with Q and A in the same column follow steps below:

    1. Import the file into the project.

    2. Select column B and mark as Question.

    3. Select the B column and click the arrow next to Answer on the floating toolbar. Then click Mark Rest as Answer.

    4. A confirmation Popup is displayed.

    5. Check to make sure you have all the rows you need listed in the Applicable to Rows.

    Note:

    • You can use comma "," as a delimiter to add/specify more rows in the Applicable to Rows field
    • You can also specify a range of rows. For example: 3,5,7,9,11-19,23,25-32,35

    6. Review the markings and click Import.

     

    Note: You can import few question and answer in the same column, and the rest in different columns too. While configuring each, you can specify the rows for which it has to be applied.

    Screenshot_2021-12-22_at_9.48.47_PM.png

    • In the above example, questions are configured in column A. Row number 3 is configured as Answer in Column A and row number 5 in column A is configured as Question and row number 5 in column B is configured as Answer.
    • If Row number 3 under column A is configured as both Question and Answer, on export, question will be replaced by the answer.
    • If Row number 2 is configured as Question and Row number 3 as Answer, both question and answer will be exported.

     

    Importing the File

    Once all the answer types been configured, click Import to initiate import process.

    Imported Sections

  • Essentials features are subscription-based and may not be available for all users. Contact your account manager, or accountmanagers@responsive.io, for more details.

    Importing Files from the Project Page

    You can import Excel questionnaires in two ways.

    1. Do one of the following to import files:
      • For a new project, click Upload Document and select the the file from your computer or any of the available storage integration options.
      • If you have already started a project:
        1. Click New Section and select Import File.
        2. When the imported file page appears, click the Menu and select Add New File.
        3. Select the the file from your computer or any of the available storage integration options.
    2. The file will be automatically identified and configured. Do one of the following:
      • If all sheets are auto-identified, the document will be imported and a message will be displayed as shown below. Click Go to Sections to review the sections.
      • When there are unidentified sheets, a message will display as shown below. Do the following:
        1. Click the drop-down arrow view the unmarked sheets. Click Review and Import to review the unidentified sheets.
        2. When the Import page appears, make any required changes and click Import to import the updated file.
        3. If the files are non-standard response templates, or cannot be identified and auto-configured, see the Manually Configuring Non-auto-identified Excel Files section below to manually configure the Excel file.

    Manually Configuring Non-auto-identified Excel Files

    A sample Excel file is displayed below:

    Users can ignore the sheet if it has no questionnaire by clicking Screenshot_2021-12-22_at_12.59.38_AM.png icon beside the sheet's name and selecting Ignore this Worksheet

    Identifying Header Rows

    Users can ignore the rows which will not be helpful in responding to the RFP. Click the row and select Header. The rows till the header rows will be excluded.

    Configuring Sections

    Click the section title and map with the Section from the right navigation. The title will be shown with the S indicator.

    Note: If the section title is not present in the worksheet cells, worksheet name will be taken as a section title.

    Users can also use Auto Identify Sections option so that the system will automatically map the title with the section. The system will auto-identify sections only when the section has a unique style. Select the sections and click Apply.

    Screenshot_2021-12-22_at_1.47.05_AM.png

    Configuring Sub-Sections

    You can configure a Subsection by selecting the row, clicking the Screenshot_2021-12-22_at_2.38.20_AM.png icon beside the Sections icon on the floating toolbar and selecting Level 1 - Sub Section.

    Users can also use Auto Identify option so that the system will automatically map the title with the section. The system will auto-identify sub sections only when the sub section has a unique style.

    Configuring Questions

    Select the question and map with the question by selecting the Question icon from the floating toolbar.

    Users can also use Auto Identify option so that the system will automatically map the title with the question. The system will auto-identify questions only when the question has a unique style.

    Screenshot_2021-12-22_at_2.48.27_AM.png

     Click Apply.

    Screenshot_2021-12-22_at_2.49.43_AM.png

    In a similar way, users can configure Sub-questions.

    Note: If there are identical questions with different answers, we consolidate those questions into one question with multiple answer types upon import

    Configuring Question Numbers

    Click the column containing the question numbers and click the Menu to display additional options as shown below:

    Click the Question No icon to set the column as Question Numbers. The indicator will be shown as N.

    Configuring Tags

    If there are several categories/department which has questions, the category/department can be considered as tags, which helps you to filter questions.

    Click the category/department column and map it as tags by clicking the three-stacked icon and selecting Tags. The whole column will be considered as tags.

    Configuring Answers

    • Click the response column and select Answer on the floating toolbar. The system will auto-identify and display answer type suggestions based on questions in the document. These suggestions can be configured further manually.

      Answer Headers you choose for a question type will reflect on the Answering Page.

      Screenshot_2021-12-22_at_2.30.27_PM.png

      You can explicitly specify rows that the headers should apply to in the Applicable to Rows column.

      To change the Answer Type, click the drop-down arrow and select an option from the list.

      You can set Q&A pairs to be added to the Answer Library or mark questions as required by selecting the corresponding Index and Required checkboxes.

      Click Add Answer Type, to configure and add an answer type manually.

      To Delete a suggested answer type, click the corresponding trash icon on the far right.

      The system will automatically identify the drop-downs from the document. Click Auto-Identify Drop-downs to view/edit the fields.

     

    The application can handle different types of questions and responses.

    • The following sections describe the available answer types and provide details on the best fit based on use case. 

       

      Answer Type: Radio/Drop-down

      If you respond to a question which has choices in a column. (Example: If Question A has answer choices "Yes" or "No" or "N/A", etc.) you can go ahead with radio/drop-down as an answer type.

      Choose the answer type Dropdown from the dropdown list.

      Add an answer options (displayed on the Answering page) and their corresponding value in a row.

      To add a row, click Add Answer Option.

      To remove a row, click the Trash icon.

      To save the options to the answer type, click Save.

       

      Now, to save the Answer Type, Click Save.

      To add additional options or edit the current answer options, click the Options icon.

       

      Answer Type: Basic Rich Text

      This answer type best suits responses that are expected to be brief but limited to a few words. To set a column to Basic Rich Text, select the column, then click ANSWER.

      Select the Basic Rich Text from the dropdown in the Answer Type and click Save.

       

      Answer Type: Full Rich Text

      This answer type best suits responses that are expected to be descriptive, format rich, include attachments etc., To set a column to Full Rich Text, select the column, then click ANSWER.

      Select the Full Rich Text from the dropdown in the Answer Type and click Save.

       

      Answer Type: Multi-Column Radio

      When the questionnaire calls for you to select one answer in a radio format from multiple columns, the appropriate answer type is a multi-column radio. For example, if question A has 3 responses in columns G, H, and I, as possible options, and the expected response is to fulfil the question requirements of any one of them.

      You can define these answer types as a multi-column radio.

      Select the Multi-Column Radio from the dropdown in the Answer Type.

      Configure the options as required and click Save to save the options.

      Click Save to save the answer type.

      The answer indicator will be shown on both columns.

       

      Answer Type: Date

      To set a column type as Date, select the column, then click Answer.

      Select the answer type as Date.

       

      Answer Type: Multi-Column Drop Down

      When the questionnaire asks that you select one answer from a row across multiple columns, the appropriate answer type is a multi-column drop-down.

      For example: Shown below, question 1 has 3 possible responses in columns K, L, and M.  You are being asked to provide your response in one of the columns to fulfil the question requirements.

      Users can define these answer types as a multi-column drop-down in the application.

      Select the answer type as Multi-Column Dropdown.

      Configure the options as required and click Save to save the options.

      Click Save to save the answer type.

       

      Answer Type: Checkbox

      When responding to a question which has answer choices in a different column and you can select multiple answers, you can choose checkbox as an answer type.

      Configure the options as required and click Save to save the options.

      Click Save to save the answer type.

    Importing Questions and Answers in the Same Column

    Below is an example of both questions and answers are in the same column "B":

    Screenshot_2021-12-22_at_9.02.13_PM.png

    To import the excel questionnaire with Q and A in the same column follow steps below:

    1. Import the file into the project.

    2. Select column B and mark as Question.

    3. Select the B column and click the arrow next to Answer on the floating toolbar. Then click Mark Rest as Answer.

    4. A confirmation Popup is displayed.

    5. Check to make sure you have all the rows you need listed in the Applicable to Rows.

    Note:

    • You can use comma "," as a delimiter to add/specify more rows in the Applicable to Rows field
    • You can also specify a range of rows. For example: 3,5,7,9,11-19,23,25-32,35

    6. Review the markings and click Import.

     

    Note: You can import few question and answer in the same column, and the rest in different columns too. While configuring each, you can specify the rows for which it has to be applied.

    Screenshot_2021-12-22_at_9.48.47_PM.png

    • In the above example, questions are configured in column A. Row number 3 is configured as Answer in Column A and row number 5 in column A is configured as Question and row number 5 in column B is configured as Answer.
    • If Row number 3 under column A is configured as both Question and Answer, on export, question will be replaced by the answer.
    • If Row number 2 is configured as Question and Row number 3 as Answer, both question and answer will be exported.

     

    Importing the File

    Once all the answer types been configured, click Import to initiate import process.

    Imported Sections

Was this article helpful?

/