How to create a line graph in Excel

The tutorial explains the process of making a line graph in Excel step-by-step and shows how to customize and improve it.

The line graph is one of the simplest and easiest-to-make charts in Excel. However, being simple does not mean being worthless. As the great artist Leonardo da Vinci said, "Simplicity is the greatest form of sophistication." Line graphs are very popular in statistics and science because they show trends clearly and are easy to plot.

So, let's take a look at how to make a line chart in Excel, when it is especially effective, and how it can help you in understanding complex data sets.

Excel line chart (graph)

A line graph (aka line chart) is a visual that displays a series of data points connected by a straight line. It is commonly used to visually represent quantitative data over a certain time period.

Typically, independent values such as time intervals are plotted on the horizontal x-axis while dependent values such as prices, sales and the like go to the vertical y-axis. Negative values, if any, are plotted below the x-axis.

The line's falls and rises across the graph reveal trends in your dataset: an upward slope shows an increase in values and a downward slope indicates a decrease.
Excel line graph

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 make a line graph in Excel

To create a line graph in Excel 2016, 2013, 2010 and earlier versions, please follow these steps:

  1. Set up your data

    A line graph requires two axes, so your table should contain at least two columns: the time intervals in the leftmost column and the dependent values in the right column(s).

    In this example, we are going to do a single line graph, so our sample data set has the following two columns:
    Source data for a line graph

  2. Select the data to be included in the chart

    In most situations, it is sufficient to select just one cell for Excel to pick the whole table automatically. If you'd like to plot only part of your data, select that part and be sure to include the column headers in the selection.

  3. Insert a line graph

    With the source data selected, go to the Insert tab > Charts group, click the Insert Line or Area Chart icon and choose one of the available graph types.

    As you hover the mouse pointer over a chart template, Excel will show you a description of that chart as well as its preview. To inset the chosen chart type in your worksheet, simply click its template.

    In the screenshot below, we are inserting the 2-D Line graph:
    Making a line graph in Excel

    Basically, your Excel line graph is ready, and you can stop at this point… unless you want to do some customizations to make it look more stylish and attractive.

How to graph multiple lines in Excel

To draw a multiple line graph, perform the same steps as for creating a single line graph. However, your table must contain at least 3 columns of data: time intervals in the left column and observations (numeric values) in the right columns. Each data series will be plotted individually.

With the source data highlighted, go to the Insert tab, click the Insert Line or Area Chart icon, and then click 2-D Line or another graph type of your choosing:
Making a multiple line graph in Excel

A multiple line graph is immediately inserted in your worksheet, and you can now compare the sales trends for different years to one another.
Multiple line graph

When creating a multiple line chart, try to limit the number of lines to 3-4 because more lines would make your graph look cluttered and hard to read.

Excel line chart types

In Microsoft Excel, the following types of the line graph are available:

Line. The classic 2-D line chart demonstrated above. Depending on the number of columns in your data set, Excel draws a single line chart or multiple line chart.

Stacked Line. It is designed to show how parts of a whole change over time. The lines in this graph are cumulative, meaning that each additional data series is added to the first, so the top line is the total of all the lines below it. Therefore, the lines never cross.
Stacked Line graph

100% Stacked Line. It is similar to a stacked line chart, with the difference that the y-axis shows percentages rather than absolute values. The top line always represents a total of 100% and runs straight across the top of the chart. This type is typically used to visualize a part-to-whole contribution over time.
100% Stacked Line graph

Line with Markers. The marked version of the line graph with indicators at each data point. The marked versions of Stacked Line and 100% Stacked Line graphs are also available.
Line with Markers

3-D Line. A three-dimensional variation of the basic line graph.
3-D line chart

How to customize and improve an Excel line chart

The default line chart created by Excel already looks nice, but there is always room for improvement. To give your graph a unique and professional look, it makes sense to begin with the common customizations such as:

In general, you can adjust any element of your graph as explained in How to customize a chart in Excel.

Additionally, you can do a few customizations specific to a line graph as explained below.

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:
    Hide one or more lines in the chart.
  3. Delete a line. To permanently delete a certain line from the graph, right-click it, and select Delete from the context menu.
    Delete a line in a graph.
  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.
    Dynamic line graph with check boxes

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.

Choosing data markers for a line graph

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.

Changing the line color

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:
Using a dashed line in an Excel graph

Tip. Even more formatting options are available on the Chart Tools tabs (Design and Format) that activate when you select the chart or its element.

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.
Smoothing the angles of the line chart

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:
Fading out the gridlines in an Excel chart

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:
Tiny line charts (sparklines) to analyze trends

That's how you plot a line graph in Excel. I thank you for reading and hope to see you on our blog next week!

You may also be interested in:

5 Responses to "How to create a line graph in Excel"

  1. mukesh kumar says:

    hi madam,

    please describe how to apply VLOOKUP on multiple colume in same time.

  2. rakhshanda says:

    Hi,
    Please describe difference between line and scatter graph and which sort of data are used for these two graphs.

    • Hi Rakhshanda,

      A line graph is typically used to display continuous data over a certain time period; it's perfect for visualizing trends over time. In a line chart, the vertical axis is a value axis and the horizontal axis is a category axis. All data points (numeric data) are evenly distributed along the y-axis, connected in a continuous line. Category data (such as months, quarters, or years) are distributed along the horizontal axis with even intervals.

      A scatter graph is used to show the relationship between two variables. In a scatter plot, both axes plot numeric data. And because it has two value axes, a scatter graph displays each data point at the intersection of the horizontal and vertical axis, therefore it is sometimes called an xy graph.

      Generally, use a line graph when you have both numeric and non-numeric (category) data; and a scatter chart when you deal with two related sets of numbers.

  3. Temesgen Fita says:

    I am PhD candidate in Agricultural entomolgy and working on insects distribution and population dynamics in line with monthly meteorological data. The material is very important for me now. Because I am working on my research paper articles for publication. Please would you tell me or give an advice what type of chart to showl the graphs with the combination of lines and columns? I thank You very much in advance for your help?
    Sincerely!
    Temesgen Fita, Ethiopia

Post a comment



Unfortunately, due to the volume of comments received we cannot guarantee that we will be able to give you a timely response. When posting a question, please be very clear and concise. We thank you for understanding!