Peltier Tech
Chart Utilities

Excel Dashboards

Books at Amazon.com

Buy me a coffee

If this topic or the whole site has been helpful, please support further development by treating me to a cup of coffee.

Apply Word Art Fills to a Column or Bar Chart

Tired of the same, dull patterns for column and bar chart fills? This page shows how to use WordArt labels in your column and bar charts.

Make a column or bar chart, and temporarily use any series format in Excel's extensive palette.

Now on your worksheet, draw a WordArt object. Format it the way you want it to appear in the chart. Our example will start with these simple labels.

Copy the first WordArt label. Activate the chart and select the first series in your chart. Use Ctrl-V or Edit menu > Paste to paste. The WordArt label fills the first chart series. Repeat with the second WordArt label and the second chart series.

Notice two things about the chart. First, the WordArt labels are transparent between the letters, so that gridlines and the plot area fill color show through (at least it would, if it were a color other than white). Second, the legend entry symbols are severely distorted. The legend is redundant in this case, since the WordArt labels tell the story.

If you have a multiple column/bar series, and you select the entire series before pasting, the WordArt label becomes the marker for every point in the series, as above. If you select just one column or bar before pasting, the WordArt label becomes the marker for just the selected point. This chart has a distinct WordArt label for each individual column in each series. Because the points were formatted one at a time, the legend key retains the original series formats, not the distorted symbols observed above. If you want to prevent distortion of your legend key, therefore, paste the WordArt labels to each point individually, even if the points share a common label.

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 are the two WordArt labels from the previous chart, with colored fills around the letters (left), and 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 slightly 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.

WordArt labels work equally well in a bar chart. The Picture + Fill (Series Border) remains the preferred method to produce a WordArt label with additional fill between the letters and a border around the label.

For more custom chart series formatting, check out Custom Chart Series Markers and Build a Column or Bar Chart with Images.

Peltier Technical Services, Inc.

Custom Solutions | Commercial Utilities | Training | Charts and Tutorials | PTS Blog

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