CODEWITHSUNDEEP
excelexcel-formula
QuestionerNo7
QuestionerNo7

Reputation: 620

How to build a line graph from two data sets in one table in Excel

I'm having an issue with Excel to which I can't find an accurate solution online.

I have this table:

PERSON   |  DATE  |   SIZE
==========================
Matt     |  1-10  |   90.1
Jane     |  1-10  |   71.3
Matt     |  1-11  |   90.0  
Jane     |  1-11  |   71.0
Matt     |  1-12  |   89.6
Jane     |  1-13  |   70.2

And so on...

The thing is that this is the best solution for inputting this kind of data. I can't have two different tables, one for each person involved, because more people will probably be involved later on, and they all should be included in the same table.

As you can see, the idea is to monitor their weights. And this would be better achieved visually by a line graph that can tell each person what their progress is.

To this end, I would like to set up one graph that shows each person's progress individually as a different line. Like this:

enter image description here

I was wondering if there was any way of having Excel automatically build this line graph using this table but ordering values according to if cell X has value....

Not being able to find that, I also tried to set up an invisible table that would gather each set in its own column. For that, I was thinking of using an IF statement but I can't seem to find a way of getting that to populate a separate table, row by row, with the data from the original table. On the Internet I can only find how to add up all the values in a certain cell if another certain cell has X value.

I was wondering if anyone out there could give me a hand with this. Thanks in advance!

Upvotes: 1

Views: 14682

Answers (1)

BradyK
BradyK

Reputation: 387

I would use a pivot table and pivot chart to accomplish this.

  1. Insert a pivot table, select your entire data range (including headers).
  2. Select Person, Date, and Size in the field list.
  3. Under pivot table options select pivot chart and choose your desired chart type.
  4. Move Person to Legend Fields, Date to Axis Fields, and Size to Values.
  5. Right click the pivot table and go to PivotTable Options
  6. Under "For empty cells show: " write N/A (this will let Excel ignore missing dates)

Here is an example of the blank values.

Let me know if any further clarification is needed.

Upvotes: 4

Related Questions