How to Make a Line Graph in Excel: Explained Step-by-Step
A Line Graph is by far one of the simplest graphs in Excel. It helps represent statistical data trends plainly.
Its ease of use makes it the top choice for the visual representation of small datasets. And it is usually used to display trends over a period of time.
In this guide, I’ll show you exactly how to use the line graph, including how to format it and what to use it for.
Let’s dive right into the article🎯
Also, you can download our sample workbook here to tag along with the guide.
Table of Contents
How to make a line graph in Excel
Making a line graph in Excel is more of a fun job. All you need to do is have a dataset, format it properly, and select the line chart you want to make out of it.
Say we have the following data set that portrays the increase in prices of LEDs during the last seven years. And we want to make a line graph out of it.
Please note that you need at least two columns to create a line graph. Each of these will be plotted on an axis (X and Y axes).
We will now use a basic line graph to represent this data. For that:
- Select data in both columns.
- Go to Insert Tab.
- Click Recommended Charts on the Charts group.
- An Insert Chart dialog box will appear.
- Select the chart type you want to use. For now, we will use a single-line graph.
The chart appears on the screen with all the data plotted as follows:
Once you have your line graph plotted, you can easily adjust the chart area, chart styles, and chart elements for it 💰
Formatting options for your line graph
This has to be the most fun and interesting part of creating a line graph.
Excel offers a huge variety of formatting options for graphs. You can adjust the chart to your preferences in seconds only using the Format Data Series pane.
Let’s explore some of these formatting options below.
Adding the chart title & axis title
At the top of the graph, you see the heading “Chart Title”.
To add your own Chart Title, simply click on it and a text box will enclose this heading.
In the text box, add any title you think fits the chart.
For example, we have set the chart title as follows:
Now let’s add the Axis title to the same graph. But first, we need to enable the axis titles.
For that, click on the plus icon at the top right of the chart. A drop-down list will appear.
Select the Axis Titles, and a rectangular box will appear on the left and bottom of the chart. The box on the left side is the text box for the Y-axis. And the box on the bottom is the text box for the X-axis.
Add in the title for the X-axis and Y-axis. And the result looks like this:
And there it is🥂
Data labels and markers
You can also select other options in the drop-down list to add elements to your chart. Like inserting Data Labels, Error Bars, Trendlines, and others.
To add data labels to your chart, simply click on the plus sign icon to the right of the chart. And checkmark the option for data labels.
Adding Data Labels looks like this:
See those data labels – $200, $450, $600, and so on alongside the trendline.
Similarly, you can add Markers to your graph from the Format Data Series task pane.
Double-click the trend line on your chart, and the pane will appear on the right side.
Select Series from the drop-down menu.
Go to Marker options > Built-in > Type. Select a shape for the markers, and it will appear on each intersection point on the trendline.
Changing the style, color, and overall look of the chart makes it look twice more appealing. And doing this is not difficult.
Select the chart, and the Chart Design tab will appear at the top of the ribbon.
Clicking the tab will open a plethora of style choices for you. Simply select the one you like.
Hover over the desired style to see a preview of the final look. We chose Style 7, and this is how it turned out 🤩
Similarly, you can change the type of the chart using the Insert Line or Area Chart.
It offers options like the Stacked line graph, Scatter Chart, 100% Stacked line chart, and others. Users mostly use the default line chart to represent their data.
Formatting axis options
The Axis options make the most important part of the Data Series task pane. It lets you change the min and max numeric values for the axis, the line width, and much more💪
For now, let’s see how to change the Bounds and Units for a chart in Excel. For that:
- Go to the Format Data Series Task pane > Axis Options.
- Select the Vertical (value) axis from the drop-down list.
This will show you the present value set for the Bounds and Units.
Say, this is our default setting.
Bounds are set to a Minimum of 0 and a Maximum of 2500. This means that your Y-axis will range from 0 to 2500. Take a look below.
Note that the gap between each axis point is 500. The first point on the Y-axis shows 0, then comes 500, then comes 1000, and so on.
This is because the Units are set to a major of 500.
You can change the Bounds and Units for your chart through the following steps.
- Under the Axis options panel, set the desired values for Bounds and Units.
- Press Enter.
Altering the Bounds value will change the minimum and maximum values on the graph. And Units value will change the gap between the two axis points.
For example, if we were to change the:
- the minimum Bounds to 200 from 0.0;
- and the major Units to 800 from 500:
The Graph will change as follows:
Note how the starting axis point is now 200 and the gap between all axis points is now 800.
The Data Series task pane has a lot more to offer. Like you can change the type of absolute values, change the alignment of the graph, add effects, and do so much more about your graph in Excel 🤩
Create a line graph with multiple lines
We’ve seen how to create a single-line graph above. Now let’s make a multiple-line graph which is as easy as the one created before.
The only difference is that in multiple line graphs, you need multiple data sets, as in the image below.
The dataset above contains data points for the retail price and purchase cost over different years.
How about we make a line graph out of it? And as we have two data columns here (retail price and purchase cost), it would be a multiple-line graph.
To create a multiple-line graph, select the data. And then go to Insert > Charts group > Line chart icon 📊
If you want different graph styles like 2D or 3D graphs, get them from the Line or Area chart option here.
We are going with a simple 2D line graph for now. And the final multiple-line graph looks like this:
Looking at the graph, we can readily compare the trend in retail price and purchase cost.
Very evidently, the retail price has gone up and the purchase cost has declined during the years 2018 to 2021 📈
When to use a line graph
A line graph is very effective in representing small sets of periodic data.
It is ideal to use a line graph when:
- You want to display data from multiple sets at one time.
- You want to show the increase or decrease of a trend over time.
- You want users to decipher the data (or a trend) in a simple, easy, and fast manner.
🚩 BUT (and this is a big but!)
There are only so many instances where you cannot and must not use a line graph. A few cases are such that line graphs simply can’t handle the data. Let’s see these below.
- Line graphs are not good at visualizing the proportions of a whole. E.g. how the answers from a survey is divided between age groups.
- Line graphs are not very efficient in representing large data sets that go over multiple rows. With increasing data sets, the readability of your line chart will be compromised.
- You can also not use line charts to represent percentages. These are better suited to dealing with whole numbers.
- Line charts do not display discrete data very efficiently. It is best used for continuous data.
That’s it – Now what?
That’s all about creating a line graph and formatting it in Excel.
Line charts are truly one of the simplest chart tools of Excel. Easy to make and easy to visualize. But it doesn’t end there🚀
This giant spreadsheet software has so much more to offer. To learn more about Excel, start from the basic functions like the VLOOKUP, IF, and SUMIF functions of Excel.
To learn these functions and much more about Excel, enroll in my 30-day free email course now!
Who said line graph is the only graph/chart type of Excel? To your surprise, Excel offers a variety of charts and graphs.
Check them out here: Gantt Chart, Pareto Chart, Histogram Chart, Pie Chart, and more.