How to Make a Line Graph in Microsoft Excel
What Is a Line Graph?
As an accounting professional of 25 years, I’ve used line graphs many times to assist upper management in understanding complex and large data sets. A line graph or chart is a graph that displays a series of data points and connects these points by straight lines or smoothed lines.
Line charts have a variety of uses; they are often used to track the progress of data over a period of time, but they can also be used for break-even analysis, plotting complex mathematical functions, finding trends over time within data.
The most common charts will plot data points against a set time period, allowing the user to visualize the data trend and make informed decisions based on these trends. This article will outline the basic concepts of creating a line chart in Microsoft Excel.
How to Make a Line Graph on Excel
Making a line graph on Excel is very easy as long as you have a structured approach. Before starting, you have to decide what data points you will collect and what quantitative measure you will use to record the trend. The general flow that you should follow is:
- Collect and set up data.
- Create line graph in Excel.
- Format the line graph.
- Consider using bar graphs and line graphs together.
I will cover each of these topics in more detail below.
Setting up the Data
For the purposes of this article, I have decided to track a webpage’s traffic over a year to see if there are seasonal trends and to help me decide whether there are times when I need to focus on writing different articles. Over a year, I have collected the total number of hits my blog has accrued per month. Now that I have the data, I can begin creating my line graph.
The first thing I must do is transfer my data to a spreadsheet (I am using Microsoft Excel 2010.)
- Open a new blank worksheet.
- Create relevant headings (Month, Page views).
- I formatted the Month column as MMM-YY so it will show up with the same formatting in the graph. To do this, click on the top cell of data, hold down the left mouse button, and drag the outline down. Right click on the highlighted range and select format cells. On the Number tab, click on Date in the Category and find Mar-01–highlight this type and click OK.
- Enter data for the page views per month.
I’m not interested in formatting the table of data as it will not be used in any presentation.
Creating a Line Graph
Now that I have the data table setup, I can create the line graph.
- Highlight the data table–click on the top left cell (including the headings), click and hold the left mouse button, and drag the box to the last cell in the data table.
- Using the menus–click on Insert.
- You now see some submenus–as we are creating a line chart, click on the small down arrow underneath Line. This presents you with several different line charts. Hovering over each one gives you an explanation of the chart type. I selected the standard Line chart as this plots a trend over time. Clicking on the Line chart will create the graph.
How to Change the Format
You will notice that Excel already formats the line chart for you. However, I’d like to change the colors and formatting
Looking at the line graph on the spreadsheet, there is a border around the chart with some dots on the edges and corners. If you hold down the left mouse button and grab these dots, you can change the shape and size of the graph. (If the border is not there, simply click the graph.) When you select the graph by clicking on it, you’ll notice that there are new menu items in the Excel Ribbon.
- Design–used to change the style of the chart.
- Layout–used to change the details of the chart (headings, labels etc.).
- Format–allows you to change fonts and amend other formatting within the chart.
I’ll start by amending the design of the line graph.
- In the Chart Layouts section in the Design menu, I chose Layout 5.
- In the Chart Styles section in Design menu, I chose style 27.
Next, I’ll amend the layout.
There are a lot of things that can be changed in this menu; the best thing to do is experiment. Excel allows you to really delve into the layout of the chart. For the purposes of this article, I’ve decided to add vertical gridlines.
- In the Axes section in the Layout menu, I clicked the down arrow under Gridlines, clicked on Primary Vertical Gridlines, and chose Major Gridlines.
Finally, I’ll amend the format.
This is where you can polish the look of your line graph.
- In the shape styles section in the format menu, select Subtle Effect - Aqua Accent 5 to add a background color to the chart. You can use the Shape Fill, Shape Outline, and Shape Effects tool to further customize the chart.
- In the WordArt Styles section of the format, I used the Text Effects menu and chose Glow Aqua 4 pt. glow; there are other tools in this section that allow you to change the format of the text.
We can also change the text within the line graph.
When you changed the type of graph, you may have noticed that some of the headings disappeared or became generic. You can change the text within the graph simply by clicking on the text and typing the required heading. You can also add additional text and headings by using the Layout menu to go to the Labels section.
Using Bar and Line Graphs Together
As I finalized the line chart, I decided that I wanted to add a new element to the graph so that I could compare page views to revenue.
The first thing I have to do is add the new data to the spreadsheet.
- Click on the graph.
- The data table has a colored line and some square boxes at the corners. Holding down the left mouse button, grab the bottom right boxes and drag the outline over one column.
- Add the title Revenue and enter the relevant data for each month. You will note that the chart already updates with the new data set.
Amending the Chart for the New Data
The new data I have entered has a different unit than the original data. I would also prefer the revenue to be displayed as a bar graph. Therefore, I will add a secondary axis and amend the type of graph for this data set to a bar chart.
- Click on the Revenue line in the chart.
- In the Change Chart Type section of the Design Menu, I chose Column – Clustered Column.
- In the Design menu, I clicked Format Selection and clicked on Secondary Axis in the Series Options section.
Looking at the chart, I have now added the revenue data, converted the chart type to a bar graph, and added a secondary axis.
This article gives a brief demonstration of how to create a line chart in Excel. The graphing tools in the software are very intuitive and powerful. They allow you to compare data in many different ways. With experimentation, you will be able to create very professional graphs.