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 line chart is a graph that displays a series of data points and connects these points by straight lines or smoothed lines.
The uses of line charts are many; often they are used to track progress of data over a series of time, but they can be used for breakeven analysis, plotting complex mathematical functions and be used to find trends over time within data.
The most common line graph will plot data points against a time series 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.
A very through book detailing everything you can do with graphs and charts on 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 line graph 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 line graph data
For 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 to create my Excel 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. For the month I entered the first day of the month to be consistent.
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; creating a graph in Microsoft Excel is very easy as long as you have the data table setup correctly:
- 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 line graph format
You will notice that Microsoft Excel already formats the line chart for you, adds labels and creates a fairly good overall format. However I’d like to change the colors and formatting:
The method above gives you a pretty decent chart already, but I want to format the chart by changing colors etc.:
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 there are new menu items in the Microsoft 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 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 of the line graph.
There are a lot of things that can be changed in this menu; the best thing to do is experiment. Microsoft 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 of the line graph.
This is where you can polish the look of your line graph:
- In the shape styles section in the format menu 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.
Changing the text within the line graph
When you changed the type of graph you will note 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 - labels section.
Using bar graphs 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 to 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; also I would 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 only 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 now have added the Revenue data, converted the chart type to a bar graph and added a secondary axis.
Final thoughts about creating line graphs in Microsoft Excel
This article gives a brief demonstration of how to create a line chart in Excel. The graphing tools within Excel are very intuitive and powerful and allow you to compare data in many different ways. With experimentation you will be able to create very professional graphs.