Microsoft Excel: Setting Up Contact Lists for Survey Distribution (Course Evaluation)

Overview

Contact lists are created in Excel before they are uploaded to Qualtrics for distribution purposes. Contact lists can be used when sending Qualtrics surveys to a number of known recipients. This is one way to distribute surveys to participants. Below are the suggested changes to the Excel Contact List so it can be uploaded to Qualtrics. 

Considerations

• Contact Lists contain recipient information, such as their name and email address as well as other identifying information that may be helpful when filtering results of a survey. 
• Typically, course evaluations are distributed via email using a contact list; however, there are alternative distribution methods. See the Distribution Basics Overview (Qualtrics Guide) to learn more. 
• Contact Lists must be saved as .CSV files. 

Setting Up the Spreadsheet

Steps to Combining Department and Course Numbers

If the spreadsheet has the course and section # in separate columns, they must be combined. Qualtrics pulls that information, but it must be formulated as “Course.#”. 

1. In the Excel spreadsheet, make a new column by right-clicking between the Course EAU and Description columns.  
2. Label the column “Course” in the header.  
3. In Cell D2, combine the course and #, so type “LTS.101”. If you are doing this in your own spreadsheet, it would be “Course.#”.  
4. Click on Data>Flash Fill. 
5. Check to ensure the data is populated correctly. 
6. Delete the course and section columns.  

Steps to Adding the Section Number to the Course Name and Number

Next, the section number must be added to the course name and number. 

1. Make a new column between the Description and the Section columns.  
2. Type in the formula CONCATENAE.  Type in “=CONCATENAE (E2, “.”, H2). 
3. Hit enter.  
4. This code combines the course name and the section into one and reformats the column so it can be used in Qualtrics.  
5. Select the dot in the bottom right corner of the cell to “flash fill” the column.  
6. Create a new column next to column C (so it should appear as column D) and copy/paste values.  
7. Delete the other columns with the redundant information within it (course, section).  

Steps to Reformat a Name

If the names appear as last name, first name in the spreadsheet, then they will have to be changed to First Name and Last Name in two separate columns for recipients. If more names are needed (such as an instructor's name for a course evaluation), they will need to be changed to First Name Last Name in one column. 

1. Create a new column to the right of the instructor’s name and name it “Instructor.” This is important because the embedded data within Qualtrics will only recognize the name of the column.  
2. Correctly spell the name in Cell F2 with no comma (first name last name). Ex: George Feeny.  
3. Select Data>Flash Fill and the column will auto populate.  
4. Delete the column F (Instructor Name).  

Changing the Headings in the Spreadsheet 

For the contact list to import correctly into Qualtrics, the spreadsheet headings must be formatted correctly.   

• Please note: Spaces and capitalization are very important.  
• The first four columns are also important as they are primarily recognized within the contact list data in Qualtrics.  
• The remainder of the columns can help with filtering information once surveys are completed. This can be important if you distribute one survey to several audiences. 
• Names of the columns after Column D can be based on your preference.  
• If additional information appears within your spreadsheet isn't helpful for the survey, delete the information. 
• If you are reusing this survey from semester to semester, you might want to review the information used from previous contact lists to ensure data is consistently collected.  

Steps to Change the Heading in a Spreadsheet

1. Click on the heading “Primary first name” and rename FirstName (column A). 
2. Click on the heading for “Primary Last Name” and rename it LastName (column B). 
3. Click on the heading for “Camp Email” and rename it Email (column C). 
4. Create a column D and name it ExternalDataReference.  
5. Leave this column unpopulated.  

How to Add Test Subjects 

Before adding a Contact List to Qualtrics, adding test subject(s) will help test the survey before it becomes live. This is a great way to work out any process issues before sending them out to the contact list.  

Steps on How to Add a Test Subject 

1. Scroll to the bottom of the spreadsheet.  
2. Add the test subjects’ first name (Column A), last name (Column B), and email (Column C).  
3. Leave column D blank.  
4. Add the course name and section (Column E).  
5. The remainder of the information can be left blank, but it is recommended that if you have a lecture and a lab, you place that information into column K.  

Saving the Contact List

The only file format recognized in Qualtrics is .csv. It is equally important to consider how the survey will be distributed as that directly impacts how many Contact Lists will need to be generated from the larger list.  

Steps on How to Save 

1. File>Save As>Term Name (see Year Term Codes for more information)  
   1. Example: For Fall 2025 the code is 2261-endsemester-ContactList 
2. In column J, apply a filter for lab students.  
   1. Search for only Lab students and delete the information.  
3. Name it: CourseTerm#-endsemester-LECT.  
4. Change the file type to CSV and save it.  
5. Undo the deletions and filter for Lecture.  
6. Delete the Lecture information and name it: CourseTerm#-endsemester-LAB. 
7. Change the file type to CSV and save it. 
8. You will end up with two contact lists.