CODEWITHSUNDEEP
excel
runningtrumpet
runningtrumpet

Reputation: 1

In Excel, formatting three columns of data for a multi-line graph

I have three columns of data: date, code group, and quantity. I want to do a simple multi-line graph, where each line is a code group, showing the quantity for each date. Date is X-axis, quantity y-axis.

Date Code Quantity
01/01/2022 A 8
01/01/2022 B 2
01/01/2022 C 5
02/01/2022 A 76
02/01/2022 B 1
02/01/2022 C 34
03/01/2022 A 57
03/01/2022 B 24
03/01/2022 C 9

However, my three columns of data are formatted properly to do a graph for this. Each code group has it's own date row. So, I have duplicate dates in the date column. I have attached a photo that shows a sample of what my data looks like.

How can I format this properly to do a multi-line graph, keeping in mind my actual data set contains over one hundred different codes and a few years of monthly data?

A simple transpose here won't work to my understanding. I'll need to do something more advanced but I'm not sure what. And I've seen solutions to this problem where folks only have less than 10 groups, but what about over a hundred? Any way to do it with a few clicks?

Upvotes: 0

Views: 718

Answers (1)

A.Steer
A.Steer

Reputation: 409

You can use a pivot table to get the desired output.

If you have the row values as your dates, the columns as your code and the value as the max of your quantity you will then produce a table that has a single line per date.

Pivot Table

Your data will look like the following:

Row Label A B C
01/01/2022 8 2 5
02/01/2022 76 1 34
03/01/2022 57 24 9

You can then produce a line graph based on the pivot table.

Upvotes: 0

Related Questions