How to Clean Survey Data in Excel (with Detailed Steps)

Get FREE Advanced Excel Exercises with Solutions!

In this article, we will discuss how to collect survey data in Excel and clean it with proper illustrations. Survey works are mostly performed by research or business organizations. Research organizations need a survey when they want to know the feedback on the invention from the users. Like new medicine, vaccine, etc. And for the business organization, they survey to know the new business opportunities, i.e. what customers will need in the future or know the satisfaction level of service provided by that organization.

Survey data can be collected in different ways. Previously, respective agents used to go to the customers and collect information, and then gather all the data in a proper format. The latest way of the survey is digital media like Google Forms, Facebook, etc.


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


How to Collect Survey Data

In this section, we will show how to collect survey data from the digital medium using Google Forms.

ūüďĆ Steps:

  • We go to the google form from any browser.
  • Then, put the name of the form.
  • After that, we will set questions based on our requirements.

Collect survey data

We have a wide range of options to answer that question, e.g. multiple choice, short answer, drop-down, etc.

  • Now, set some questions and their answering type and look at the form.

People will insert information in the form and finally click on Submit button.


Steps to Clean Survey Data in Excel

Now, we have gathered the information collected by the survey in an Excel file. This is what our dataset looks like.

Collected survey data transferred to Excel

This is our raw data. We will clean this data to make it usable. Follow the below steps for that.


Step 1: Clean Extra Spaces from Survey Data Using TRIM Function in Excel

The TRIM function removes all spaces from a text string except for single spaces between words. 
  • We can see our survey data consists of unnecessary spaces. We have to remove those spaces using the TRIM function
  • First, add a new column for modified names. We will show how to remove spaces from the Name¬†column.

  • Put the following column on Cell C5.
  =TRIM(B5)

Use TRIM function to clean survey data

  • Then, press the Enter button and drag the Fill Handle¬†icon.

  • Now, select the data of the Modified Name column and then press Ctrl + C to copy that data.

Copy clean survey data

  • Move to Cell B5.
  • Press Ctrl + V. Then, we will see a Ctrl drop-down list on the bottom side. Click on that down arrow.
  • A list will appear now.
  • Choose the Values (V) option from the Paste Values¬†section.

  • We can see the unnecessary spaces have been removed.

Read More: Using Excel to Clean and Prepare Data for Analysis (10 Examples)


Step 2: Remove Non-Printable Characters from Survey Data

The CLEAN function removes all nonprintable characters from text.

We already removed unnecessary spaces from the dataset. We can use this CLEAN function to remove non-printable characters formed by the numbers 129, 141, 143, 144, and 157 from the Excel dataset.


Step 3: Identify and Highlight Blank Cells Within Survey Data Using Excel Conditional Formatting

In this section, we will identify and highlight the blank cells of the survey dataset using the Conditional Formatting feature.

  • First, select the whole dataset.
  • Click on the Condition Formatting down arrow from the Home¬†tab.
  • Select the New Rule option from the list.

Apply Conditional formatting to clean survey data

  • The New Formatting Rule window appears.
  • Choose the Format only cells that contain the rule type.
  • Then, choose Blanks as the Format.

  • After that, click on the Format feature to highlight the blank cells.

Choose blank cells of the survey data

  • Select the desired color from the Fill tab and press the OK¬†button.

  • We go back to the previous window and get the overview of the Preview.

  • Finally, press the OK¬†button.

We can see blank cells have been filled with the chosen color. Now, if needed we will be able to remove the highlighted cells.

Read More: 19 Practical Data Cleaning Techniques in Excel


Step 4: Format Data with Proper Case

The PROPER function converts a text string to proper case; the first letter in each word to uppercase, and all other letters to lowercase. 

We can see attributes of the Name column must be in the proper format. The PROPER function will format the dataset.

  • Go to Cell C5 and put the following formula.
=PROPER(B5) 

Use of PROPER function to clean survey data

  • Press the Enter button and drag the Fill Handle¬†icon.

We can see data have been formatted properly.

  • Now, copy the data of the Formatted Name column to the Name column.


Step 5: Check Spelling of Survey Data Using F7 Function Key

We already applied some steps to clean the survey data. Now, we will check the spelling of the survey using a keyboard shortcut.

  • For this, we will need to take the help of functional keys. Just press the F7¬†button.

Check spelling of survey data in Excel

  • We will see a pop-up appears seeking permission to check spelling from the beginning.
  • Press Yes¬†there.

  • Now, a new window appears showing the mistakes.
  • There is also an option to choose the correct spelling. We have to select the correct spelling and click on the Change¬†button.

Correction of survey data in Excel

One thing that needs to add here. If there is no spelling error, then this window will not show.

  • When the spell-checking is complete, we will see another pop-up.


Step 6: Delete Duplicates from Survey Data

Now, we will remove the duplicates from the survey data with the default feature of Excel.

  • Now, select the whole dataset.
  • Go to the Data Tools field from the Data tab.
  • Choose the Remove Duplicates¬†option.

Remove duplicates from the survey data

  • The Remove Duplicates window appears.
  • Tick the desired column where to apply the remove duplicate operation.
  • Mark the My data has headers option.

  • Finally, click the OK¬†button.

A pop-up will appear. It shows the number of duplicates in the pop-up.


Step 7: Split Data (If Needed) Using Text to Column Wizard

Now, we will split any data from the survey sheet using the Text to Column feature.

  • Choose the Range B5:B9. Select the Data Tools field from the Data¬†tab.
  • Click on the Text to Column¬†feature.

Split the survey data using text to column feature

  • Convert Text to Column Wizard window appears.
  • Tick the Delimited¬†option.
  • Then, click the Next¬†button.

  • Tick Space as the Delimiters Again, click on the Next option.

  • Choose the Text format and finally click on the Finish¬†button.

  • A pop-up appears to replace data.

  • We can see data has been split successfully.


How to Clean Survey Data in an Automated Way Using Excel Power Query

So far, we have learned how to clean survey data in Excel with different functions, tools, and features in it. Now in this section, we will discuss the automated data cleaning of survey data in Excel using Power Query.

ūüďĆ Steps:

  • Select the whole dataset first.
  • Choose From Table Range from the Data¬†tab.

Form Power Query of Survey data

  • The Create Table window appears. Put a tick on My table has headers¬†option.

  • Lastly, press the OK button and look at the power query data.

  • Click on the heading of the Name column and press the right button of the mouse.
  • Select the Transform option from the Context Menu.
  • They perform the operations one by one. Those are Capitalize Each Word, Trim, and Clean.

Apply different operations to clean survey data in power query

  • This will remove unnecessary spaces and nonprintable characters from the dataset.

  • Again, press the right button and choose the Remove Empty option, this will remove all empty rows.

  • We also have the Remove Duplicates option for removing duplicate data.


Conclusion

In this article, we described the steps to clean survey data in Excel. We also showed automated data cleaning. I hope this will satisfy your needs. Please have a look at our website ExcelDemy and give your suggestions in the comment box.


Related Articles

Alok Paul

Alok Paul

Hello, this is Alok. I am working as an Excel & VBA Content Developer at Exceldemy. I want to provide solutions to various Excel-based problems. I completed my study at East West University major in Telecommunications Engineering. I love traveling, reading books, playing cricket.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo