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.
- Create the chart as usual. It must be a bar chart or a column chart.
- Display the Drawing toolbar, and add a Rectangle AutoShape to your worksheet.
- Double-click the Rectangle to display the Format AutoShape dialog box.
- In the AutoShape dialog box, select the Colors and Lines tab.
- Use the Color control to select a color.
- Use the Transparency scrollbar to specify a transparency setting
- For best results, use the Line Color control and specify No Line.
- Close the Format AutoShape dialog box.
- Select the AutoShape. Press Shift, and select Copy - Picture (this command is available only when the Shift key is pressed). Accept the default settings.
- Activate your chart and click the bar or column series.
- Choose Edit - Paste, to paste the copied AutoShape.
- 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.
Excel has a long history, and it continues to evolve and change. Consequently, the tips provided here do not necessarily apply to all versions of Excel.
In particular, the user interface for Excel 2007 (and later), is vastly different from its predecessors. Therefore, the menu commands listed in older tips, will not correspond to the Excel 2007 (and later) user interface.
Browse Tips by Category
Search for Tips
Needs tips? Here are two books, with nothing but tips:
Contains more than 200 useful tips and tricks for Excel 2007 | Other Excel 2007 books | Amazon link: John Walkenbach's Favorite Excel 2007 Tips & Tricks