Related links:

Excel Charting Tips:  Time series charting
 

Chart tip #1: Excel 2007 vs. Excel 2003
Chart tip #2: Pie Chart tips
Chart tip #3: When to use Cone, Radar, and Pyramid Charts
Chart tip #4: Bar Chart Tips
Chart tip #5: Times Series Charts tips
Chart tip #6: Labeling Scatterplots (and other charts)
Chart tip #7: Boxplots
Chart tip #8: Where to go for more help
Chart tip #9: Printing and copying charts
Chart tip #10: Downloading different data formats
 

Time Series Chart Tips:

• Time series chart types: Line or XY?
• The Economist-style time chart
• Missing data on a trendline
• Controlling the X-axis
• Vertical - shaded time series chart:
• Horizontal - shaded time series
   

Time series chart types: Line or XY?

Most often, times series charts should be constructed using the Excel Scatter-with-lines chart type, rather than the "Line" chart type. 

see: Jon Peltier, X Axis: Category or Value?

Kelly O'Day Trend Charts: Line or XY?

The Economist-style time charts

The Economist magazine features some of the most effective charting practices, scrupulously adhere to Tufte's "minimize the ink-to-data ratio" rule. Note what is not on the chart at the left: Plot area borders, line on the vertical axis.  The Economist has also pioneered the practice of placing the Y-axis scale on the right hand side of the chart, thus highlighting the last and most important data point in the trend.

"Postcards from the ledge," Dec 19, 2007

Missing data on a trendline

In the chart shown for the next tip, below, notice that there is missing data for part of the lederly time series.  By default, Excel will not show any line for that part of the series.  The solution is explained in the 2007 Help section, titled "Display hidden data and empty cells in a chart"

Select the chart with the mouse, choose Chart Tools | Design | Select Data | Hidden and empty cells

In Office 2003 and earlier, select Tools | Options | Chart

Controlling the X-axis

In the chart below, notice the tickmarks beginning on the second data point (1960) rather than the first (1959) and the label for the last (2004).  Excel, even Excel 2007, only permits even spacing of the tick marks, beginning with the minimum value. You need a dummy data series and a chart-label add-in.

Step 1:  Enter the two data columns: one with the labels and the other with
what will be the minimum value of the Y-axis (Columns I and J, below)

Step 2: Add a new data series (click on the chart, select: Design | Select data | add) as shown below

Step 3. Remove the existing X-axis labels and tickmarks:  Layout | Horizontal (value) axis: format selection:
        select "none" for the tickmark type and axis labels boxes.

Step 4. Format the new data series using the plus sign (+) as the data marker: marker fill (no fill) | marker options built-in.

Step 5. Rescale the Y-axis minimun value (if necessary).

Step 6. Add data labels to the new series:

Step 7. Format the data labels: specify label contains X-value

Vertical - shaded time series chart:

The Census Bureau regularly displays its poverty time series data with shaded bars indicating recessionary periods, in quarters.

Constructing such a chart with Excel involves combining a time series chart (for the trends) and a bar chart (for the recessions).

To approximate the chart above, the bar part of the chart will consist of four data series (Q1, Q2, Q3 and Q4).  The recessionary quarters are identified with a number equal to what will be the maximum value (or greater) of the Y-axis.   Because the four bars for each year will be centered over each years' tick mark, quarters Q1 and Q2 will be the quarters to the left of each years' tick mark.   Step 1.  Create the column bar chart:   Step 2. Format the data series, specify gap width 0 each data series shaded in light blue. Step 3. Format the X-axis: position axis: on tick marks Step 4. Modify legend: Note that the "word" recession was used to label the first quarter (Q1) Click on each of the other legend labels (Q2, Q3, and Q4) and delete, so that only the box legend for the word "recession" remains. With some other formatting, this gives us the chart below:     Step 5. Now add the trendline data series (children first). click on the chart and select: Design | Select data | add

Step 6. Now select the newly added data series and select Change Series Chart Type | Line
Then add any additional series.
Reformat the chart as below:

Figure:  Child and Elderly Poverty Rates: 1959-2004

Horizontal - shaded time series:

Horizontal shading on a time series chart, perhaps to indicate a target range, can be done by
combining a stacked bar chart and a line chart.  {Note this can also be done by combining a
stacked area chart and a line chart}.

{click on this image for excel file}

Note: With Excel 2007, service pack 1, the "position axis on tick marks" setting has to be reset as you go through this process.

08/03/2008