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 Formatting Techniques and Tricks


 


Simple Conditional Charts.

Set up conditional formatting for your chart. Change color and marker style depending on the value of a point.

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


Use an Arrow to Indicate Special Points.

People often want to use an arrow or other symbol to indicate a point in a chart. If you draw an arrow, or any AutoShape, in a chart, it is not in any way tied to the data or to the chart axes, so it will not move to keep up with a point as the axes change or the chart resizes. Even if the chart does not change, an AutoShape is not guaranteed to be in exactly the same position the next time the file is opened. This technique shows how to attach an indicator (arrow) to a point by creating custom markers for the Min and Max series.

Top of Page


Horizontally Banded Chart Background.

A chart could be made more informative by selectively shading regions of the background with different colors. For example, a run chart may show colored bands to indicate standard deviations of a process value from the mean. Excel only provides the ability to add one color to the background, but multiple colors can be added by creating a combination chart with added area chart series colored as desired. This tutorial shows how to construct such a chart.

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


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


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


Custom Histograms.

Suppose you want to chart the relative frequency of numbers in a list. Suppose further that instead of a bland column chart, you want to put an X in the histogram for every occurrence of a value. You can do this with a scatter chart, using a procedure offered by Excel MVP Debra Dalgleish in the Microsoft Charting news group.

Top of Page


Clustered and Stacked Column and Bar Charts.

Excel offers clustered column charts and stacked column charts among its standard options. How do you combine a stacked column chart with a clustered column chart?

Through careful arrangement of the data in your worksheet, you can make a stacked column chart that looks like a clustered-stacked column chart. In Clustered and Stacked Column and Bar Charts. I show how it is done with illustrated step-by-step instructions.

Peltier Tech Cluster Stack Chart Utility

In this page I have described a technique for constructing clustered and stacked column charts in Excel. The protocol required to create clustered-stacked charts is rather tedious, and the data layout is complicated, so I have developed the Peltier Tech Cluster Stack Chart Utility that allows the user to construct clustered-stacked column charts directly from the data. The utility inserts a new worksheet, adds a table with the appropriate data arrangement, and then creates and formats a clustered-stacked column chart from this table.

The utility allows the user to set up a simpler data range, and through a dialog select the clustering and stacking configuration.

The utility is designed to work in Excel for Windows versions 2000 through 2010.

Read about the Peltier Tech Cluster Stack Chart Utility.

Top of Page

Warning: include(ColumnsOnTwoAxes.php): failed to open stream: No such file or directory in /www/eh7701/public_html/Excel/Charts/format.html on line 88 Warning: include(): Failed opening 'ColumnsOnTwoAxes.php' for inclusion (include_path='.:/usr/share/php:/usr/share/pear') in /www/eh7701/public_html/Excel/Charts/format.html on line 88 Warning: include(SqueezeColumns.php): failed to open stream: No such file or directory in /www/eh7701/public_html/Excel/Charts/format.html on line 89 Warning: include(): Failed opening 'SqueezeColumns.php' for inclusion (include_path='.:/usr/share/php:/usr/share/pear') in /www/eh7701/public_html/Excel/Charts/format.html on line 89
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


Column Combination Chart for Monthly Temperatures.

I was perusing the Sunday paper recently, and came across a chart which summarized the past month's temperatures. It's an informative chart, which compares each day's minimum and maximum temperature with the normal range and the historical minimum and maximum temperatures for each date. I've seen similar charts many times, including this same design in the local paper, but this time the chart called out to me, and I had to build it myself. This page has links to three versions of this chart.

Top of Page


Excel Box and Whisker Diagrams (Box Plots).

Box and Whisker charts (Box Plots) 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.

Peltier Tech Box and Whisker Chart Utility for Excel

I have developed the Peltier Tech Box and Whisker Chart Utility for Excel that allows a user to construct box and whisker charts directly from the raw Excel data. The utility builds the intermediate summary table and then creates and formats the box plot from this table. This utility is designed to work in Excel for Windows versions 2000 through 2010. The Box and Whisker Chart dialog is shown below:

Read about the Peltier Tech Box and Whisker Chart Utility for Excel.

Top of Page


Fix Chart Font Scaling

Hate the way Excel mucks up your chart font sizes when you resize the chart?
So do I, and here's how I counteract the default font scaling.

Top of Page


Make Gridlines Square.

A common question people ask is "How do I format my chart so its gridlines make a square pattern?" Excel has no setting that forces equally spaced horizontal and vertical gridlines or horizontal and vertical axis ticks, but you can achieve this effect using VBA. Below is a chart after making the appropriate scale adjustment.

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