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

 

Fun With Dummy Series.


 

Introduction

Microsoft Excel offers many chart types and many chart effects, but sooner or later you'll want to do something that Excel doesn't offer. Many effects can be accomplished through the use of extra series and clever formatting. For example, you might want a customized axis style, or a line or shape drawn in a particular position, or text labels that are tied into axis values.

Several of these tricks with dummy series are adaptations of Tushar Mehta's Flexible Log Scale Axis and Broken Y Axis techniques, from his web site, http://www.tushar-mehta.com. While custom labeling of dummy series points can be done manually, most of these tricks rely on Rob Bovey's XY Chart Labeler, a free add-in available for download at http://www.appspro.com, or John Walkenbach's JWalk Chart Tools, another free addin from http://j-walk.com/ss/excel.

Dummy Series Tricks

Simulating Axis Types that Excel Doesn't Include

Placing Lines, Shapes, and Labels Exactly Where You Want Them

See also Chart Axes and Axis Tricks and Combination Charts


Simulating Axis Types that Excel Doesn't Include


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


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


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


Stacked Line Chart.

Want to place multiple lines on a chart, but separate them so you can visualize all at once? This page contains instructions and an example worksheet and chart that show an approach with offsets built into the series, plus formatting tricks to dress it up.

A more comprehensive treatment of this technique, covering line, column, and area charts, is given in Stacked Charts With Vertical Separation.

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


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


Placing Lines, Shapes, and Labels Exactly Where You Want Them


Add a Horizontal or Vertical Line to a Column or Line Chart.

How do you add a horizontal or vertical line to a column or line chart, to show a target value, or the series average? The method involves adding a new series, applying it to the secondary axes, and making the secondary axes disappear. A variation uses an error bar for the straight line, rather than the connecting line between two points.

Add a Horizontal Line to a Column or Line Chart: Series Method.

Add a Vertical Line to a Column or Line Chart: Series Method.

Add a Horizontal Line to a Column or Line Chart: Error Bar Method.

Add a Vertical Line to a Column or Line Chart: Error Bar Method.

Top of Page


Stock Chart with Left Tick Marker for Open Series.

Note: This is an old article. I've discussed another way to accomplish this effect in Stock Charts and Other Line Chart Tricks.

When an Open-High-Low-Close chart is created in Excel, the result is a candlestick chart. The bar spans the range from open to close and the drop lines show the low and high extremes, with a white fill indicating an increase and a black fill indicating a decrease. Personally I find this confusing, because I have to read the data to recall whether white fill means up or down.

Stock charts in the newspaper and on line show a tick mark to the left of the low-high drop lines for open and one to the right for close. This page describes my procedure for left-handed opening price tick marks.

Top of Page


Special Format for Minimum and Maximum.

Ever want to apply special formatting to just a certain point? This example shows you how to highlight the minimum and maximum values of a series by using a different marker for each, and data labels. We'll accomplish this with two extra series in the chart, one for minimum and one for maximum.

Top of Page


Chart with Colored Quadrant Background.

I was recently asked, "I have an XY scatter chart that gets divided into 4 quadrants and each quadrant needs a different color. Any ideas?" I love a good challenge, so I came up with the procedure in this page.

Top of Page


Colored Vertical Band.

This page shows how to put a vertical band on a chart, highlighting a certain region. The technique can be used, for example, to highlight a certain time period in the background of a gantt chart.

Top of Page


Floating Column Charts.

Some kinds of data look very nice and are easily understood in the form of a "floating" column or bar, in which the column floats in the chart, spanning a region from a minimum value to a maximum value.

Top of Page


Excel Waterfall Charts (Bridge Charts).

Waterfall charts are a special type of Floating Column Charts. A typical waterfall chart shows how an initial value is increased and decreased by a series of intermediate values, leading to a final value. An invisible column keeps the increases and decreases linked to the heights of the previous columns.

This page shows how to arrange your data and create a waterfall chart in Excel.

Peltier Tech Chart Utility

I have developed the Peltier Tech Chart Utility that among many other features, allows the user to construct waterfall charts charts directly from the data; the utility adds the necessary data to the table and then creates and formats the chart from this table. The utility is designed to work in Excel for Windows versions 2000 through 2010. The Peltier Tech Chart Utility waterfall chart dialog is shown below:

Read about the Peltier Tech Chart Utility.

Top of Page


Control Bubble Chart Bubble Sizes.

In Microsoft Excel's bubble charts, bubble sizes are fixed according to the largest bubble in the chart. This is a problem when comparing multiple charts that have dissimilar bubble size data. This article shows an easy way to link the bubble size scales of charts with different bubble size data.

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