Stacking Pictures in Column and Bar Charts

Build a Column or Bar Chart with Images

Tired of using the same boring fill choices for columns and bars in your charts? This page shows how to use any custom shape to build an Excel column or bar chart.

Make your chart, and temporarily use any handy fill in Excel's extensive palette.

Now on your worksheet, draw the shape you want, from the AutoShapes available on the Drawing toolbar. Size and format it the way you want it to appear in the chart. Our example will start with our favorite graphic, the smiley face.

Copy the custom shape. Activate the chart and select the series in your chart. Use Ctrl-V or Edit menu > Paste to paste. The smiley face becomes the custom fill for the column (or bar) series.

The custom shape by default stretches itself to fill the entire width and height of the column (or bar). You can get the effect of using multiples of the shape as building blocks. Select the series or just one of the columns or bars, if desired, and press Ctrl-1 (numeral one) to format the series or point. On the Patterns tab, click on Fill Effects, choose the Picture tab, then enter a number of Units/Picture in the Stack and Scale To box. In the following chart, each smiley face is scaled to one unit on the Y axis.

You can draw a border around the stacked shapes. Select the series (or a single point, if desired), and press Ctrl-1 (numeral one) to format it. On the patterns tab, apply the desired border. (Don't try to apply a fill at this point, because the fill will obliterate the label you've pasted into place.)

If you have a multiple point series, and you select the entire series before pasting, the copied shape becomes the marker for every point in the series, as above. If you select just one point before pasting, the copied shape becomes the marker for just the selected point. Here are a variety of custom shapes: four arrows of different color and orientation, and two single-character WordArt objects.

Select and copy a shape (the green, left-pointing arrow), then select a single point in a chart series (two single clicks), and paste. The selected point is now marked by the arrow. Repeat with the remaining shapes and points. Notice that when the entire series is formatted at once, its legend entry picks up the custom series marker. When the points are formatted one-by-one, however, the legend retains the original boring built-in chart series marker.

Suppose you want a WordArt label, with the space between the letters filled with a distinct color. Perhaps you also want a distinct border around the label. Copy the WordArt label, then use Edit menu > Paste Special to paste it into the workbook as a picture. The Line Color and Fill Color command bar buttons work on the background of the WordArt picture, leaving the border and fill of the letters unchanged. Double clicking on the WordArt picture, and using the controls on the Colors and Lines tab has the same effect.

Below is a single character WordArt label (left), pasted as a picture with colored fills around the letter (center), and pasted as a picture with colored fills and a black border (right).

The following chart shows several options for column chart formatting:

WordArt: The original WordArt label is copied and pasted into the charted point.
Picture: The original WordArt label is copied and pasted into the worksheet as a picture. This picture is copied and pasted into the charted point.
Picture + Fill: The original WordArt label is copied and pasted into the worksheet as a picture. The picture is given a fill color. This picture is copied and pasted into the charted point.
Picture + Fill + Border: The original WordArt label is copied and pasted into the worksheet as a picture. The picture is given a fill color and a border. This picture is copied and pasted into the charted point.
Picture + Fill (Series Border): The original WordArt label is copied and pasted into the worksheet as a picture. The picture is given a fill color. This picture is copied and pasted into the charted point. The charted point is formatted to apply a border. (Don't try to apply a fill at this point, because the fill will obliterate the label you've pasted into place.)

We can make a couple of observations about this chart:

The WordArt and Picture approaches are essentially identical. The border around the letters in the WordArt sample is thicker than the border around the letters in the Picture sample; WordArt borders scale proportionally as the WordArt object itself is scales, while Picture borders remain a constant width. If the WordArt object had been decreased in size to fit, the border would have become thinner.
The Picture + Fill + Border approach results in labels that are smaller than the original columns. This results in distortion of the data. The Picture + Fill (Series Border) approach is therefore the preferred method if a border is desired.

For more custom chart series formatting, check out Custom Chart Series Markers and WordArt Labels for Custom Chart Series Fills.

Peltier Technical Services, Inc.

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

Peltier Technical Services, Inc., Copyright © 2010. 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

My MVP Profile