In Excel 2003 and now in Excel 2010 , there are pattern fills which you can use to fill chart bars so your charts print just great in black and white.

Unfortunately the same feature was removed from Excel 2007 – wtf? I have no clue why but it was but it has to be a very silly thing to have done.

If you are using Excel 2007 and you need to use pattern fills with a chart you are out of luck – well not really – you just need to read the rest of this tip because I can tell you how to put the fills back into Excel 2007.

To begin, download this handy add-in: http://officeblogs.net/excel/PatternUI.zip 

Update: This link is no longer live so patternui.zip is not longer available. You can find an add-in here (with instructions which achieves the same thing) courtesy of Andy Pope.

The zip file contains a single file patternUI.xlam which you need to extract and place somewhere you will find it easily and where it won’t get deleted by accident. You could make an Excel add-ins folder for it, for example.

Once you’ve done this, open Excel 2007 and choose the Office button > Excel Options > Add-ins and from the Manage dropdown list, select Excel Add-ins and click Go. This opens the old Add-ins dialog from earlier versions of Excel. Click Browse and locate the .xlam file that you just unzipped and placed somewhere safe. Select it and click Ok. Ensure that the PatternUI option appears in the Add-ins available list and that it is checked and click Ok.

Now create an Excel chart. Once you have you chart, click on the data series to fill with a pattern – if you have a single series plotted then select just one of the columns at a time. Select the Chart Tools > Format tab and notice that you now have an option called Patterns available. Click the Patterns option and select a pattern to apply to the currently selected chart series or column. Click on each series or column in turn and apply a pattern to it. When you are done, you can print your chart as usual.

Installed add-ins are managed automatically by Excel so you will find that the add-in will still be there and accessible next time you use Excel.

If you are using Excel 2010 you don’t need this add-in as the pattern fills are back where they should have been all the time.

Helen Bradley

Related posts:

  1. Excel – print charts in black and white
  2. Excel charts – create an overlapping series
  3. Excel multi color column charts
  4. Multi colored Excel charts
  5. Selecting chart elements in Excel 2007