UTS Library

BCII Capstone Project

This guide is by David.Litting@uts.edu.au and Alycia.Bailey@uts.edu.au . If you have any questions or need any help don't hesitate to email us!

Cleaning Survey Data in Excel

When you are trying to place survey data into excel it's often wise to examine it a little before working with it. Ask yourself questions like:

* Is the data numerical, or textual?

Excel is at home working with numbers, so if you have a spreadsheet including numbers like rate this item on a scale of 1-10, or how many time do you do such and such a week? Then Excel will make your life pretty easy. 

If you're asking questions that involve 'controlled vocabularies' then Excel is also pretty easy to work with. You can create controlled vocabularies by using multiple choice survey questions, or by using Likert scales (strongly agree, strongly disagree etc).

If you are using Likert scales, or any similar kind of question (eg: how frequently do you exercise? once a week, twice a week etc) you may find that you can perform more varied analysis by first converting the answers into a number. For example in a Likert scale you could have strongly disagree be represented with a 1 and strongly agree be represented with a 5. You can convert these kinds of text answers to numbers by using the Find and replace function in Excel

*  Are you asking open ended questions?

Open ended questions are usually posed with a blank field in the survey form where the respondent can write any answer they choose. 

If your survey has open ended questions with relatively fixed answers like 'what is your favourite sport?' you'll probably get pretty consistent data that you can work with. However some folks might use different words to say the same thing eg: soccer and football.
If so you can standardize those answers, also by using the Find and replace function in Excel

If you've asked open ended questions that require more than one word to answer and offer a lot of possibilities, like 'where do you see yourself in five years' you'll probably have data that can't be forced into conforming to an excel's way of working. This data is better handled with simple text analysis applications that make word clouds like Wordcloud or Wordle. If you just want a numerical breakdown of how often a word is mentioned, or combinations of words are mentioned you can use tools like this text analyzer












Jacqueline Medvold has also prepared a list of other apps that will help you analyze your survey data

Calculations in Excel

The simplest analysis you can do in excel is basic mathematical calculations, like 'what percentage of respondents said they were scared about the future?' or 'how many respondents said they were 25-50?'. This video will show you how to calculate counts and percentages from survey data.

Tables, Pivot Tables and Charts in Excel

Tables allow you to sort and filter the contents of a spreadsheet, and are pretty invaluable for any sort of analysis you'd want to do. This workbook  (p.2) will take you through the basics of making a table.

Pivot Tables are more complex versions of tables where you assign a data field to an axis (row or column) and then compare that data to different data on the opposite axis. Pivot tables allow you to swap between comparisons really quickly and draw conclusions you wouldn't be able to make with your eye alone. This workbook   (p.5) will take you through the basics of making a pivot table. 

Once you've got data in a table or pivot table Excel can graph or chart that data for you pretty easily. To make charts check out this video. To make pivot charts check out this video 

Charts in Tableau Public

Tableau Public is a data analysis application that uses Excel data to build charts. It takes a bit more getting used to than Excel. 

You can find an example of a chart made with Tableau Public in our workbook. For more information on Tableau take a look at the very helpful Lynda course, although bear in mind this is for the full paid up version, not Tableau public.