Microsoft Excel - analysing data

  Gary Wood 19:23 25 Mar 2007

I habe a spreadsheet of questionnaire results. Each column represents one question. Each row is one person's responses, which are values from 1-5. I need to know, for each column, how many of each value, 1-5, there is. Does anyone know of an easy way to do this?



  VoG II 19:25 25 Mar 2007

A Pivot Table - tutorial click here - turn up sound to hear commentary.

  VoG II 20:32 25 Mar 2007

The alternative if you don't have time to learn Pivot Tables is to use a series of formulas. At the bottom of column B in a set of cells:


(adjust row numbers to suit). Then copy the formulas across to the other columns.

  Gary Wood 21:07 25 Mar 2007


Thanks for your reply.

I had already briefly looked at Pivot Tables, but cannot figure out which columns to put where. I've watched the video you linked to, but still can't figure it out.

Basically, my spreadsheet is formatted with each column representing a question. The first row of the sheet has the question numbers in it (1-74). Beneath that, each row is one person's responses, with values 1-5 for each question. I need a table which has question numbers down the left, then five columns, labelled 1-5, with a count of each response for each question in the data area.

When I create the Pivot Table, the fields I can drag in to the table are just the first column headings (i.e. the question numbers 1-74).

Can you advise me further on where I should put the fields? If you don't mind helping further and it would help for you to see my file, please email me and I'm happy to send it to you.



  VoG II 21:23 25 Mar 2007

Hi Gary

I think I may have been hasty to suggest Pivot Tables - I really don't use them but on first thoughts it seemed to be a potential solution. I can certainly get this approach to work with a single column of numbers but I cannot (yet) see how to do it with multiple columns.

I suggest that you try my COUNTIF approach. Once you have entered those formulas under your data in one column, select all the cells containing the COUNTIF formula, move the cursor to the bottom right of the bottom cell in the selection. The cursor should turn into a + then hold down the left mouse button and drag to the right as far as necessary to copy the formulas across.

  Gary Wood 22:31 25 Mar 2007

Hi VoG,

Thanks for your further reply. Having spent some more time playing around with this, I think I have found a solution using Pivot Tables.

Here's what I did:

1) Insert a blank column at the start of the sheet.

2) Select the data range and launch the Pivot Tables wizard.

3) Select "Multiple Consolidation Values" as the location of the data to analyse.

4) Choose "Create a single page range for me", then specify the range as the whole data set. Click Next.

5) Click the Layout button. Remove all the default field layouts and set up as follows:
Row: "Column" field
Column: "Value" field
Data: "Value" field

Next/OK out of the wizard, and the resultant table has a column for each value (1-5, plus any other values (e.g. free text responses, blanks)) and a row for each question.

I'm not sure why it's necessary to add a blank column to the beginning of the sheet, but it works! Without it, the first question is missed off the pivot table.

Thanks again for your help with this. In fac I may even use your method as the final solution, because I'm designing the sheet for use by other people. Your way would let me create a two-sheet workbook, one sheet with the table containing formulas and the first for the user to enter their data into.



This thread is now locked and can not be replied to.

Elsewhere on IDG sites

Xiaomi Mi Mix 2 review

What went wrong at the Designs of the Year 2017

iPhone X news: Release date, price, new features & specs

Comment regarder des séries et talk-shows américains en France ?