When you create a line chart in Microsoft Excel, your chart may display only a single plot line. It's often helpful, however, to plot two or more lines on the same chart, for example you may have multiple data series that were collected at the same time, or you want to compare data taken at different times. To create an Excel chart with multiple lines, create a new chart with multiple plot lines or add plot lines to an existing chart.
A tutorial on how to make line graphs and scatter plots with Excel. Chart Studio is the easiest and fastest way to make and share graphs online. To add the Excel file to your workbook, click where you want to insert the picture inside Excel. On the INSERT tab inside Excel, in.
Create a New Chart With Multiple Lines
When you create a new chart in Excel, you must specify the data to be plotted (for more information please see How to Make a Line Graph in Microsoft Excel). When you create a line chart using one column of data Excel adds only one plot line to the chart. But when you include two or more columns of data, Excel treats each column as a separate data series and automatically creates separate lines on the chart for the data in each column.
Enter Your Data
If you already have a spreadsheet with data entered into columns, skip to the next step (Create Your Chart, below).
In the first row of the worksheet, enter the labels for each of the data series to plot. Since each data series (the data for each separate line to be plotted) must be in a separate column, you must enter the labels for each data series in a separate column on the first row. Excel uses the labels in the first row of each column to label the lines in the chart for the data in that column.
Enter your data into each of your columns, in the rows below the labels.
Here's an example worksheet showing two data series. The data for each series is in a separate column, and the label to display on the chart for each is in the first row of each column.
1. Select all the cells in each column that contain your data.
Tips
If there is at least one empty row and one empty column between the data for your chart and any other data in the worksheet (or if the data for your chart is the only data in the worksheet), you can select all your data cells by selecting any cell of the data and typing Ctrl-A.
2. On the Office Ribbon select the Insert tab, click on Line in the Charts section of the ribbon, and then select the type of chart you want to create.
Tips
There are multiple types of line charts used for different purposes. If you're unsure what type of line chart to create, select either Line or Line with Markers. Don't worry if it's not right the first time, since you can change it later if you need to.
Excel creates a new chart that displays a separate plot line for each column of data.
Add a Line to an Existing Chart
If you already have a line chart, you may add a new plot line to it by editing the Data Source for the chart.
Enter or copy and paste the data for the new plot line into the column immediately to the right of the original data. Make sure to add a label to the first row of the new column so the chart can display a label for the new plot line.
Click on the chart that displays the original data to select it.
Right-click on the chart and choose Select Data... from the pop-up menu. Alternately, click Select Data from the Data section of the Office Ribbon in the Chart Tools Design tab.
The Select Data Source dialog opens. The data displayed in the chart is shown in the Chart data range field.
Tips
TIP: The dollar signs in the Chart data range field indicate that the field uses absolute cell references.
4. In the Chart data range field, change the last letter of the displayed range, to make it match the letter of the last column of the new data.
In the example, the Chart data range displays =Sheet2!$A$1:$A$12, but the new column of data is column $B. So the last A (in the cell reference $A$12) must be changed to a B. The resulting Chart data range displays =Sheet2!$A$1:$B$12.
After changing the Chart data range, press the OK button. The chart updates to display an additional plot line for the new data.
Tips
There are two other ways to include new data in the chart from the Select Data Source dialog.
1. Press the range selection button (the small button on the right side of the Chart data range field that looks like a grid), and then use your mouse to select all the chart data in the worksheet. Make sure to include both the old and new data, and to include the first row of both columns that includes the plot labels.
2. Press the Add icon in the Legend Entries (Series) list. You must then separately type in or select (using the range selection button on each field) only the first row of data in the new columns, and then all the other data in the new columns except the first row.
Video of the Day
Brought to you by Techwalla
More Articles
This short tutorial will walk you through adding a line in Excel graph such as an average line, benchmark, trend line, etc.
In the last week's tutorial, we were looking at how to make a line graph in Excel. In some situations, however, you may want to draw a horizontal line in another chart to compare the actual values with the target you wish to achieve.
The task can be performed by plotting two different types of data points in the same graph. In earlier Excel versions, combining two chart types in one was a tedious multi-step operation. Microsoft Excel 2013, Excel 2016 and Excel 2019 provide a special Combo chart type, which makes the process so amazingly simple that you might wonder, 'Wow, why hadn't they done it before?'.
- How to customize the line
How to draw an average line in Excel graph
This quick example will teach you how to add an average line to a column graph. To have it done, perform these 4 simple steps:
- Calculate the average by using the AVERAGE function.In our case, insert the below formula in C2 and copy it down the column:
=AVERAGE($B$2:$B$7)
- Select the source data, including the Average column (A1:C7).
- Go to the Insert tab > Charts group and click Recommended Charts.
- Switch to the All Charts tab, select the Clustered Column - Line template, and click OK:
Done! A horizontal line is plotted in the graph and you can now see what the average value looks like relative to your data set:
In a similar fashion, you can draw an average line in a line graph. The steps are totally the same, you just choose the Line or Line with Markers type for the Actual data series:
Tips:- The same technique can be used to plot a median For this, use the MEDIAN function instead of AVERAGE.
- Adding a target line or benchmarkline in your graph is even simpler. Instead of a formula, enter your target values in the last column and insert the Clustered Column - Line combo chart as shown in this example.
- If none of the predefined combo charts suits your needs, select the Custom Combination type (the last template with the pen icon), and choose the desired type for each data series.
How to add a line to an existing Excel graph
Adding a line to an existing graph requires a few more steps, therefore in many situations it would be much faster to create a new combo chart from scratch as explained above.
But if you've already invested quite a lot of time in designing you graph, you wouldn't want to do the same job twice. In this case, please follow the below guidelines to add a line in your graph. The process may look a bit complicated on paper, but in your Excel, you will be done in a couple of minutes.
- Insert a new column beside your source data. If you wish to draw an average line, fill the newly added column with an Average formula discussed in the previous example. If you are adding a benchmarkline or target line, put your target values in the new column like shown in the screenshot below:
- Right-click the existing graph, and choose Select Data… from the context menu:
- In the Select Data Source dialog box, click the Add button in the Legend Entries (Series)
- In the Edit Series dialog window, do the following:
- In the Series namebox, type the desired name, say 'Target line'.
- Click in the Series value box and select your target values without the column header.
- Click OK twice to close both dialog boxes.
- The target line series is added to the graph (orange bars in the screenshot below). Right-click it, and choose Change Series Chart Type… in the context menu:
- In the Change Chart Type dialog box, make sure Combo > Custom Combination template is selected, which should be by default. For the Target line series, pick Linefrom the Chart Type drop-down box, and click OK.
Done! A horizontal target line is added to your graph:
How to plot a target line with different values
In situations when you want to compare the actual values with the estimated or target values that are different for each row, the method described above is not very effective. The line does not allow you to pin point the target values exactly, as the result you may misinterpret the information in the graph:
To visualize the target values more clearly, you can display them in this way:
To achieve this effect, add a line to your chart as explained in the previous examples, and then do the following customizations:
- In your graph, double-click the target line. This will select the line and open the Format Data Series pane on the right side of your Excel window.
- On the Format Data Series pane, go to Fill & Line tab > Line section, and select No line.
- Switch to the Marker section, expand Marker Options, change it to Built-in, select the horizontal bar in the Type box, and set the Size corresponding to the width of your bars (24 in our example):
- Set the marker Fill to Solid fill or Pattern fill and select the color of your choosing.
- Set the marker Border to Solid line and also choose the desired color.
The screenshot below shows my settings:
Tips to customize the line
To make your graph look even more beautiful, you can change the chart title, legend, axes, gridlines and other elements as described in this tutorial: How to customize a graph in Excel. And below you will find a few tips relating directly to the line's customization.
Display the average / benchmark value on the line
In some situations, for example when you set relatively big intervals for the vertical y-axis, it may be hard for your users to determine the exact point where the line crosses the bars. No problem, just show that value in your graph. Here's how you can do this:
- Click on the line to select it:
- With the whole line selected, click on the last data point. This will unselect all other data points so that only the last one remains selected:
- Right-click the selected data point and pick Add Data Label in the context menu:
The label will appear at the end of the line giving more information to your chart viewers:
Add a text label for the line
To improve your graph further, you may wish to add a text label to the line to indicate what it actually is. Here are the steps for this set up:
- Select the last data point on the line and add a data label to it as discussed in the previous tip.
- Click on the label to select it, then click inside the label box, delete the existing value and type your text:
- Hover over the label box until your mouse pointer changes to a four-sided arrow, and then drag the label slightly above the line:
- Right-click the label and choose Font… from the context menu.
- Customize the font style, size and color as you wish:
When finished, remove the chart legend because it is now superfluous, and enjoy a nicer and clearer look of your chart:
Change the line type
If the solid line added by default does not look quite attractive to you, you can easily change the line type. Here's how:
- Double-click the line.
- On the Format Data Series pane, go Fill & Line > Line, open the Dashtype drop-down box and select the desired type.
For example, you can choose Square Dot:
And your Average Line graph will look similar to this:
Extend the line to the edges of the chart area
As you can notice, a horizontal line always starts and ends in the middle of the bars. But what if you want it to stretch to the right and left edges of the chart?
Here is a quick solution: double-click the on the horizontal axis to open the Format Axis pane, switch to Axis Options and choose to position the axis On tick marks:
However, this simple method has one drawback - it makes the leftmost and rightmost bars half as thin as the other bars, which does not look nice.
As a workaround, you can fiddle with your source data instead of fiddling with the graph settings:
- Insert a new row before the first and after the last row with your data.
- Copy the average/benchmark/target value in the new rows and leave the cells in the first two columns empty, as shown in the screenshot below.
- Select the whole table with the empty cells and insert a Column - Line chart.
Now, our graph clearly shows how far the first and last bars are from the average:
That's how you add a line in Excel graph. I thank you for reading and hope to see you on our blog next week!