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 simple words, a Line chart is used to show changes over time to time. The line graph is one of the simplest and the easiest graphs you can make in Excel.
Line graphs can show a single line for one data set, or multiple lines to compare two or more sets of data. It is a data visualization tool for marketing, finance, laboratory research, forecasting, and other areas. This article explains how to make a line graph in Excel for compelling presentations or reports.
How to Make and Format a Line Graph in Excel
A line chart is a graph that displays a series of data points connected by a straight line. It is one of the most commonly used charts to visualize data and display trends.
A line graph mainly consists of the following components:
- Chart Title – The title of the chart
- Plot Area – The area on which the data is plotted.
- X-axis (Horizontal Axis) – The axis that includes the categories of the data and generally shows time periods.
- Y-axis (Vertical Axis) – The axis represents the value data and shows the data you are tracking.
- Legend – This section provides information to help distinguish data series from one another
Step-by-Step Guide to Build a Line Chart in Excel
Microsoft Excel provides line chart support and an extensive set of options to customize them.
Setup Your Data for Line Graph
To create a new line chart, the first step is to enter data into Excel. Since the line chart has two axes, your table should contain at least two columns: The first column will be the time intervals (hour, day, month, etc.) and the second column will 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 3 or more columns of data: time intervals in the left column and observations (value data) in the right columns, each data series will be plotted 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 properly formatted, you can create your line chart. First, select the data you want in the graph (A2:D12) as shown:
Next, go to the ‘Insert’ tab in the Ribbon, and click on the ‘Line Chart’ icon to see the line chart types. As you hover your mouse pointer over chart types, Excel will show you a description of that chart type as well as its preview below. Click on the desired chart to insert it. In this example, we will be using a simple ‘2D Line’ chart.
When you click that, your line graph will appear.
Excel Line Chart Types
Microsoft Excel offers the following types of the line graph:
Line – The basic 2-D line chart is shown above. If there is more than one column (data series) in your data set, each is plotted individually.
Stacked Line – The lines in this graph are drawn in a cumulative manner, meaning that each additional data point is added to the first, so the top line is the total of the ones below it. Therefore, the lines will never cross.
100% Stacked Line – This chart is similar to a stacked line chart, but the Y-axis shows percentages rather than absolute values. The top line will always represent a total of 100% and appear straight across the top of the graph. 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 line graph adds indicators at each data point.
3-D Line – It is similar to the basic line graph, but represented in a three-dimensional format.
Customizing a Line Chart/Graph
In Excel, you can customize almost any element in a line chart. 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 changed or formatted as per the user requirement for a better understanding of the graph.
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 pop up the side panel 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 options in the ‘Design’ tab under Chart Tools or from the ‘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 select ‘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
You can change the type of your chart at any time. To change the type of your chart, right on the chart and select ‘Change Chart Type’ in the context menu, or go 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 along with a preview of your chart. 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 (data point), or you changed 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.
You can also use this dialog box to 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 shows the general 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 (+) 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, go to the ‘Fill & Line’ tab, click the ‘Marker’ button and expand ‘Marker Options’. Under the Marker Options section, select the ‘Built-in’ radio button, 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.