How to create a line graph in Excel

When to use a line graph

Line charts work well in the following situations:

1. Good visualization of trends and changes. Of all the variety of Excel charts, a line graph is best suited for showing how different things change over time.
2. Easy to create and read. If you are looking for a simple and intuitively clear way to visualize large and complex data, a line graph is the right choice.
3. Show relationships between multiple data sets. A multiple line graph can help you reveal relationships between two or more variables.

When not to use a line graph

There are a few cases in which a line graph is not suitable:

1. Not suited for large data sets. Line graphs are best to be used for small data sets under 50 values. More values would make your chart more difficult to read.
2. Best for continuous data. If you have discrete data in separate columns, use a bar graph
3. Not suited for percentages and proportions. To display data as a percentage of the whole, you'd better use a pie chart or a stacked column.
4. Not recommended for schedules. While line charts are great to show trends over a certain period, a visual view of projects scheduled over time is better done by a Gantt chart.

How to show and hide lines in the chart

While making a graph with multiple lines, you may not want to display all the lines at a time. So, you can use one of the following methods to hide or remove the irrelevant lines:

1. Hide columns. In your worksheet, right-click a column you don't want to plot in the graph, and click Hide. Once the column is hidden, the corresponding line will disappear from the graph straight away. As soon as you unhide the column, the line will be right back.
2. Hide lines in the chart. If you don't want to mangle the source data, click the Chart Filters button on the right side of the graph, uncheck the data series you want to hide, and click Apply:
   
3. Delete a line. To permanently delete a certain line from the graph, right-click it, and select Delete from the context menu.
   
4. Dynamic line graph with check boxes. To show and hide lines on the fly, you can insert a check box for each line, and make your graph respond to selecting and clearing the check boxes. The detailed instructions to create such a graph can be found here.
   

Change data markers in a line graph

When creating a line chart with markers, Excel uses the default Circle marker type, which in my humble opinion is the best choice. If this marker option does not fit well with the design of your graph, you are free to choose another one:

1. In your graph, double-click on the line. This will select the line and open the Format Data Series pane on the right side of the Excel window.
2. On the Format Data Series pane, switch to the Fill & Line tab, click Marker, expand Marker Options, select the Built-in radio button, and choose the desired marker type in the Type box.
3. Optionally, make the markers larger or smaller by using the Size box.

Change color and appearance of a line

If the default line colors do not look quite attractive to you, here's how you can change them:

1. Double-click on the line you want to re-color.
2. On the Format Data Series pane, switch to the Fill & Line tab, click on the Color drop box, and choose a new color for the line.

If the standard color palette is not sufficient for your needs, click More Colors… and then pick any RGB color you want.

On this pane, you can also change the line type, transparency, dash type, arrow type, and more. For example, to use a dashed line in your graph, click the Dash type drop-down box and choose the pattern you want:

Smooth angles of the line chart

By default, the line graph in Excel is drawn with angles, which works fine most of the time. However, if the standard line chart is not beautiful enough for your presentation or printed materials, there is an easy way to smooth the angles of the line. Here's what you do:

1. Double-click the line you want to smooth.
2. On the Format Data Series pane, switch to the Fill & Line tab, and select the Smoothed line check box. Done!

In case of a multiple line chart, perform the above steps for each line individually.

Fade out the gridlines

The standard Excel line graph includes the horizontal gridlines that make it easier to read the values for data points. However, they do not necessarily need to be so prominently displayed. To make the gridlines less obtrusive, all you have to do is change their transparency. Here's how:

1. In your chart, double-click on any gridline. The blue dots will appear at the end of each gridline, indicating that all the gridlines are selected (please see the screenshot below).
2. On the Fill & Line tab of the Format Major Gridlines pane, set the transparency level to 50% - 80%.

That's it! The gridlines are faded into the background of the chart where they belong:

Create an individual line graph for each row (sparklines)

To visualize trends in a series of data located in rows, you can create a number of very small line charts that reside inside a single cell. This can be done by using the Excel Sparkline feature (please follow the above link for the detailed instructions).

The result will look something similar to this: