How to Make a Line Graph in Microsoft Excel

Updated on September 8, 2017
This line graph comparing page views to revenue was created using Excel and took about 15 minutes to create.
This line graph comparing page views to revenue was created using Excel and took about 15 minutes to create. | Source

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.
Setting up the data. | Source

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.

Choosing the type of line graph.
Choosing the type of line graph. | Source

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.

Changing the design of the chart in Excel.
Changing the design of the chart in Excel. | Source

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.

Add additional data and format to a bar chart in Excel
Add additional data and format to a bar chart in Excel | Source

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.

Changing the layout of the chart in Excel
Changing the layout of the chart in Excel | Source

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.

Changing the format (background color) of the chart.
Changing the format (background color) of the chart. | Source

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.

Changing the format (text format) of the chart in Excel.
Changing the format (text format) of the chart in Excel. | Source

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.

Adding a secondary axis to the chart.
Adding a secondary axis to the chart. | Source

Final Thoughts

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.

Questions & Answers

    Comments

      0 of 8192 characters used
      Post Comment

      • profile image

        Daniel Clark 

        5 years ago

        Thank you, a very helpful article!

      • Brainy Bunny profile image

        Brainy Bunny 

        6 years ago from Lehigh Valley, Pennsylvania

        Thanks for the useful overview. I love Excel, and am always looking for ways to improve my skills.

      working

      This website uses cookies

      As a user in the EEA, your approval is needed on a few things. To provide a better website experience, turbofuture.com uses cookies (and other similar technologies) and may collect, process, and share personal data. Please choose which areas of our service you consent to our doing so.

      For more information on managing or withdrawing consents and how we handle data, visit our Privacy Policy at: https://turbofuture.com/privacy-policy#gdpr

      Show Details
      Necessary
      HubPages Device IDThis is used to identify particular browsers or devices when the access the service, and is used for security reasons.
      LoginThis is necessary to sign in to the HubPages Service.
      Google RecaptchaThis is used to prevent bots and spam. (Privacy Policy)
      AkismetThis is used to detect comment spam. (Privacy Policy)
      HubPages Google AnalyticsThis is used to provide data on traffic to our website, all personally identifyable data is anonymized. (Privacy Policy)
      HubPages Traffic PixelThis is used to collect data on traffic to articles and other pages on our site. Unless you are signed in to a HubPages account, all personally identifiable information is anonymized.
      Amazon Web ServicesThis is a cloud services platform that we used to host our service. (Privacy Policy)
      CloudflareThis is a cloud CDN service that we use to efficiently deliver files required for our service to operate such as javascript, cascading style sheets, images, and videos. (Privacy Policy)
      Google Hosted LibrariesJavascript software libraries such as jQuery are loaded at endpoints on the googleapis.com or gstatic.com domains, for performance and efficiency reasons. (Privacy Policy)
      Features
      Google Custom SearchThis is feature allows you to search the site. (Privacy Policy)
      Google MapsSome articles have Google Maps embedded in them. (Privacy Policy)
      Google ChartsThis is used to display charts and graphs on articles and the author center. (Privacy Policy)
      Google AdSense Host APIThis service allows you to sign up for or associate a Google AdSense account with HubPages, so that you can earn money from ads on your articles. No data is shared unless you engage with this feature. (Privacy Policy)
      Google YouTubeSome articles have YouTube videos embedded in them. (Privacy Policy)
      VimeoSome articles have Vimeo videos embedded in them. (Privacy Policy)
      PaypalThis is used for a registered author who enrolls in the HubPages Earnings program and requests to be paid via PayPal. No data is shared with Paypal unless you engage with this feature. (Privacy Policy)
      Facebook LoginYou can use this to streamline signing up for, or signing in to your Hubpages account. No data is shared with Facebook unless you engage with this feature. (Privacy Policy)
      MavenThis supports the Maven widget and search functionality. (Privacy Policy)
      Marketing
      Google AdSenseThis is an ad network. (Privacy Policy)
      Google DoubleClickGoogle provides ad serving technology and runs an ad network. (Privacy Policy)
      Index ExchangeThis is an ad network. (Privacy Policy)
      SovrnThis is an ad network. (Privacy Policy)
      Facebook AdsThis is an ad network. (Privacy Policy)
      Amazon Unified Ad MarketplaceThis is an ad network. (Privacy Policy)
      AppNexusThis is an ad network. (Privacy Policy)
      OpenxThis is an ad network. (Privacy Policy)
      Rubicon ProjectThis is an ad network. (Privacy Policy)
      TripleLiftThis is an ad network. (Privacy Policy)
      Say MediaWe partner with Say Media to deliver ad campaigns on our sites. (Privacy Policy)
      Remarketing PixelsWe may use remarketing pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to advertise the HubPages Service to people that have visited our sites.
      Conversion Tracking PixelsWe may use conversion tracking pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to identify when an advertisement has successfully resulted in the desired action, such as signing up for the HubPages Service or publishing an article on the HubPages Service.
      Statistics
      Author Google AnalyticsThis is used to provide traffic data and reports to the authors of articles on the HubPages Service. (Privacy Policy)
      ComscoreComScore is a media measurement and analytics company providing marketing data and analytics to enterprises, media and advertising agencies, and publishers. Non-consent will result in ComScore only processing obfuscated personal data. (Privacy Policy)
      Amazon Tracking PixelSome articles display amazon products as part of the Amazon Affiliate program, this pixel provides traffic statistics for those products (Privacy Policy)