Peltier Technical Services, Inc.
 

Excel Chart Add-Ins | Training | Charts and Tutorials | Peltier Tech Blog


Peltier Tech Chart Utility

 

Excel Dashboards

 

Books at Amazon.com

 

Chart Axes and Axis Tricks


 

See also Dummy Series and Combination Charts


X Axis: Category or Value?

In Microsoft Excel charts, there are different types of X axes. While the Y axis is a Value type axis, the X axis can be a Category type axis or a Value type axis. Using a Value axis, the data is treated as continuously varying numerical data, and the marker is placed at a point along the axis which varies according to its numerical value. Using a Category axis, the data is treated as a sequence of non-numerical text labels, and the marker is placed at a point along the axis according to its position in the sequence. This page illustrates the difference between Value and Category Axes, and describes some of the unique behavior of Date-Scale Category Axes.

Top of Page


Vertical Category Axis.

How do you arrange your chart so the categories are displayed along the Y axis? The method involves adding a dummy series along the Y axis, applying data labels to its points for category labels, and making the original Y axis disappear.

You may also want to check out the page on Dot Plots.

Top of Page


Stacked Charts With Vertical Separation.

Want to place multiple series on a chart, but separate them vertically so you can visualize all at once? Here is an example of a four-high stack with offsets built into the series, plus formatting tricks to dress it up. This can be done with Area, Column, or Line Chart styles.

This class of charts is known as panel charts, which are described in Panel Chart Example: Chart with Vertical Panels and Panel Charts with Different Scales.

Top of Page


Tertiary Y Axis.

I once had a page on this site that showed how to generate additional axes in a chart. With the technique you could go beyond primary and secondary axes, to tertiary and quaternary axes, and even more.

Given that charts with just primary and secondary axes can be confusing, a tertiary axis is just overkill. Multi-axis charts can be cluttered and confusing, even when using a custom color scheme to help identify each series with its corresponding axis.

I propose using Panel Charts as a substitute for charts with confusing multiple axes. Here are some tutorials and examples of panel charts:

Top of Page


Arbitrary Axis Scale.

Don't you wish you had more control over Excel's chart axis ticks and labels? There isn't a lot of built in support for this, but over the years people have learned a few tricks to generate charts like this.

Top of Page


Arbitrary Gridlines.

Don't you wish you had more control over Excel's chart gridlines? Wouldn't you like Excel to draw gridlines where you want them? There isn't a lot of built in support for this, but over the years people have learned a few tricks to generate charts like this.

Top of Page


Align X Axis to Y=0 on Two Y Axes.

A common question is:

I have a chart with data on the primary and secondary Y axes, and both axes range from negative to positive numbers. How do I make the X axis cross each Y axis at X=0?

This page describes the relatively simple algebra required to accomplish this task, and provides a VBA procedure to do this easily.

Top of Page


Simulated Probability Chart.

Microsoft Excel does not offer a built in capability to chart probability data, but the technique described here allows you to simulate a probability scale along a chart axis.

Top of Page


Reciprocal Axis Chart.

An Arrhenius equation gives the following relationship between some measure of reaction rate or chemical solubility and temperature:

K = A exp (-Q/RT)

where K is the rate or solubility, A is a constant, Q is an activation energy, R is the gas constant, and T is the absolute temperature. The equation above can be restated to:

log (K) = A' - Q/RT

A chart can be constructed with log(K) on the Y axis and reciprocal temperature (1/T) on the X axis; the slope of this line indicates the activation energy (actually -Q/R) and the intercept is the new constant A'. This page describes the construction of one variation of this type of chart.

Top of Page


Switch X and Y on an XY Chart

Ever get your chart all fixed up, then decide you want to swap the X and Y axes? Or switch the X and Y values of a series in the chart? Or both? This zipped Excel file can swap the X and Y on your chart.

This only works on XY Scatter Charts, not Line Charts, or Bar Charts, or any other kind of chart. So far it also only works on primary axes, not secondary axes. Eventually I will expand this utility's capabilities to include Line Charts, and Bar and Column Charts, and secondary axes. I have not yet built in error checking to assure you don't try to use this on the wrong style of chart. So please save your work before using the Switcher, and keep in mind these limitations.

Top of Page


Creating Exponential Notation Axis Labels.

The way most computer programs render numbers in scientific notation is not particularly attractive, for example 1.23E-04. Most of us learned true exponential notation in high school, and many publications require the use of this notation, rendered with a true superscripted exponent, for example 1.23x10-4. Here is a technique to add exponential notation axis labels to your chart.

Top of Page


Staggered Axis Labels.

Sometimes the labels of a category axis are too closely spaced, and either Excel inclines the labels, or it will not render them all. Unfortunately Excel offers no built-in method for staggering axis labels, but there is a simple trick to achieve the same result.

Top of Page


Link Chart Axis Scale to Values in Cells.

Excel offers two ways to scale chart axes. You can let Excel scale the axes automatically; when the charted values change, Excel updates the scales the way it thinks they fit best. Or you can manually adjust the axis scales; when the charted values change, you must manually readjust the scales. Wouldn't it be great to be able to link the axis scale parameters to values or, even better, formulas in the worksheet? This page shows how to use VBA to accomplish this.

Top of Page


 

 

Page copy protected against web site content infringement by Copyscape

 

Peltier Tech Chart Utility

 

Create Excel dashboards quickly with Plug-N-Play reports.


Peltier Technical Services, Inc.

Excel Chart Add-Ins | Training | Charts and Tutorials | PTS Blog

Peltier Technical Services, Inc., Copyright © 2014. All rights reserved.
You may link to this article or portions of it on your site, but copying is prohibited without permission of Peltier Technical Services.

Microsoft Most Valuable Professional

Microsoft Most Valuable Professional

My MVP Profile