Creating a chart or pivot with text data sets (i.e., responses from a survey)

**Kaycee831** · 04-14-2023, 02:02 AM

Hello,

I work for a charity and we have a diversity monitoring form which we ask all volunteers or prospective staff to complete as part of the recruitment processes to collect demographic data.

I have a set of answers for this form (all text based apart from age range) which i need to present in a way that can be easily viewed in terms of numbers and visually represented in a bar chart (or other suitable chart) showing the number of responses for a particular 'characteristic'.

I can't see any way of doing this without actually counting each answer and creating a separate table - which is time-consuming.

I would be very grateful if someone could please assist on how i can create a pivot table and chart that would best fit this scenario?

very many thanks
demo data.xlsxK

**dflak** · 04-15-2023, 09:50 PM

You are looking at the problem sideways. I took the data, copied it and transposed it. This put it into a normalized format. I made a table of this and then made a couple of pivot tables out of it.

For grins, I added a slicer for the age brackets and linked it to all the pivot table. Whatever you select for a bucket is reflected on the other metrics too. Read up on tables, pivot tables and slicers.