Updated date:

How to Graph and Label Time Series Data in Excel

Author:

I am a current college student who is fascinated by economics and math.

GDP vs. government spending: 1950-2010

GDP vs. government spending: 1950-2010

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. I will cover everything from downloading a data set from the internet to editing the style of your graph.

Note: This walk-through uses 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 since 1950. To get the data I go to the Bureau of Economic Analysis.

Here I have a couple of options ranging from when the data starts to how often you want the data (annual or quarterly GDP). I'm going to go ahead and select the quarterly data going back to 1950 and then press the "update" button. At the bottom of the page, click the "download dates shown" button to download the time series you've selected.

Select the quarterly data going back to 1950 and then press the "update" button.

Select the quarterly data going back to 1950 and then press the "update" button.

Step 2: Clean Your Data

Note: The purpose of this step is to make working with the 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.

  1. 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. (When I say row number, I mean the far left number, not the data in the Excel.)
  2. Now I right-click and select "delete" from the right-click menu. I could clean my data further, but for now I'm happy.
  3. Delete that pesky "line column".
  4. Select the problem cells and right-click. Now, select "delete". When given the option, select "shift cells left".
I have selected row 10 to row 27.

I have selected row 10 to row 27.

I select and delete that pesky "line column".

I select and delete that pesky "line column".

Step 3: Select the Data and Insert a Graph

  1. 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.
  2. Once you've highlighted the rows you want, let go of "ctrl" and go to the top menu bar. Click on the "Insert" tab.
  3. Click on the "Line" button.
  4. Choose the top-left icon.

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, it's 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.

When the small menu of graphs pops up, I usually choose the simple line graph (top-left corner). You can experiment with other types of line graphs, as well.

When the small menu of graphs pops up, I usually choose the simple line graph (top-left corner). You can experiment with other types of line graphs, as well.

In my opinion, the default graph is pretty ugly. Let's clean it up.

In my opinion, the default graph is pretty ugly. Let's clean it up.

Step 4: Label the Graph

  1. Add a title: Go to the "Layout" tab on the top menu bar. Click the "Chart Title" button.
  2. 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 you're adding labels.
  3. 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 axes.
  4. Add series labels: That legend on the right of the graph is just unsightly. Let's 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.
  5. Now you can delete that legend on the right and your graph has more space to breathe. It should look something like the graph below.

Recommended for you

Note: While you're 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.

Click the "Axis Title" button.

Click the "Axis Title" button.

Now your graph has more space to breathe.

Now your graph has more space to breathe.

Step 5: Correct the Time Axis

Our graph looks pretty good now, but let's take a look at that x-axis. It's 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 fifth year. You should always check to make sure you have these two boxes make sense. Otherwise you get weird, and often incorrect, axis labels.

Axis options menu

Axis options menu

Final cleaned-up graph

Final cleaned-up graph

Further Refinements

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 two ideas to get you started:

  • Right-click the GDP line and select "format data series." Try to change the color of the line and make it thicker.
  • 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.

For more graph refinements, learn how to add recession lines to your graph.

This content is accurate and true to the best of the author’s knowledge and is not meant to substitute for formal and individualized advice from a qualified professional.

Comments

S. Ghosh on July 21, 2020:

How to plot various data (say chemical concentration) on Y axis for points at varying distace on x axis? Any distance I give gets plotted on fixed interval on x axis, which is not acceptable.

hellogirl123 on May 08, 2012:

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 on April 04, 2012:

Thanks! Useful info. Voted up.

Related Articles