Creating A Transparent Chart Series

Excel charts do not support color transparency. When you apply a color to a bar or column chart series, the color must be a solid color. Although the Fill Effects dialog box includes Transparency controls, these controls are disabled.

This tip describes how to make a bar or column series transparent by pasting a picture. The figure below shows a chart after the column series has been made transparent. Notice that the gridlines show through the columns.

Following are the steps required.

1. Create the chart as usual. It must be a bar chart or a column chart.
2. Display the Drawing toolbar, and add a Rectangle AutoShape to your worksheet.
3. Double-click the Rectangle to display the Format AutoShape dialog box.
4. In the AutoShape dialog box, select the Colors and Lines tab.
5. Use the Color control to select a color.
6. Use the Transparency scrollbar to specify a transparency setting
7. For best results, use the Line Color control and specify No Line.
8. Close the Format AutoShape dialog box.
9. Select the AutoShape. Press Shift, and select Copy - Picture (this command is available only when the Shift key is pressed). Accept the default settings.
10. Activate your chart and click the bar or column series.
11. Choose Edit - Paste, to paste the copied AutoShape.
12. If you formatted the AutoShape without a border, you may prefer to add a border to your chart series. Double-click the series and use the Patterns tab in the Format Data Series dialog box.

In the example below, the transparent AutoShape was formatted with a rainbow gradient.