How to make a line chart with 3 series in Google Sheets (2023)

Introduction

Here is the data sheet you can copy:
docs.google.com/spreadsheets/d/1ZO_8NTayGyKNW0MM3WERN1G9e-czVptppbZDV0AtXQw/edit

Video

Hello today, I'm going to show you how to make a line chart with three series of data using the data that my class is talking about right now in this chart, we have the year that the oysters were caught that's, the oysters landing.

Then we have the um a year.

And then the ratio of the floating algae to the sea floor algae, which if that was equal to one would mean, the population of seafloor algae was about the same as the floating algae.

And then over here, the third data series is the pre-settler floating to sea floor.

Algae ratio, which is a one, but that will be included as a third series in order to provide a reference line.

Now the first thing we have to do is, um, oh, wait here's our target.

This is where I'm putting the image up behind me that I can't see right now of more or less the final product there's, a couple of aspects of that we can't easily or quickly reproduce in google sheets.

But we can definitely cover the title.

The legend, the two y axes, and then having the data presented on the same x-axis and here's how we will start going about that.

So before we can make the chart we have to put all the x values in the same column.

So I'm going to take this data, cut it from here paste it down here and then move the y values over one column.

And then the reference values I'm going to do the same thing for so I'm going to copy them I'm going to paste.

Now this sheet that I share is also has like a short list of the instructions here that you can go along with as well.

So let's cut that from down here and paste it over here.

Now we have three groups of y values and a single x series here.

Next let's cut these this value here.

So we want to label our columns what the name of the series is going to be can do it thusly now, this is all extraneous.

So I will delete that.

So now we have each column has the title of the series that is going to be present.

This will be the x-axis.

These will be the on the three sets of y values.

So now we're going to select all this all the way down to here, then we're going to go up here to select insert and chart.

Now the default chart is not what we're looking for.

Let me pull it up here.

But that's.

Okay, we can go over to setup on the right, click, column chart and then switch it to line.

This still looks quite strange right? Well, here, I'm, still in setup, the x-axis needs to be here and then the series we need to remove the year as a separate series, because that is the horizontal axis.

And now we have a we have oyster landings.

We have rounded, um, the rounded values for floating algae to sleep seafloor algae that ratio, and then the pre-settler and now we're having a little carry-over from one of the values right here.

This does not belong there.

There we go.

We got rid of that extraneous value.

What else is weird in here? Oh, my looks like all.

These are reproduced no wonder.

It looked weird let's delete that.

So you see as you delete things here, we can cut these, and it is reflected on the chart in live there.

We go now that's looking a little closer to our target.

It is not quite there yet, though because we have some labels and some formatting to do.

However, even at this point, you have a basic, um view of how this data is going over time.

So you can sort of compare these trends right at this point, but this isn't something that you would screenshot and turn in right.

So we need to look at the format.

Okay.

So we can double click on the chart to reopen the chart editor.

And then we want to go to customize let's start with our horizontal axis.

We need a label there, oops, that's, not where we put the label on it, though it's under charted axes titles.

So horizontal axis title is years.

Now another thing we do want to do with that grid lines and ticks under grid lines and text, if we want to make it look more like that other published chart, we want to change the step values here to say, oh, oops, that's, vertical vertical.

We want one horizontal.

We want 50.

now under series since we want to use labels on both sides.

We need to give ourselves.

So we have the oyster landing is, uh.

We have the rounded floating ratio we're going to put that on the left axis so that's where it needs to be we're going to put oyster landing on the right axis and then we'll give this.

So this was red in that other example, we might as well make it look like it right in the sea floor ratio.

We can change this to a dashed line like a long dash and uh to blue.

And then we can change this other series since this is just a reference value, let's gray that out with a dashed line, let's write it.

One.

Okay, that is getting there.

Now we need some more labels.

We need to label some more axes here so let's go back to chart and axes titles and let's check the vertical axis, that's defaults to the left side.

So let's go to here and make it.

We will make this side, the floating algae.

Yes, floating algae to sea floor.

Algae ratio.

And then on the right vertical axis, we can put oyster landings.

Now, the units here are times 10 because of course, we do want to include unix units wherever we can 10 to the fifth metric tons.

Okay and let's, uh, even out these numbers or take away the numbers on no let's.

Don't.

Take them away.

Let's go over here, right? Vertical axis, minimum value, let's hit it a little bit below zero.

So we can see that zero, minus zero point, 0.22 say, eight, and then the vertical axis now it's been pushed up anyway.

But I just I feel like for completeness that I need to do this over here to eight.

Okay.

Now, uh, that has our side that has our bottom.

We have our tick marks there.

Now, the only thing we really need next is what we need a title for the chart.

So we can go back here.

We can hit chart title and uh, let's see the title over here.

I guess we can use oyster landings and floating algae sea floor.

Algae ratio in the chesapeake can maybe center that there's probably a little bit more formatting.

We can do with that, but the by and large we have, uh, what we are looking for there.

Um.

Now, this line being above one is a little bit.

What if what if I do something else here? And then this should be down to one.

So this is displaying a little bit off so we'll have that intercept that's for our display.

So if we're going to take a picture of it, we need this to, you know, be a one, which is where it's starting out with the historical because it's the same data point.

Okay, that is the basics.

Uh, we now have this chart that you could do the what you see and what it means with right, okay and that's it.

I will talk to you later.

I hope this helped.