It’s common for surveys to take in text responses in various forms, but in the end, to get insights, they need to be quantified for measurements. The process of converting textual responses into quantifiable units is commonly referred to as quantification or scaling. In survey research, this approach is often part of Likert scaling or ordinal scaling. These processes provide a quantitative measure of qualitative data, making it easier to analyze and interpret people’s opinions and attitudes. In post, I’ll demonstrate effective ways to convert different types of qualitative responses into metrics and charts in Excel.
The Method
This can be real-time interactive (as shown in the animations below), or it can be post-survey, meaning the text responses are just pasted in the Responses columns, the visuals will be instantly drawn accordingly. I’ll present two set of the questionnaires one with responses scoped within: Always, Usually, Frequently, Often, Sometimes, Occasionally, Seldom, Rarely, and Never. Another scoped to: Strongly Agree, Agree, Neutral, Disagree, and Strongly Disagree.
The first step is to decide on an ordinal scale for each response. I’ll be creating in-cell bar charts for the responses, so the maximum makes sense to be set at 100, and minimum at 0. But you can choose another scale that makes sense for the situation. So, we create a range or a table of the scale with the Always to Never values ranging from 100 to 0 in a gradient scale. To make this efficient and fool-proof, the responses should only be selectable from a drop-down list. This is done via Data Validation from the range mentioned. Once a selection is made, we can execute a lookup function to retrieve its corresponding ordinal value from the range. Finally, to create the bar charts, we can use Conditional Formatting feature using Data Bars and set the minimum and maximum values according to our scale, and specify Excel to show only the bars (not the actual ordinal values). We can further customize as needed. The following animation shows one survey and its response visuals below recorded in real-time.
Let’s look at another example, this time the responses range from Strongly Agree and to Strong Disagree. Therefore, it makes sense to create a butterfly bar chart that can extend to the right (for agree), and to the left (for disagree), and center for neutral. This time, once we set the score for each response, say, using Likert scale from 1 (Strongly Disagree) to 5 (Strongly Agree), we’ll need another set of values for the bar charts to be drawn from -100% (for Strongly Disagree) to 100% (for Strongly Disagree) consistent with the previous bar chart scaling. We could do without rescaling to -100 to 100 and keep 1 to 5 scale, but we would not get the butterfly bars. Thereafter, the steps are the same, lookup function, then conditional formatting. This survey-taking and instant visuals animation are shown below.
Not only are these sleek, and fool-proof (i.e. no typos by survey-takers are possible), but also meaningful for metrics and creating captivating presention.
I hope you found this post helpful and interesting. Explore this site for more tips and articles. Be sure to also check out my Patreon site where you can find free downloads and optional fee-based code and documentation. Thanks for visiting!
