Sometimes you may face that the creation of a line graph in Excel is not working properly. When creating a Line chart in Microsoft Excel, the chart may show two lines at a time, contain a gap in a line, or the line may not be visible on the chart. In this article, you will learn how to solve all these.
Line Graph in Excel Not Working: 3 Examples with Solutions
Problem 1: Line Graph Command Is Producing Two Lines
In the case of showing two lines in a chart, you need to convert one series of data (in this example, Year) into text format. Look at the following image. We have plotted this chart to show different sales in different years. Inserting a line chart, we get the two lines in the chart.
Our goal is to omit the Year line from the chart.
- For this, we need to convert the Year data into text format. We can simply do this by selecting data and putting an Apostrophe in front of it as shown in the image below.
- Next, you need to drag the Fill Handle to the remaining cells to convert all the Year data into text format.
Here is the Year data converted to text data.
- Now, insert the double-line chart again. At this time, you will see the one line that is the Sales line.
Problem 2: Gap Appearing in a Line Graph
An Excel line chart may contain a gap in the line when you create it. There is a possibility that this issue will occur if there is a blank cell in the data range for the Line chart.
Here is a solution you can use to overcome this issue.
Display empty cells based on interpolation. For that, just do the following.
» In Excel 2007 and Higher Versions:
- Right-click anywhere within the chart after selecting the chart. Then, click on Select Data. A Select Data Source dialog box will pop up.
- You can then select Hidden and Empty Cells. A Hidden and Empty Cells Settings dialog box will appear.
- Choose the “Connect data points with line” option, then press OK twice.
Finally, here is the result.
» Excel 2000-2003:
- To begin, click your Line chart.
- Click Options from the Tools menu.
- Select Interpolated from the Chart tab, then click OK.
Problem 3: Line Not Visible on Chart
Consider a chart with the year on the X-axis and COMBO Y-axis in which data is displayed as four series (three lines on the left Y-axis and one bar graph on the right Y-axis). The lines are all good except for one that isn’t visible. There is a legend for the missing line, but not the actual data line, showing up as part of the data series. You may have tried selecting data/hiding cells/connecting data points with lines, but are not able to get the last data to appear. There is a solution for you discussed below.
After selecting the chart, the Chart Tools group appears on the right-hand side of the Ribbon, including the Design and Format tabs. In the Format tab, you can select different elements of the chart using a dropdown menu on the left. You should see little handles where the points should be if you select a series you cannot see.
Download Practice Workbook
You can download the following practice workbook that we have used to prepare this article
In this tutorial, I have discussed 3 examples of solutions for line graphs in Excel not working. I hope you found this article helpful. Please, drop comments, suggestions, or queries if you have any in the comment section below.