|
Excel Books
| |
Books that I own and use while developing in Excel
|
|
Goods and Services
| |
Excel or charting related products and services which I use or feel are worthwhile additions
|
|
|
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
Simulating Axis Types that Excel Doesn't Include
|
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.
Top of Page |
|
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
|
|
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
|
|
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
|
|
This zipped Excel file has another example of a chart using arbitrary axis and gridlines. The data is sparse for early times and dense later, so neither a Line chart nor an XY Scatter chart will do.
Top of Page
|
|
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
|
|
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
|
|
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.
Top of Page
|
|
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.
Top of Page
|
Placing Lines, Shapes, and Labels Exactly Where You Want Them
|
If you create a clustered column chart that has some blank values, the blanks will result in gaps between columns in the chart. Excel doesn't allow you to squeeze the existing columns together, but you can rearrange your data and use dummy series to fix the category axis.
Top of Page
|
|
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 |
|
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
|
|
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
|
|
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
|
|
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
|
|
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. At the end of the page you'll find a link to a utility for creating waterfall charts easily. The utility has recently been updated to provide optional labels above columns in the chart, and to correct problems experienced by some non-US users.
Top of Page
|
|
This page describes construction of a fancy Waterfall Chart. This chart shows how three classes of revenue increased from 2002 to 2003 because of Volume, Price, and other factors.
Top of Page
|
|
Waterfall charts are a graphical approach to show how an initial value is increased and decreased by intermediate values, to reach its final value. When the intermediate value drops below the X axis, the stacked column chart approach no longer accurately displays these values. This page shows how to combine the Waterfall Charts technique with the Stacked Column Charts that Cross the X Axis approach to correct the chart's appearance. The page has been updated to show how to add labels above the bars in the chart.
Top of Page
|
|
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
|
|
|
Waterfall Chart
Box and Whisker
