Can You Create a Survey in Excel?

Creating a survey in Excel can be a straightforward process once you understand the basic steps. While Excel isn't a dedicated survey tool, it can be a useful and cost-effective solution for simple surveys. Here, we'll guide you through creating a survey in Excel, from setting up your questions to analyzing the results.

Before we dive in, ensure you're using a version of Excel that supports creating forms and collecting responses. Excel 2010 and later versions on both Windows and Mac, as well as Excel for the web, support this feature.

Setting Up Your Survey

Your first step is to plan your survey. Decide on the number and type of questions, and consider the survey's purpose and target audience.

Once you've planned your survey, open Excel and create a new workbook. In the first sheet, set up your questions. Each question should be clearly labeled, and answer choices should be distinct. For multiple-choice questions, use dropdown lists for easy selection.

Creating Questions

To create a question, type it into a cell. For multiple-choice questions, list the possible answers in different cells. Select these cells, and click on the 'Data' tab. In the 'Data Tools' group, click on 'Use Data Validation'. On the 'Settings' tab, select 'List' as the validation criteria. Add the list of answers, then click 'OK'.

To make the answers dropdown lists, select the validation cell and go to the 'Input Message' tab. Check 'Show input message when cell is selected' and type a prompt. Click 'OK'. Your answer choices will now appear as a dropdown list when the cell is selected.

Formatting Your Survey

Make your survey visually appealing and easy to use. Use different fonts, colors, and shading to distinguish sections. You can also add instructions for respondents at the beginning of your survey.

To prevent respondents from accidentally changing the format or content of your survey, make the cells read-only. Select the range of cells containing your survey, then click on the 'Home' tab. In the 'Cells' group, click on 'Format as Table'. In the 'Create Table' dialog box, check 'My table has headers' and click 'OK'. In the 'Format as Table' dialog box, click on 'Convert to Table'. In the 'Table Tools' tab, click on 'Properties'. Under 'Table Standout Styles', select 'Clear'. Under 'Total Row', uncheck 'Total Row'. Under 'Properties', check 'Preserve dropdown style'. Click 'OK'. Now, respondents can't change the format of your survey.

Distributing Your Survey

Once your survey is set up, you need to distribute it. You can send it via email, post it on a website, or share it using a link if your version of Excel supports that.

To share your survey as a web page, click on the 'File' tab, then 'Share', followed by 'Publish to the web'. You'll be prompted to choose where to publish your survey and set permissions. Once it's published, you'll get a link to share.

Collecting Responses

As responses come in, you can see them in the same worksheet. To keep your survey results organized, you can move them to a separate sheet or use Excel's data manipulation tools to analyze the results.

Analyzing Your Results

Once you've collected responses, you can analyze them using Excel's data analysis tools. You can calculate averages, count responses, and more. You can also use pivot tables to summarize and compare data.

To insert a pivot table, select your data, then click on the 'Insert' tab. In the 'Tables', click on 'PivotTable'. In the 'Create PivotTable' dialog box, select where you want to place the pivot table and click 'OK'. In the 'Insert Fields' dialog box, drag and drop fields to create your pivot table.

Finally, remember to respect respondent's privacy. Only collect necessary information, and ensure you have permission to collect and use the data. Always securely store and dispose of personal data.