How to Graph and Label Time Series Data in Excel
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.
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.
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.
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.
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