MSExcel: Replace Fancy Chart Designs with Stnd Black and White Patterns


Introduction: The purpose of this article is to provide the MSOffice 2007 users with the method to use the old fashion black and white chart patterns that were omitted by Microsoft.  While it requires a few extra steps, Microsoft did provide us with the means to do this.

While the steps below seems a bit odd at first, I think when you practice them you will find them as easy as “pie” to execute.

Additionally,  if i am not mistaken, the steps below have not been published by Microsoft or Microsoft MVP’s or anyone else on the net.  So i hope this article and experience will benefit all MSOffice2007 users that need black and white charts for printing via black and white printers.

Problem: Microsoft deliberately omitted the chart patterns, aka svg patterns, from MSOffice 2007 modules citing that black and white patterns were not popular.  However it is unclear where they ascertain this ideology.  But the feature has been re-introduce in MSOffice 2010.

Solution: The steps below is a generalized method to add those black and right patterns to your charts.  At this time, if you are now working on a highly complex report, please make a back up copy of it before executing the steps below. But better yet, practice on blank documents first.

Step 1:  Create a plain / blank document.

Step 2:  Insert a chart on the blank document. It can be any, but use a pie chart that will sync with the steps below.

PCSmartiesPatternizeChart1

Step 3: Next insert a couple of blanks spaces/lines between the top of the chart and top of the page or header or margin, so that the chart will move down and inch or so.  (the next image below is an accumulation of the following steps)

Step 4: In the blank area you created  insert a “shape”.  For this exercise i used a rectangle. Be sure its a “rectangle” from the “shapes” section and not a “text box”, ie rectangular text box.

Step 5: Next fill that empty shape with your desired pattern and apply it via step A or step B.

  • Step A: Right click on shape > format autoshape > colors&lines > fill effects > pattern
  • Step B: Left click on shape > format menu option > shape fill > gradient > more gradients > pattern

PCSmartiesPatternizeChart2

Step 6: After the shape is filled with the pattern, next click on the shape and copy the shape and it will be transferred into the “Clipboard”, ie into the computers memory.

PCSmartiesPatternizeChart3

Step 7:  Next click on one of the “series” of the pie chart and get the “Format Data Point” option.  This can sometimes be tricky to get.  But in my case, it requires 1 click of the mouse, wait a second or two, then click again.

Step 8: After clicking the “Format Data Point”, its properties window will open. Now go to to “Picture or Text Fill” and  click on the “ClipBoard Button” that is provided on the properties window.

PCSmartiesPatternizeChart5

Step 9: Close the options window and you will now find that the steps above have added a pattern to one of the data series.

In Conclusion: While it would be easier just to fill the the data series with patterns, like in MSOffice2003, the steps above are not that difficult to execute.   It now just requires the extra steps of selecting a pattern via the insert shape feature then putting that shape into the clipboard.  Then once in the clipboard, filling the data series with it is a simple procedural process.  Incidentally, in step 8 you had to click on the “clipboard button” at first.  But afterwards, Office will automatically fill the datapoint in the future without clicking the clip board button.

Tip 1:  Keep in mind that once your desired pattern is in the clipboard, it will stay there until it is replaced with a new pattern or until the clipboard is emptied.   So while the pattern is temporarily committed to memory, you can apply itto other charts displaying that same data series.

Tip 2:  In step 5 above, you can customize the pattern before committing it to the clipboard.  Customization includes changing its color scheme via the same window where the patterns are provided.  But keep in mind, that the colors you select will wash out when printed on a black and white printer.  And then if the black and white hard copy is zerox, then the subtle shades will certainly be washed away even more.   So avoid shades of colors and shades of grey that are too closely related and instead use extreme shades of colors and grays to make your data series distinguishable from each other.

Tip 3: While the steps above is performed in Word, the steps should also be applicable in the other MSO2007 modules as well, since this is OLE that is engineered by Microsoft into its products.

Tip 4:  Create your own pattern template and save it for future use.  In other words, create a blank document and insert a half dozen or so of the rectangular shapes.  Then patternize each shape with a unique pattern and label each rectangle with the name of the data series the pattern is assigned to. Then save your new template.   Because of Windows OLE,  you can then copy the pattern from your open template then flip over to the open report and fill the charts in them with ease and for continuity.

~ happy charting

🙂

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s