Written by Allen Wyatt (last updated May 9, 2020)
This tip applies to Excel 97, 2000, 2002, and 2003
When you create a chart with Excel, you typically use the Chart Wizard to create the basic chart, and then make changes to prepare the final chart exactly as you want it. One of the things that Excel allows you to control is the pattern used to fill the graphic representation of a data series.
Normally, series are presented as appropriate for the type of chart you are creating, and then filled with varying colors. For most charts this may be more than adequate. However, you may want to change the pattern used in a data series, instead of using the solid color used by default. To do this, follow these steps:
- Create your chart as you normally would.
- Click on the data series that you want to customize. There should be selection handles that appear around the data series elements.
- Choose Selected Data Series from the Format menu. Excel displays the Format Data Series dialog box.
- Make sure the Patterns tab is selected. (See Figure 1.)
- Click on the Fill Effects button. Excel displays the Fill Effects dialog box.
- Make sure the Pattern tab is selected. (See Figure 2.)
- From the selections in the dialog box, choose the pattern you want applied to the data series.
- Click on OK to close the Fill Effects dialog box.
- Click on OK to close the Format Data Series dialog box.
Figure 1. The Patterns tab of the Format Data Series dialog box.
Figure 2. The Pattern tab of the Fill Effects dialog box.
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (3022) applies to Microsoft Excel 97, 2000, 2002, and 2003.
With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. Learn more about Allen...
MORE FROM ALLENSwapping Two Strings
Part of developing macros is learning how to use and manipulate variables. This tip examines a technique you can use to ...
Discover More
Changing the Attached Template
Templates, when attached to a document, can greatly affect how that document looks. You can change from one template to ...
Discover More
Preventing Changes to Multiple Selected Worksheets
It is common to select a group of worksheets and then print them. When done, any edits you make may affect the entire ...
Discover More
More ExcelTips (menu)Cropping Pictures
Excel allows you to easily add graphics to a worksheet. If you want to crop an image you previously added, here's how to ...
Discover More
Using the Camera in VBA
The camera tool allows you to capture dynamic "pictures" of portions of a worksheet. If you want to use the camera tool ...
Discover More
Pop-Up Comments for Graphics
Excel allows you to add comments to individual cells in a worksheet, but what if you want to add comments to graphics? ...
Discover More
Pattern Fill Add-In
In xl2007 the chart feature of using pattern fills was deprecated.
For backward compatibility the functionality was included in the new charting engine. Only the interface to it was excluded.
This add-in provides the UI in order to use that functionality.
The add-in add a new group to the following contextual tabs, which appear when a chart is
selected.
- Chart Tools, Layout
- Chart Tools, Format
- Drawing Tools, Format
- Pivot Chart Tools, Layout
- Pivot Chart Tools, Format
The dialog allows you to select Foreground and Background colours.
You can also select from the predefined patterns
When completed the chosen pattern and colours are applied to the select chart element, if applicable.
Chart with normal formatting
Chart with Shingle pattern applied.
The add-in will now handle shapes
as well.
The Pattern Fill button also appears on the Drawing Tools Format contextual tab when a shape is selected.
You can apply pattern fills to the following charting elements
- Chart Area
- Plot Area
- Data Series (any chart style that has fill capabilities)
- Data Point
- Data Labels
- Data Label
- Legend
- Chart Title
- Axis Titles
Note: Due to the fact that using VBA destroys the undo stack you will not be able to use undo to restore any changes made using the add-in.
- Revision v1.1 - Added code to pick up, where possible, Theme colours used in workbook.
- Revision v2.0 - Add-In now handles shapes.
- Revision v3.0 - Added Fills for Pivot Charts
- Revision v3.3 [4-Jul-2009]
- Recoded Selection detection routine to handle OM bug.
- Transparent shapes should now retain transparency.
- Revision v3.4 - Filling of Line shapes such as arrows, arc, curves and scribbles.
Instructions on how to install an add-in
To replace existing add-in with later version you simply need to overwrite the existing
add-in. Make sure Excel is not running otherwise you will not be able to replace the file.