How To Input Multiple Responses For 1 Variable in Excel

Question

Hello, I have been having some trouble finding an answer for how to do this, probably because I'm having trouble explaining it. I'm trying to set up an excel spreadsheet to account for multiple data points under a single variable; essentially, for each case (row) there is a variable of diagnosis (column), and for most cases there are 2-4 separate diagnoses.  I want to be able to create a bubble chart looking at the frequencies of each diagnosis by age and gender (other variables I have), but at the moment I can only do comparisons with "Diagnosis A" (one column), "Diagnosis B" (second column) and so on, instead of all the instances of each diagnosis together. Is there a way to fix this?

Answer 1

Hi,

Select the entire dataset (including the header row) and go to Insert > Table.  Click on any one cell in the Table and go to Data > From Table/Range.  In the Query Editor, select the first 3 columns, right click and select "Unpivot other columns".  Rename columns, if required.  Click on Close and Load.

Now try to build your desired chart.

Hope this helps.

Answer 2

Here's the way I have the data laid out

And I'm looking to do a bubble chart like so

Answer 3

Hi,

Share your data layout and if possible show the expected result (not your desired graph but just the form in which you would like to transform your original table).