Dummy Axis Series

This tutorial shows you how to build a step chart and add a dummy X axis series to improve Excel's XY Chart date format. It is based on a workbook that also shows how to make a step chart.

1. Dummy Axis Series

XY charts let us accurately place markers exactly where we want in a 2 dimensional grid. Excel lets us format our charts by adjusting the scale range, interval and axis number formats.

There are times when we need more than simple formatting to show what we want:

Dummy series provide a powerful tool that you can use to significantly enhance Excel charting capability. There are several sites that provide excellent "how to" information on dummy series and chart axis formatting.

The basic concept is to replace Excel's standard Axis (X or Y) with a dummy axis line that has data point markers and data labels to show the User's desired scale. Just about any type of scale can be assigned to an axis using this dummy series axis method.

This site includes a number of dummy axis series in this site:

2. XY Trend Chart Dates - Can We Make Them Better Looking?

Dates are a fundamental part of trend charts. If we are plotting one years worth of data, it would be nice to show the months. If we are showing several years of data, we may want to just show the years or years and quarters. Excel line charts have a built in capability to properly scale the dates for either months or years. XY charts need a little help because months have irregular intervals (31,28,31,30,etc days per month) and years have 365 days except for leap year(366).

We can use a dummy date axis series to label our months and/or years the way we want, and improve on the default data formatting. This way, our XY charts can look just as good or better than a line chart.

Let's start by comparing an XY chart with default dates and one with a dummy date axis series.

The default data format chart shows the month - year on the first day of the month. The dummy series chart shows markers for the beginning and end of year as well as quarter. The year data label is in the middle of the year span.

Improving the data axis display helps the reader interpret the data much more rapidly.

3. Organizing Our Source Data

To make our year and quarter dummy axis series, we will need to add three dummy data series:

Year labels to show up at mid-year (7/1of year)
1st Day of Year Marker series for each year
1st Day of Quarter Marker series (except Q1 which is same as 1/1/ of year)

Our three dummy series data tables are shown on the right.

The Year labels will be placed at 7/1 for the year on the x axis and 0 on the Y axis.

The 1st Day of Year markers will be placed on the first day of the year on the X axis and 0 on the Y axis. The last year marker is placed on 12/31/05. the grid line marker will be used to provide vertical grid lines for each year.

The quarter markers will be placed on 4/1, 7/11 and 10/1 of each year on the X axis and 0 on the Y axis.

4. Adding Dummy Series to Chart

We are now ready to remove the original x axis markers and labels and add our 1st of year markers, 1st day of quarter markers, and year labels in mid-point of year span.

See Day of Year Scale for an explanation of the steps.

J-Walk's Excel Charting Tools was used to place the date labels. http://j-walk.com/ss/excel/files/charttools.htm.

The workbook includes the detail steps.

Step Chart and Dummy Axis Series