Alok Paul
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.
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.
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
- 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)
- 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.
- 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
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.
- 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.
- 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
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)Â
- 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.
- 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.
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.
- 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.
- 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.
- 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.
- 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.
