Exercise 2-Graphing and Dates for Excel 2007

Pg 1 of 11

Exercise 2-Graphing and Dates for Excel 2007

This exercise will cover how to chart data, using aggregated data or individual points, so that patterns can be recognized. It is important to evaluate data graphically first, before leaping into complicated statistical analyses.

Dates

In general, use the x-y (scatter) type chart, even for charting dates. The excel 'Time' Scale axis is really a 'Date' Scale axis: all times from a given date are treated as integers, and are plotted at midnight at the start of that date. To see how this can mislead you, make a small line chart.

Open the sheet Axes in the Exercise 2.xls spreadsheet (all the way to the left in the tabs on the bottom of the file).

Select the first two columns of data then click Insert,

Then select a line chart:

Excel treats the line chart as a category chart, so that each value (date in this case) is a category rather than a value (even though it says “useful to display trends over time, dates, years) it is still a category variable rather than a continuous variable that is plotted on the x axis.

You can see this more easily if you add another series.

Add the data in columns C and D to the chart as a new series, by first selecting the new data (columns C and D), clicking Copy,

Thenselect the chart (when it is selected it has a little border with 3 dots on each side around it), and use Paste:

Which gives you this:

You can see that although the last date in the new series is in 2001, it used the date from the first series and does not plot the point correctly. This is because excel treats the date axis as a category rather than as a continuous type variable, and just assumes that each new point should be plotted in order, no matter what its date is.

To enable multiple date ranges, convert the to an XY (SCATTER) chart, as follows:

First, select the chart by clicking on it so you see the border with the 3 dots on each side, then right-click the chart and select Change Chart Type.

Then select X Y (Scatter) connected by lines with markers for each point:

Now you can see that the Value 2 values are plotted correctly, in 2001:

Add the Value 3 as a third series, by first selected the data:

Then right-clicking the range and selecting Copy:

Then selecting the chart by clicking on it, then right-clicking the chart and selecting Paste, and it should look something like:

You can see how excel is treating the dates as numbers if you click on the x-axis (the easiest way to do this is to click on one of the dates, right-click and select Format Axis:

Then pops up this dialog box:

And you can see that the minimum, maximum, minor unit (between tick marks on the axis) and major units are not dates but are numbers. These are the numbers that “underlie” the date values. For example, if you enter 40.0 in any excel cell:

Then hit return, then select that cell and go to Home, Cells:

Then click on Format cells:

Then format that cell as a date:

Then that cell appears as:

Save your file.

To submit the exercise result, open a new Word document, name it Excel 2 xxx-mmddyy (with xxx being your initials and mm being month, dd the date, and yy the year), then copy and paste the chart with the 3 series into the Word document, and submit the Word document using the Assignment tool.