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 multiple known recipients. This is one way to distribute surveys to participants. Below are the suggested changes to the Excel Contact List to enable uploading to Qualtrics. 

Considerations

• These instructions follow a practice document associated with an LTS Training session. Before you review the steps below, it is important to note that every course evaluation is conducted differently; please review how it has been conducted previously and discuss distribution with your chair before proceeding. 
• The first FOUR columns in a contact list for Qualtrics must contain the FirstName (column A), LastName (column B), Email(column C), and ExternalDataReference (Column D).  
• Contact lists can be created manually or created from information found in Getlist or CampS, which means not all steps will apply. If you are confused, please reach out to LTS Training (training@uwec.edu) for support.  
• The information in the contact list will help with data collection and analysis once the survey has been completed. 
• Contact Lists contain recipient information, such as their name and email address, as well as other identifying information that may be helpful when filtering the survey results 
• Typically, course evaluations are distributed via email using a contact list; however, alternative distribution methods exist. 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 an Excel spreadsheet, make (or select) a new column. 
   1. If you are using the practice worksheet,  the column should be 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 (or select) a new blank column between the Description and the Section columns.  
2. Type in the formula CONCATENATE.  Type in “=CONCATENATE (E2, “.”, H2). 
   1. This code combines the course name and the section into one and reformats the column so it can be used in Qualtrics.  
3. Hit enter.  
4. Select the dot in the bottom right corner of the cell to “flash fill” the column.  
5. Create a new column next to column C (so it should appear as column D) and copy/paste values.  
6. 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 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.