Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have 15 colleagues who have answered 'numerous questions' over the years in a questionnaire. The questions used to be standardised and all colleagues had the same questions. Unfortunately standards slipped and people didn't notice that different terminology was being used in some questionnaires.

I now need to standardise the questionnaire again, but basing my list on the most used terminology. Majority rules. i.e. Do you have a Pet? rather than, do you have an animal in the house?

I've put the data in to relevant columns, but now I need to be able to see which questions appeared the most in the questionnaires.

My PT shows me what has been answered and how many times, but what would help would be a sub-total showing me it's % of the total.

I could just write a formula manually next to the PT but I thought the PT would include something that wouold allow extra manipulation of the data, but I'm struggling to find the missing link...

Updated question - I updated the question because I realised that it didn't really explain my dilemma very well.

Many thanks


Cheap and cheerful example below:

---Question 1--------Chosen

---Colleague 1-------1

---Colleague 2-------1

---Colleague 3-------1

---Colleague 3-------1


-------------% Sub-total----26% ((4/15)*100)---

share|improve this question
up vote 0 down vote accepted

This is a question that comes up a lot, and as far as I’m aware, there’s no automatic way to do this. The only solution I’ve found is to build a helper column. I discovered this solution from the following post on the ExcelForum website (you’ll need to signup to the forum in order to download the sample file):

Here’s the link to the entire discussion:

Scroll down and look for the solution posted by steven1001 (it's comment #7)

share|improve this answer
Thanks Daljit. Your reply helped me realise that i hadn't explained myself very well. The example spreadsheet is great, but it is looking at a different type of problem - hence my hopefully better explination of the dilemma. – RocketGoal Jul 5 '10 at 15:35
Nope, I think the problem is something that a Pivot Table can't answer itself, but that we need to use helper columns to use within the Pivot Table. Not easy when the data is like mine but possible I suppose. Your answer led me to a better understanding of my problem. Thanks. – RocketGoal Jul 12 '10 at 15:08

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .