Widely used in various fields, a line chart is one of the staples of data visualization. Line graphs (charts) are a graphic representation of data shown on lines across the chart area.
In a line chart, lines typically represent categories, and are drawn between data points called markers. Line charts are commonly used for displaying trends over time with the time data shown on the X axis. In this article, we’re going to show you how to make a line graph in Excel for compelling reports.
Line Chart Basics
A line chart mainly has 5 sections:
- Plot Area: This the graphic representation area.
- Chart Title: The title of the chart. It’s always a good idea to give the chart a concise, but descriptive title.
- Vertical Axis: The axis representing the measured values, also known as the x-axis.
- Horizontal Axis: The axis that includes the categories of the data, also known as the y-axis. The data series can be grouped like shown in the sample chart above.
- Legend: The legend is an indicator that helps distinguish data series from one another.
The most common types of line charts are the following:
- Clustered: Each line representing the data series is clustered on the vertical axis. This type is best used for comparing each individual value separately.
- Stacked: Data points are drawn in a cumulative manner for each category, meaning that the top line represents the total of the data points below it. The stacked line charts are useful for highlighting changes between categories, or their contribution to the whole. However, it’s a bit more difficult to compare the relative placement of the data points to each other in this style.
- 100% Stacked: You can choose a 100% stacked line chart to see relative percentage of the data series. While similar to the Stacked type, the top line is set to 100% in 100% Stacked. This type is great for comparing the contribution of each component in a category. However, this style doesn’t show the actual values.
How to Make a Line Graph in Excel
Most Excel versions have line chart support and an extensive set of options to customize them. To create a new line chart, begin by selecting your data in your workbook. Excel will automatically detect data labels when you Include them in your selection.
Next, go to the INSERT tab in the Ribbon, and click on the Line Chart icon to see the line chart types. Click on the desired chart to select it. In this example, we will be using a Clustered Line.
Clicking the icon will insert the default version of the selected chart. Now, let’s take a look at Excel’s rich customization options.
Customizing a Line Chart
In Excel, you can edit almost any element in a line chart. Let’s take a look at some of the commonly configurations.
Double-clicking on any item will pop up the side panel which contains customization options for the selected element. This panel contains element specific options, as well as generic ones like coloring and effects.
Remember that any changes you make on the side panel will apply to the selected element, and you don’t need to click the element again to make further changes.
Right-Click (Context) Menu
Right-clicking an element displays context menu, where you can modify basic styling properties like colors, delete items, or open the side panel for more options. To display the side panel, select the option that begins with Format. For example, Format Data Series… in the following image will take you to the customization page for editing the data series.
Chart Shortcut (Plus Button)
Beginning with Excel 2013, shortcut buttons have been introduced to charts. Here you can easily add/remove elements, apply predefined styles, color sets, and filter values.
With this method, you can see the effects of selected options on the fly, before actually applying them. For example, in the following image the mouse is over the Data Labels item, and we can see the labels on our chart.
Ribbon (Chart Tools)
Whenever you activate a ‘special object’, Excel will add a new tab (or tabs) to the Ribbon. You can see these chart specific tabs in a new bar named CHART TOOLS. This consists of two sections: DESIGN and FORMAT. The DESIGN tab contains options for adding elements, applying styles, modifying data and the chart itself. On the other hand, the FORMAT tab provides generic options that are common in other chart objects. Essentially, the CHART TOOLS in the Ribbon is where you can find all options we mentioned before in one place.
Preset Layouts and Styles
Instead of customizing each element separately, you can try the preset layouts and styles to further improve the look-and-feel of your chart.
You can find styling options in the DESIGN tab under CHART TOOLS, or from the brush icon of Chart Shortcuts. Here are some examples:
Applying a Quick Layout:
Updating the Chart Style:
Changing the chart type
You can change the type of your chart at any time from the Change Chart Type dialog. In this guide, we’re going to focus on the Line chart variations.
To change the type of your chart, click on Change Chart Type in the Right-Click (Context) Menu, or the DESIGN tab.
In the Change Chart Type dialog, you will see the options for all chart types along with a preview of your chart. Here, you can find Stacked and 100% Stacked types with marker options, as well as their 3D counterparts.
By default, Excel considers vertical labels to be categories, and horizontal labels data series. If your data is transposed, click the Switch Row/Column button in the DESIGN tab when your chart is selected.
Move a chart to another worksheet
By default, charts are created in the same worksheet as the selected data. If you need to move your chart into a new or existing sheet, open the Move Chart dialog by clicking its icon in the DESIGN tab, or from the right-click menu for the chart itself. Please keep in mind that you need to right-click an empty place in the chart area to see this option.
Move Chart dialog will give you 2 options:
- New sheet: Select this option and enter a name to create a new sheet and move your chart there.
- Object in: Select this option and select the name of an existing sheet from the dropdown input to move your chart there.