Office Excel 2007: Missing your “Pattern Fill” Feature?

56 Replies

Andy Pope’s “Pattern Fills”, which has some different functionality from Eric Patterson’s add-in.

I admit that when I first went to Office 2007, I personally found “The Ribbon” quite daunting, but I ultimately found Microsoft’s logic – that there are features in Office that you wouldn’t even know you had with the old menu system, which are easily discovered with “The Ribbon” – to be true.

Even having arrived at the conclusion fairly quickly, it took a lot of ‘accustomization’ and tweaking of the Quick Access Toolbar to make Office 2007 or 2010 easy enough for the real advantages to become appreciable.

I made a fairly quick jump to Office 2010 because Office 2007 lacks some key advantages of 2010; particularly “Track Change” in Excel and PowerPoint.

Apparently, Microsoft Excel 2007 also lacks a key feature from Excel 2003: Pattern Fill for charts and graphs. This is a big deal if you print your charts or graphs in black and white, instead of color.

Today my wife, the “Anti-Ribbonist”, called me with a surprising question: How do you do Pattern Fill for a graph in Excel 2007? It seems that my wife’s lab people work on Excel 2007, and they couldn’t convert her Excel 2003 charts from color to monochromatic patterns.

I thought that this would be easy to figure out … until I searched the web and found that a lot of other people had the same questions, and answers were scarce and often confusing.

So as a public service, I decided to report my solution, and how I implemented it.

NOTE: When using these particular “Pattern Fill” add-ins, the “Undo” feature may not work with it. Or it may. On different machines, I’ve noticed it both ways. There’s probably a difference in settings of which I’m unaware.

