How to Graph and Label Time Series Data in Excel

Updated on October 28, 2016

High quality graphs can be achieved quite easily using Excel once you have a solid foundation of the basics. This tutorial walks you through the basics of how to graph a time series using excel. Everything from downloading a data set from the internet to editing the style of your graph is covered in this "how to".

Note: I walk through using Excel 2007 but the methods are very similar for Excel 2010.

Step 1: Create or Download Your Data

You can create your own time series or download one from the internet. For this tutorial, I'm going to graph Real GDP over the past 20 years. To get the data I go to the Bureau of Economic Analysis. You'll see you have a couple options ranging from when the data starts to how often you want the data (annual or quarterly GDP). Im going to go ahead and select the quarterly data going back to 1950 and then press the "update" button.

near the top of the page
near the top of the page

At the bottom of the page, click the "download dates shown" button to download the time series you've selected.

Step 2: Clean Your Data

Note: This step is only to make using our data easier. If you want to keep all of the data, that is totally fine.

Once you've opened the file you've downloaded, the next step is to clean your data by removing the data you don't want. This means we have to highlight and delete all of the rows we don't want. I click on the row number 10 and drag my cursor down until row number 27. Now I right-click and select "delete" from the right-click menu. I could clean my data further, but for now I'm happy. When I say row number, I mean the far left number, not the data in the excel.


row 10 to row 27 selected
row 10 to row 27 selected

In fact, why don't we delete that pesky "line column".

Select the problem cells and right-click. Now, select "delete". When given the option, select "shift cells left".





Step 3: Select the data and Insert a Graph

Click on the the row number of the data you want to plot on your time series graph. I click on the row number for GDP, then press and hold "ctrl." With "ctrl" pressed down I can also highlight other rows of data. While holding down "ctrl" I now press on the row number for Government Consumption Expenditures. Also, click on the row number for the row with your time/date data.

Once you've highlighted the rows you want, let go of "ctrl" and go to the top menu bar. Click on the "Insert" tab.

Click on the "Line" button.

Choose the top left icon.

When the small menu of graphs pops up, I usually choose the simple line graph (that's top left corner that is highlighted). You can experiment with other types of line graphs.
When the small menu of graphs pops up, I usually choose the simple line graph (that's top left corner that is highlighted). You can experiment with other types of line graphs.

At this point you should get a simple graph that looks like the one below.

Note: This is is the default graph spewed out by excel. In my opinion, its pretty ugly. Note that the colored labels are on the right and scrunch up the graph, neither axis is labeled, and the year/quarters seem to be jumping around at random on the x-axis.

Let's see if we can make it look a little bit cleaner and easier to understand.

an ugly graph
an ugly graph

Step 4: Label that Sucker

a) Add a Title- Go to the "Layout" tab on the top menu bar. Click the "Chart Title" button.

b) Add Axis Labels- Go to the "Layout" tab on the top menu bar. Click the "Axis Title" button.

The screenshot below shows how the menu looks when your adding labels.

c) Relabel- As awesome as Chart Title is as the name of your graph, you probably want to change that. Click on the "Chart Title" at the top of your graph. Wait a second. Click it again. You should now be able to edit text. Repeat for both axis.

d) Add Series Labels- That legend on the right of the graph is just unsightly. Lets go to the "Insert" tab and click on the "text box" button. Click and drag on your graph to draw a box you can type in. Type "Real GDP" into the box and adjust the font color to match the "Gross Domestic Product" line. Do the same for the government spending.

Now you can delete that legend on the right and your graph has more space to breath. It should look something like the graph below:


Note: While your at it, you can change the font, size, and style of your titles when your relabeling. To do this, click on the "Home" tab at the top left of your menu bar. You should see tools that remind you of Microsoft Word. You can edit text now just like any other text editor. I like putting everything in a nicer font, like Garamond or Palatino Linotype, and then bolding my labels.

Step 5: Correct the Time Axis

Our graphs looks pretty good now, but lets take a look at that x-axis. Its oddly showing the year and quarter every 7 quarters. So, 1950-Quarter 1 is followed by 1951-Quarter 4 is followed by 1953-Quarter 3. This is obviously a weird way to label a graph.

To fix this, right-click on the x-axis dates and select "format axis". A box will appear with a bunch of options. For now, look at the top 2 options which I have labeled A and B and fill them in with 20 as I did on the right.

Note: 20 is a multiple of 4, the number of quarters in a year. Specifically 20/4=5. So our new graph should be labeled by the first quarter of every 5th year. You should always check to make sure you have these 2 boxes make sense. Otherwise you get weird, and often incorrect, axis labels.

Now that you have the basics down, you can go on to make more interesting graphs. Try to fiddle with all the options you get from right-clicking random stuff. Here are 2 ideas to get you started:

1) Right-click the GDP line and select "format data series." Try to change the color of the line and make it thicker.

2) Right-click the vertical axis and select "format axis". Try and change the scale of the axis and make the axis a logarithmic scale.

Feel free to ask questions or ask for clarification in the comments.

By the way, if you want to add recession lines to your graph, check out how at

http://hubpages.com/hub/How-to-Add-Recession-Bars-to-Your-Graphs-Using-Excel

Comments

    0 of 8192 characters used
    Post Comment

    • profile image

      hellogirl123 

      6 years ago

      Finding the actual measures coach madison leather around the arena is usually http://www.nflcheapjerseys2u.com/products/Soccer-c... built less complicated with a substantial high-definition movie display screen in coach handbags factory outlet which Dr.

    • PreetSaluja profile image

      PreetSaluja 

      6 years ago

      Thanks! Useful info. Voted up.

    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)