How to Make a Line Graph in Excel
This article explains how to make and format a line graph in Excel to show trends or track data across multiple time periods.
A line graph (also called a line chart ) is a graphic representation of trends in data over time. In other words, a Line chart is used to track changes over periods of time (over months, days, years, etc.). The line graph is very simple and easy to make in Excel. It is one of the most commonly used graphs in excel.
As its name implies, the line graph uses lines to represent data in a chart, a line for each data set. It is a data visualization tool for marketing, finance, laboratory research, forecasting, and other areas. This tutorial explains how to make a line graph in Excel with examples.
How to Make and Format a Line Graph in Excel
A line chart displays a series of data points connected by straight lines on two axes. It is one of the most commonly used charts to visualize data and display trends.
A line graph usually consists of the following components:
- Chart Title – The title of the chart
- Plot Area – The area where the data in your chart is plotted.
- The X-axis (Horizontal Axis) – The axis that includes the categories of the data and generally shows time periods.
- The Y-axis (Vertical Axis) – The axis represents the value data and shows the data you are tracking.
- Legend – The legend of a graph helps readers understand what each data series means.
Step-by-Step Guide to Build a Line Chart in Excel
Microsoft Excel provides line chart support and an advanced set of options to customize charts.
Setup Your Data for Line Graph
To create a new line chart, the first step is to enter data into Excel and then format it. Since the line chart has two axes, your table should contain at least two columns: The first column should be the time intervals (hour, day, month, years, etc.) and the second column should be the dependent values (prices, population, etc.). This called a single-line graph because it has only one dependent values range (a single line will be shown in the graph).
However, if your dataset (table) contains three or more columns of data: time intervals in the left column and value data in the right columns, each data series will be drawn individually (one line for each column). This is called a multiple-line graph.
For example, we have this wildlife population data in Blue River forest here:
The above example data set has three columns, so we are going to do a multi-line graph.
Insert a Line Graph
Once your data is entered into the worksheet, you can create your line chart. First, select the data you want in the graph (A2:D12) as shown:
Then, go to the ‘Insert’ tab in the Ribbon, and click on the ‘Line Chart’ icon to see the line chart types. When you hover your mouse pointer over chart types, Excel will show you a short description of that chart type and its preview. Click on the desired chart to insert it. In the example below, we will be using a simple ‘2D Line’ chart.
When you click that, your line graph will appear.
Excel Line Chart Types
Excel offers different types of the line charts:
Line – The basic 2-D line chart is shown above. If there is more than one column of value in your table, each is plotted individually.
Stacked Line – This line chart type is used to show how data changes over time and it usually requires more than one column of data. Each additional set is added to the first, so the top line of the chart is the combination of the lines below it. As a result, none of the line cross each other.
100% Stacked Line – This chart is just like the stacked line chart, but the only difference is that this Y-axis shows percentages rather than absolute values. The top line is the 100% line and runs across the top of the chart. This type is typically used to compare the contribution of each component to the whole over time.
Line with Markers – The marked version of the 2-D line graph will contain pointers at each data point.
3-D Line – It is similar to the 2-D line chart, but represented in a three-dimensional format.
Customizing a Line Chart/Graph
In Excel, you can customize almost every element in a line graph. Let’s take a look at some of the customization you can do to make it look better.
Add Title to Line Chart in Excel
The Chart title can be edited and formatted as per the user requirement.
When you insert a line chart, its default title is ‘Chart Title’. To change the title, click once on the default chart title to select it and click a second time to edit it. Delete the default text and type your title.
To change the appearance of the title, right-click on it, then click ‘Format Chart Title’ or double-click on the title.
Double-clicking on any element on the chart will bring the side pane which contains customization options for the selected element.
Change Chart Layouts, Styles, and Colors
You can change the layouts, styles, and colors of the chart using Excel’s in-built preset layouts and styles to further improve your chart. You can find these designing options under the ‘Design’ tab from the Chart Tools group or from the floating ‘brush icon’ on the right side of your chart.
On the ‘Design’ tab, click ‘Quick Layout’ to apply your desired layout.
To change your chart style, choose any chart styles option under the ‘Design’ tab.
Changing Colors of the Chart Lines
To change the default color of chart lines, click the ‘Change Colors’ icon in the Chart Styles group under the ‘Design’ tab and choose a choose any color combination and your line chart will get a new look.
You can change the color for each line in the graph individually by right-clicking on the line and selecting ‘Format Data Series’ from the context menu to open the ‘Format Data Series’ pane on the right side. Alternatively, you can double-click to open the Format Data Series pane. Then, switch to the ‘Fill & Line’ tab (paint can icon), click on the ‘Color’ dropbox, and choose a new color from the color palette.
Change Chart Type
If you don’t like your chart type, you can change it at any time. To change the type of your chart, right on the chart and select ‘Change Chart Type’ in the context menu, or switch to the ‘Design’ tab and click the ‘See All Chart’ icon.
In the ‘Change Chart Type dialog’, you will see the templates for all chart types as well as a preview of how your chart will look like. Choose any template to change your graph type.
Change the Data Range in Line Chart
In case you didn’t select all the data you want, or you added a new column, or you made changes to your data in the source table, you can always adjust the data included in the chart.
Select the line chart and on the ‘Design’ tab, in the Data group, click ‘Select Data’ option.
A Select Data Source dialog box will appear. Here if you want to remove a data point uncheck them. If you want to change the data for a data point, select that label and click ‘Edit’ to change the range. If you want to add a new data point (line) to your chart, click ‘Add’ to add a data range. Then, click ‘OK’ to close the dialog box.
In the Select Data Source dialog, you can also switch the rows and columns of your graph.
Add a Trendline to Your Line Chart
A trendline is a straight or curved line in a chart that summarizes a pattern or prevailing direction of the data.
To add a trendline to your line chart, select the line for which you want to add a trendline and click the plus (+) floating button on the right side of the chart. From the list of chart elements, click the arrow next to ‘Trendline’ and choose your trend type.
Or click the ‘More Options’ to open the Format Trendline pane. Here, you have more trendline options, choose a Trend/Regression type. We’re choosing ‘Linear’ in this example. You can also further format your trendline here.
Add Data Markers in a line graph
Data markers are used to draw attention to the data points along a line. If your line graph does not include them, you can easily add them.
First, double-click on the line to which you want to add data markers and open the Format Data Series pane, or right-click on the line and select ‘Format Data Series’ to do the same.
In the Format Data Series pane, switch to the ‘Fill & Line’ tab, click the ‘Marker’ button and expand ‘Marker Options’. Under the Marker Options section, select the ‘Built-in’ option, and choose the desired marker type in the ‘Type’ list. In the ‘Size’ list, you can adjust the size of the markers. Here, you can also make other customizations to the markers as desired.
Move the Line Chart
If you need to relocate the line graph to a new or existing worksheet, right-click on the graph, click ‘Move Chart’.
In the Move Chart dialog, choose an existing worksheet from the ‘Object in’ dropdown or create a new one.