In any case, on a site called “Dose of Excel”, I found this page. (http://www.dailydoseofexcel.com/archives/2007/11/17/chart-pattern-fills-in-excel-2007/

It directed me to this:“Microsoft’s Eric Patterson addressed this problem by creating an add-in. You can download it here: Chart Pattern Fills.”  An alternative add-in his by Andy Pope, here. This procedure will work fine for either one, depending on which add-in’s functionality you prefer.

This is how your Ribbon will look after installing PatternUI.

Using Eric Patterson’s add-in as an example, here’s the solution I found to the Chart Pattern Fill, and how I implemented it.

  1. Go to this page on the Excel Blog: Chart Pattern Fills
  2. Left-click on the download link: http://officeblogs.net/excel/PatternUI.zip
  3. Save the ZIP file to a location of your choice.
  4. Unzip the file. It will create its own subdirectory with a file called “PatternUI.xlam”.
  5. Open Excel.
  6. At the end of the “Quick Access Task Bar” is a small down-pointing arrow. It’s a drop-down menu for adding commands to the Task Bar.
  7. Left-click on “More Commands”.
  8. At the top of the new window, left-click on the “Choose Commands From” drop-down menu
  9. Choose “All Commands”
  10. Scroll down the command options until you find “Add-ins…” Left-click on it
  11. Between the two columns (Optional commands on the left, and chosen Task Bar commands on the right) are two buttons. Left-click “Add”.
  12. On the bottom right, click “OK”, and the window will close.
  13. Your “Add-ins” button will now be on your Quick Access Task Bar. Click it.
  14. You will be given optional add-ins to check off and add to your Ribbon. Instead, choose “Browse”.
  15. Browse to where you have saved your PatternUI.xlam file.
  16. You may have to select the “all files” option to see it.
  17. Click on the file and highlight it, then click “Save”.
  18. This should return you to the small “Add-ins” box, with PatternUI already checked off.
  19. Click “OK”.
  20. Now, when you click on your Excel 2007 chart, there should be a tab at the very top/right called “Chart Tools”. Below it, there is a “Format” tab. Click on that.
  21. You will see the ‘cross hatch’ icon for your Pattern Fill gray tones, along with color selections.

See? Wasn’t that easy?

This will probably work for other 3rd party add-ins if you want to integrate them into Office.

Note: This article was updated on 7/12/11 in order to insert Andy Pope’s add-in link, and to further encourage the reader to seek additional options for Pattern Fill or other optional  functionalities.

56 thoughts on “Office Excel 2007: Missing your “Pattern Fill” Feature?”

  1. Pingback: Office Excel 2007: Missing your “Pattern Fill” Feature?

  2. Rebekah Sims Gilbert

    Is there any hope for those of us using Office2008 for Mac? I get an error telling me that “Visual Basic macros do not work for …. Mac” when I try to install these Add-Ins. Any suggestions, besides “make a pattern in Word then tile it”?

    Thanks in advance!

    Like

    Reply ↓
    1. ThinkWingRadio Post author

      I’m far from a Mac maven, but your question piqued my curiousity. On a quick search, I turned up this article: “Converting VBA Macros to AppleScript in Microsoft Office ” (Link here: http://www.mactech.com/vba-transition-guide/index-001.html) A key statement from the article is, “Most Mac users will want to convert their macros to AppleScript as VBAthere‘s not only rich AppleScript in Office now, with more to come, but also it gives the huge benefit of being able to weave Mac OS X and other non-Microsoft applications into one‘s solutions.”

      Whether or not this article is specifically helpful, you might search for VBA-to-Apple Script conversion tools to make the job less painful or tedious.

      If you find a solution, please let me know. If you write it up, I’ll post it.
      Mike

      Like

      Reply ↓
  3. Excel 2010 problem

    Hello, I am using Excel 2010 and have installed the add-in successfully. However, I can only have gray color pattern. Is this any chance that I can have the patterns with different colors? Thanks a lot in advance!

    Like

    Reply ↓
    1. ThinkWingRadio Post author

      In my article, 2 different add-ins are mentioned. Whichever you installed, install the other one. ;-)
      ____________________________
      (Update, 10/18, 2011): Looking again at the article, I’d like to be a bit more specific.

      Andy Pope’s “Pattern Fills” is more versatile in terms of color. “PatternUI” is a bit more user friendly, as I recall. My wife needed patterns so that her color graphs would be understandable when printed in B&W, so she installed PatternUI.

      I can see how Andy Pope’s add-in is in some ways superior. It’s a personal user judgment.

      Like

      Reply ↓
  4. Neil Carroll

    What is truly bewildering about this is that Microsoft removed a feature that is required by everyone who wants to display a chart in black and white … maybe 90% of the world ‘out there’. It is the greatest example of ignorance and arrogance that I’ve come across from a supposedly “world-aware” company. (How many color copiers in Africa .. India ..?) Staggering.

    Like

    Reply ↓
    1. ThinkWingRadio Post author

      Thanks for writing, Neil. Yes, I totally agree. I don’t know what Microsoft was thinking when they omitted this feature. Was it an “Oops” moment that somehow slipped through all the beta checks?

      I was stunned when my wife came to me with this problem in Excel 2007. The article I posted here is almost exactly what I wrote for her and her lab staff, all of whom had the same problem. Posting the article here was actually an afterthought; I decided in passing that there must be other people out there with the same problem, and I’d already gone through the effort to research and write it.

      As it turns out, this single article has become one of the most popular click-throughs on my site since it was published.

      I’m glad if it helped you.

      Mike

      Like

      Reply ↓
  5. Grainne

    Is there a way to automaticaly update a chart – so that each slice had a different cross hatch (vary by date point?)

    Like

    Reply ↓
  7. Wesley

    So I’ve downloaded and installed the pattern fill add-in, but this does not seem to work for graphs where you must use the ‘shape fill’ tab to fill in the shape. There is not an option for pattern fill, just gradient, picture, or texture. How can I add the pattern option to this?

    Like

    Reply ↓
  10. Phil

    Thank you! I was shocked to discover these were missing from Excel 2007 and 2010. I had found Eric Patterson’s article, but didn’t try to execute it, as it looked too complicated. Thanks for making it accessible!

    Like

    Reply ↓
      1. ThinkWingRadio Post author

        Yeah, That’s the strange thing. Why Microsoft decided to skip a generation and remove this feature from Excel 2007, and then include it back into Excel 2010 is a real mystery.

        Like

  15. MI

    Strangely the Pattern feature is available for shapes in Word 2007 but not Excel 2007. You can create a shape in Word, apply a hatch then copy it across, but this doesn’t help for graphs!

    Like

    Reply ↓
  17. Naira

    Thanks for your help with this! I also can’t believe that Microsoft would omit this very important feature in the 2007 version. I did encounter a problem though that I hope someone can help me with. I was able to add Add-ins to the quick access toolbar, and browsed to the PatternUI excel file, which showed up as ticked box thereafter, but after I clicked on OK Excel freezes and I cannot click on the graph or anywhere in the spreadsheet, and no addition to the ribbon occurs. Any suggestions would be appreciated. I did restart my computer and this didn’t work. Thanks in advance!

    Like

    Reply ↓
    1. Naira

      Problem solved – the drop-down box appears on a new excel document but not in my previously created ones. Thanks again for this page!

      Like

      Reply ↓

  21. Pingback: Christian Round Winners [Turing 2013]

  23. Pingback: Christian Round Winners [Turing 2013] | Mobile Atheist

  28. Talychka

    Can you please explain how to vary the patterns by data point – I’m using bar charts and want to have different patterns to differentiate without having to go to each bar and select a different pattern for each – is there a way to do it automatically?

    Like

    Reply ↓
  31. Dianna

    Wow! This information is great. I was trying to insert a pattern fill into a text box but all the selections were blank. One of the comments made my day when they mentioned adding patterns to shapes instead! I’m using Office 2013.

    Like

    Reply ↓

Leave a comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.