What is Line Chart in Excel?
A line chart in Excel is a graphical representation where a straight line connects a series of data points. A line chart captures trends, such as an asset’s price history, recognizable patterns, etc. It also shows how information changes with time. Here, the data is shown in a continuous line.
Typically, the horizontal axis may represent the changes over time. For example, a salesman wants to track the sales trend of mobile phone sales over one month since he opened his store. Given below is the data in the table. To plot a line chart, go to the “Insert” menu, and in the “Charts” Tab, select the “Line” charts symbol. Next, choose a required line chart; here, we choose the “Line with Markers” option. Thus, we can get an idea of the sales trend over the four weeks by observing the line chart.
Table of contents
Key Takeaways
- A line chart in Excel is a graphical representation of different data points in a continuous line. It is used to show the changes in the value over time.
- There are various line charts, like line charts with markers, stacked line charts, 100% stacked line charts, etc., used for plotting different types of data or categories.
- You can also plot line charts combined with other chart types like bar and column charts using the “Insert Combo Chart” option in the Charts group.
Types of Line Chart in Excel
The various types of line charts in Excel are:
- 2D Line chart
- Stacked Line Chart
- 100% Stacked Line Chart
- Line with Markers
- Stacked Line with Markers
- 100% Stacked Line with Markers
- 3D Line Chart
Let us look at a brief description of each.
2D Line chart – This simple chart shows trends over time and is used when there are many data points.
Stacked Line Chart – It shows how parts of a whole change with time. It is slightly hard to read compared to other types of charts.
100% Stacked Line Chart – This chart shows the change in percentage contributed by each value over time. A top line of 100% is present in the chart.
Line with Markers – It is like the line chart and shows trends over time but it also marks the data points.
Stacked Line with Markers – Similar to the stacked line chart, it shows how parts of a whole change with time, but with the data points marked.
100% Stacked Line with Markers – This also shows the change contributed by each value to the percentage over time. Additionally, the data points are marked, making them easier to understand.
3-D Line chart – This chart is used to show the trends over time and in the 3-dimensional form; hence, some lines are shown in front of the others.
Excel VBA – All in One Courses Bundle (35+ Hours of Video Tutorials)
If you want to learn Excel and VBA professionally, then Excel VBA All in One Courses Bundle (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.
How to Add/Create Line Chart in Excel?
Creating a line chart in Excel is simple. It provides clarity on information such as an asset’s price history, the performance of your business, etc., by presenting information as data points connected by straight-lines. But first, let us look at a simple example to plot a line graph.
Here, we have the details of the revenue of a business for FY 2018-19 and FY 2019-20 in a table. Let us plot a graph to compare the two.
Step 1: To plot the graph, select the data in the table from cells A2 to C6.
- Go to the “Insert” menu.
- In the “Charts” Tab, click on the “Insert Line or Area Chart” symbol. From here, we can select a required line chart. Here, we choose the 3-D line chart.
Step 2: We get the chart below where the revenues of FY 18-19 and 19-20 for different products are represented in a line.
To display various elements like the axis labels, trend line chart in Excel, etc., you can click on the + sign in the top-right corner of the chart.
Thus, creating a line chart in Excel is simple and easy to format according to your requirement.
Examples
As seen above, line charts typically depict the changes in the information with time. Let us look at a few examples where line charts have proved to be handy in predicting trends.
Example #1
Let us look at an example of how to plot the line chart to predict the growth of the supermarket’s performance. Below is the revenue of the different branches of a supermarket, calculated from January to April. But first, we must plot a line graph to evaluate the performance of their units.
Step 1: To insert line chart in Excel, select the cells from A2 to E6. Go to the Insert tab, and choose the “Insert Line or Area Chart” option in the Charts group.
- Choose any line chart; here, we choose the “Line with Markers” option.
- You get a graph as shown below.
Step 2: The maximum and minimum values in the table are $1600 and $780, respectively. Hence, we can change the y-axis minimum and maximum values as $700 to $1800 for better clarity.
- Select the right axis by placing the cursor on it and right-click.
- Choose the option “Format Axis.”
Step 3: You get the Format Axis pane. Click on the “Axis Options” button. You get the Minimum and Maximum bounds. Change their values to 700 and 1800, respectively.
Step 4: Thus, you can observe that the y-axis maximum and minimum values have changed. From the graph, you can check out the performance of the four branches. The Eastern branch needs concentration as its performance is dipping.
Such observations can be easily made with the line chart which would otherwise be missed, especially when you have a large amount of data.
Example #2
Let us check the use of line chart in Excel to plot the temperatures in Johannesburg. The table below shows the average maximum and minimum temperatures from January to December.
Step 1: Let us plot a line graph with markers to check the change in maximum and minimum temperatures throughout the year.
- Now, go to the Insert tab and choose the “Insert Line or Bar Chart” image in the Charts group.
- Click on the drop-down arrow and choose the “Line with Markers” option.
Step 2: You get the line chart representing the change in maximum and minimum temperatures over time.
Now, to change the features of the chart, like the colors of the line, its appearance, etc., right-click by placing the mouse on the line. Then, choose “Format Data Series” from the menu.
From the option available on the right side of the sheet, you can change the appearance as per requirement. Here, we are changing the color of the minimum temperature line.
Step 3: You can also make changes to the map. For instance, you can click on the “Chart Elements” option at the top left corner to add the data labels.
Thus, you get a line chart with markers that tracks the changes in temperature over one year.
Example #3
Let us consider an example where we must plot the line chart to observe the trend in scores of 3 students over four semesters. Here, the time is plotted along the x-axis in units of semesters, and the y-axis shows their scores.
Step 1: To plot a line chart, select the elements in the table from A2 to D6.
- Now, choose the option “Insert Line or Area chart” in the Charts group of the Insert tab.
- From here, you can select the 2-D Line chart option.
- You get a chart as shown below.
Step 2: You can make changes to your chart as required. Here, the y-axis begins from zero, and hence the score of the students is hard to read as the axis needs to be more precise.
We observe that the minimum value for the y-axis measurements is 65; that is, the minimum mark scored by any student is 65. However, our axis begins at zero. To change the minimum value of the y-axis, place the cursor over the axis and right-click on it.
Step 3: Choose the option “Format Axis.” The pane appears on the right side. Under “Axis Options,” set the Minimum value in Bounds as 40.
Step 4: You get the graph with the axis updated as shown.
Important Things to Note
- Line charts are most appropriate for dates, text labels, or numeric values along the x-axis.
- Stacked Line charts are built with two or more sets of data.
- Line charts can be used to track changes over short or long periods of time.
- You can format the axis, data point, data series, etc., with the appropriate options like “Format Axis,” “Format Data Point,” and “Format Data Series,” choice, which is available when you right-click after placing the mouse at the appropriate point.
Frequently Asked Questions (FAQs)
The stacked line chart represents the sum of all previous series as a cumulative value, not just its value. It requires more than one data set, and each set is added to the rest.
We can combine bar and line chart in Excel by adding a secondary axis. Go to the charts group and click the Insert Combo Chart option. From here, select Create Custom Combo Chart.
Sometimes, we get two lines in the chart when we have two series. You must convert the years into text format by adding an apostrophe before the year. For e.g., ‘2012. Also, if there is a blank cell in the data values, you get a gap in the line graph.
If you notice gaps in your line chart, right-click on the chart and then click on Select Data. A dialog box will pop up. Select Hidden and Empty Cells and choose the “Connect data points with line” option.
Download Template
This article must be helpful to understand the Line Chart in Excel, with its formula and examples. You can download the template here to use it instantly.
Recommended Articles
Guide to Line Chart in Excel. Here we learn the types of line chart and how to create them with examples and downloadable excel template. You may learn more from the following articles –
Leave a Reply