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.
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.
- Go to this page on the Excel Blog: Chart Pattern Fills
- Left-click on the download link: http://officeblogs.net/excel/PatternUI.zip
- Save the ZIP file to a location of your choice.
- Unzip the file. It will create its own subdirectory with a file called “PatternUI.xlam”.
- Open Excel.
- 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.
- Left-click on “More Commands”.
- At the top of the new window, left-click on the “Choose Commands From” drop-down menu
- Choose “All Commands”
- Scroll down the command options until you find “Add-ins…” Left-click on it
- Between the two columns (Optional commands on the left, and chosen Task Bar commands on the right) are two buttons. Left-click “Add”.
- On the bottom right, click “OK”, and the window will close.
- Your “Add-ins” button will now be on your Quick Access Task Bar. Click it.
- You will be given optional add-ins to check off and add to your Ribbon. Instead, choose “Browse”.
- Browse to where you have saved your PatternUI.xlam file.
- You may have to select the “all files” option to see it.
- Click on the file and highlight it, then click “Save”.
- This should return you to the small “Add-ins” box, with PatternUI already checked off.
- Click “OK”.
- 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.
- 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.
Pingback: Office Excel 2007: Missing your “Pattern Fill” Feature?
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!
LikeLike
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
LikeLike
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!
LikeLike
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.
LikeLike
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.
LikeLike
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
LikeLike
Is there a way to automaticaly update a chart – so that each slice had a different cross hatch (vary by date point?)
LikeLike
I’m sorry. I don’t know. If I come across something, I’ll post it. In the meantime, perhaps someone else will answer your question.
LikeLike
No words to thank you sir…..it helped me lot..
LikeLike
You’re most welcome. Glad I could help.
LikeLike
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?
LikeLike
Nevermind, found an add-in that works perfect (http://www.andypope.info/charts/patternfills.htm)
LikeLike
Glad that worked for you. :)
LikeLike
Hey, thank you so much !!!
LikeLike
OMG, this is perfect. Why on earth Microsoft left patterns out is beyond words, but thank you for posting this!
LikeLike
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!
LikeLike
It’s on 2010 for sure. Just used it.
LikeLike
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.
LikeLike
Thanks!! This really saved my day.
LikeLike
I’m glad to hear it. :)
LikeLike
thank you so very much. may God bless you. thanks
LikeLike
how to download a free excel patterns software
LikeLike
Thank you! I had to greyscale a chart for a publication and was at my wit’s end!
LikeLike
I’ve heard that a lot. :)
You’re very welcome.
LikeLike
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!
LikeLike
Yeah, it was a really wierd omission.
LikeLike
Thanks that was very helpful. It works
LikeLike
Glad to be of service. :)
LikeLike
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!
LikeLike
Problem solved – the drop-down box appears on a new excel document but not in my previously created ones. Thanks again for this page!
LikeLike
Thanks for your note and the follow-up ‘fix’. :)
LikeLike
thanks
LikeLike
Works for regular charts, but doesn’t work for pivot charts. Any tips?
LikeLike
I’m sorry, no. But if you find a solution to your problem, let me know and I’ll post it here as an addendum.
LikeLike
Thank a lot brother..!
LikeLike
Pingback: Christian Round Winners [Turing 2013]
Thanks for the credit and citing link. And I’ll have to spend a bit more time reading your article. It looks interesting. – Mike
LikeLike
thankx alotttttt ,i realy need it for my thesis composing.
LikeLike
Always glad to help. :)
LikeLike
Pingback: Christian Round Winners [Turing 2013] | Mobile Atheist
Most useful!!
LikeLike
Thank you. :)
LikeLike
Thankss !!!
It’s very useful for me
Perfect for presenting graph in my report
LikeLike
That was exactly what my wife needed when I researched and wrote that. :)
LikeLike
link isnt working…y?? plz
LikeLike
Can you be specific which link isn’t working?
LikeLike
How do I remove it?
LikeLike
You should be able to ‘disable’ it by going to your Excel options menu, going to Add-Ins, search your .COM add-ins and disabling it. Does this help?
LikeLike
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?
LikeLike
I am very grateful! Thank you very much, you are making life simple.
LikeLike
Glad to have helped. :)
LikeLike
very helpful indeed. thank you!
LikeLike
I’m happy to have been of assistance. – Mike
LikeLike
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.
LikeLike
I’m surprised that this blog post is applicable in any way to Office 2013, but glad I could help.
LikeLike