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

There are times when we need to use more than Excel's standard XY Chart axis formatting  to have the X or Y axis look the way we want. The example custom X - Y axes in the table below show that you can enhance the readability of our XY Charts with custom axes.

Example XY Chart Custom Axis - (Examples from this site)
Y Axis - Flexible  Log Scale	Y Axis - Text Instead of Numbers	Y Axis - 2 Level Text Labels	X Axis - Year & Quarter Labels Centered in Span	X Axis - Month Label Centered in Span

We can make custom axis by replacing the Excel generated axis with a new data series (dummy series)  and formatting the new series line and marker formats and data labels the we want. Just about any type of scale can be assigned to an axis using custom axes.

Tushar Mehta has a nice tutorial on flexible log scales.  We'll use his example to show how to make a custom Y axis. His data is reproduced on the right.

The goal is to plot the Y values on a flexible log scale. Excel's default log scale is inflexible, you can only have values that are multiples of 10. In this case, we want to have the Y axis range from 40 to 460,000.

Organizing Our Data

We need to organize our data so that we can chart both the data series and our custom axis series. In this case, we want to plot the log of our Y values on the Y axis and then a custom axis series that will show the Y values. We need to set up two data sets, our data series and our custom chart axis series, usually called a dummy series. While I prefer the term custom axis series because it describes the role of our series, either custom axis series and dummy series can be used.

The data series data table includes the X value and the log of the Y values.

The custom Y axis series data table includes the X and Y plot values and the labels that we want to show on our custom Y axis. The X values are all 0 because we want the custom Y axis to be located at 0 on the X axis.  We specify the Y label values that we want to display and then calculate the log of  values for our custom series.

We make and format our chart in 5 steps:

1. Initial Chart

We start with an initial chart that plots the Y value logs.

2. Remove Default Excel Axis

We can remove the default axis by activating the format axis patterns dialog and setting Major tick mark type, Minor tick mark type and Tick mark labels to none.

3. Add Custom Y Axis Data Series

We now want to replace the default axis line and markers with our custom line and markers. To do this, we need to create a dummy data series that we will add to the chart. The dummy series must have the X and Y values and tick mark data labels.

4. Format Custom Y Axis

We format the custom Y axis by changing the color and data markers.

5. Add Data Labels to Custom Y Axis

Now that we have our Y Axis markers, we want to add labels to them. There are two popular and free chart labeler add-ins:

• XY Chart Labeler  (Rob Bovey)           
• Chart Tools (John Walkenbach)

We place the labels to the left of our markers by adjusting the label alignment.

We now have our custom Y axis labeled the way we want.