What's New?

Introducing the PTS Blog

Top of Page

Interactive Parallel Coordinates Chart

A parallel coordinates chart, or profile plot, is a useful way to compare several sets of observations (here, one set per quarterback) of a combination of different factors (offensive categories). The entire population, or at least a broad set of data, is generally shown in a subdued color (the gray lines in the chart) while one or more individuals are highlighted for inspection (the blue and red lines).

Top of Page

Line and Fill Effects in Excel Charts Using VBA.

Excel charts offer a wide variety of formats, but you can use Excel's drawing tools to enable even more formatting choices. It's possible to draw shapes on the chart to produce these formats, using the polygon drawing tool. This allows more line formats, by enabling more choices of line thickness and by making it easier to read dashed lines. More fill possibilities are made possible than merely filling below a series, as in an area chart: the fill can go below or to the side of the series, and in fact, an enclosed region in the chart can be filled. The fill can be made transparent too, allowing gridlines to show through the shape.

This article presents VBA procedures that automate the polygon drawing tool, and gives hints about the kinds of formatting which may be achieved. A sample procedure has been recently added to show how to use this technique for charts that have multiple series.

Top of Page

Box and Whisker Plots.

Box and Whisker charts are commonly used in the display of statistical analyses. Unfortunately, Microsoft Excel does not have a built in Box and Whisker chart type. You can create your own custom Box and Whisker charts, using stacked bar or column charts and error bars, in combination with line or XY scatter chart series to show additional data. The procedures in these tutorials have been updated to show how to add additional series (means of other populations, perhaps, or sets of target values). This page also links to a utility which can be used to generate Box and Whisker charts directly from population data. The Box Plot utility has recently been upgraded to provide more professional output, to correct treatment of outliers in horizontally oriented charts, to run tenfold faster, and to fix a few small bugs. The utility was previously updated to provide additional chart styles, and to correct problems experienced by some non-US users.

I have developed a Box and Whisker Chart Utility that allows the user to construct box and whisker charts directly from the raw data. The utility builds the intermediate summary table and then creates and formats the chart from this table. The utility is designed to work in Excel versions 2000 through 2007. There is a professional version with an extensive feature list, and a free demo version which makes simple box plots.

Top of Page

Consolidate Text Data for Charting.

You have a column of text values, which is often how survey data is formatted. You would like to plot these values, but an Excel chart cannot create a sensible chart from such a range.

This page shows how to consolidate the text values and calculate the occurrances of each value, using a set of COUNTIF formulas, or a pivot table.

Top of Page

Change Series Formulas.

Ever make a copy of a chart, and all you want is do is change the worksheet containing the source data? Or change the X values of all series from column A to column B? The series formula is a simple text string, but there's no Search and Replace feature in Excel that can access these formulas. But you can use some very simple VBA code to make wholesale changes to chart series formulas.

The utility was upgraded on 1 August 2007 to avoid a VBA error that occurs when a defined name is used for the X values in the series formula.

Top of Page

Waterfall Charts And Utility.

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

Panel Charts with Different Scales.

Panel charts are useful for showing several sets of data in separate panels of a chart. This arrangement allows comparison of values without some plotted data obscuring the rest. Simple panel charts use the same scales in adjacent panels, but there are many cases in which a panel chart is ideal for displaying data with different value ranges, and even in different sized panels. For example, a stock chart may have rice data plotted in a larger upper panel, with volume in a smaller panel beneath the prices. Expanding on Stacked Charts With Vertical Separation, this article shows how to make flexible panel charts with different axis scales and even different panel sizes.

Top of Page

Deviation Column Charts.

This page shows how to use column charts to show deviations from budget. Two ways are shown to differentiate positive and negative deviations: the Invert if Negative formatting option for a single series, and the use of separate series in the same chart.

Top of Page

Invert if Negative.

The Invert if Negative formatting option seems like it should be easy, but it is not obvious how to use it to get a negative bar or column that is filled with any color besides white. This page shows how to use the Invert if Negative option and get the colors you want.

Top of Page

Axis Label Cut Off.

Under certain conditions, the text of a vertically aligned axis title may be cut off, so it loses the end of the title. This label truncation may be as little as a pixel or two, or as much as several characters. This article analyzes the problem and presents several alternative workarounds.

Top of Page

Plot an Equation.

A common question new users ask is "How can I plot an equation?" I think people expect that charts have a magical ability to accept an equation and spit out a graph of X vs. Y. Excel charts aren't that smart, though: they can only plot data values that you assign. To provide this data you need to enter X values into one column (or row), and calculate the corresponding Y values in the next column (or row). Then select this data and create an XY chart to show the relationship.

Top of Page

Using Solver in VBA.

Solver is a powerful analysis tool, bundled with Excel and used for optimization and simulation of business and engineering models. It can be even more powerful if used in conjunction with VBA, to automate solving of multiple models which use different input parameters and constraints. This article shows how to run Solver using VBA conventionally and using VBA with Application.Run to avoid reference issues, and it shows how to initialize Solver for its first run so the user need not run Solver manually the first time.

Top of Page

Dot Plotter Utility.

A "Dot Plot" is an alternative to a bar chart that is cleaner and according to research more easily understood. The chart displays labels along a vertical axis, while the data is charted as dots, spaced horizontally according to its value. This page describes a utility which can be used to generate Dot Plots directly from worksheet data.

Top of Page