Excel - charts from questionnaire data


Hi folks

The work I have done in the past has been very text orientated. I think to expand the things I can offer I need to improve my Excel skills. Had to turn down a job as I couldn’t think how to do it! Have produced simple Excel charts but this was much more complicated.

The task was to produce many charts from a .csv that had been produced from a multiple answer questionnaire. One of the questions, for example, was “I would recommend” with many “Agree”, “Disagree” “Partly Agree” type answers. Each respondent was listed with their answer.

Another question listed 7 items and the respondents had answered on a scale of importance from 1-8.

Many more questions that needed charts producing.

How do you, for example, assign all the “agree” responses so they don’t appear in the chart as an individual entry? If that makes sense…

This is probably very simple for those of you with good Excel experience and I feel a bit of an idiot that I can’t think where to start with this. However, I believe if I can’t do something then I need to learn how. Can anyone give me any advice on where to start with some sort of online learning tool or tell me what I should be looking up on Google?

I hope you don’t mind my asking - I will eventually work this out but I thought you folks might help me cut down the amount of time it takes.


It depends a bit on how the survey has marked the excel sheet, but if it’s an X you can just do a find and replace on that column and replace “X” with “1” and then do a simple bar chart from there?


You could highlight that column only and make it into a chart?


Have you experience of pivot tables/charts? Or countif formulae? I would use a countif or pivot depending on charts required - simple one =countif(A1:A10,“Agree”) should count all the agree answers in the column you can then use the figures provided to create a chart. With a Pivot table you use the insert menu and can select the data and how it then goes into columns/rows and if count or sum. I think though a countif would be sufficient for this task