How do I create a pattern in Excel 2007?

WindowsmacOS



Let's say column A contains first names, column B has last names, and you want to fill column C with first and last names combined. If you establish a pattern by typing the full name in column C, Excel's Flash Fill feature will fill in the rest for you based on the pattern you provide.

You can add emphasis to selected cells in an Excel 2007 worksheet by changing the fill color or applying a pattern or gradient effect to the cells. If you’re using a black-and-white printer, restrict your color choices to light gray in the color palette and use a simple pattern for cells that contain text so that the text remains legible.

Applying a fill color

To choose a new fill color for a cell selection, follow these steps:

1. Click the Fill Color button’s drop-down menu in the Font group on the Home tab.

   The Fill Color palette appears.

   

   Use the Fill Color button to choose a solid background color to add to selected cells.

2. Select the color you want to use from the drop-down palette.

   Excel's Live Preview lets you see what the cell selection looks like in a particular fill color when you move the mouse pointer over the color swatches before you click the desired color.

Adding patterns to cells

Follow these steps to choose a pattern for a cell selection:

1. Click the Font dialog box launcher on the Home tab (or press Ctrl+1).

   The Font dialog box launcher is that little button in the bottom-right corner of the Font group. The Format Cells dialog box appears.

2. Click the Fill tab.

   

   Select a new pattern for a cell selection on the Fill tab of the Format Cells dialog box.

3. Click a pattern swatch from the Pattern Style button’s drop-down menu.

4. Click a pattern color from the Pattern Color button’s drop-down palette.

   The Sample box displays the selected pattern and color.

5. (Optional) To add a fill color to the background of the pattern, click its color swatch in the Background Color section.

6. Click OK.

Applying a gradient effect

If you select multiple cells, the gradient you choose will be applied to each chosen cell individually instead of creating one gradient over multiple cells.

1. Press Ctrl+1 to open the Format Cells dialog box and then click the Fill tab.

2. Click the Fill Effects button.

   The Fill Effects dialog box appears, with controls that enable you to define the two colors to use as well as the shading style and variant.

   

   Use the Fill Effects dialog box to apply a gradient effect to selected cells.

3. Select the two colors you want to use in the Colors section.

4. Select one of the Shading Styles radio buttons to choose what type of gradient pattern you want to use; then click the variant that you want to use.

   The Sample box displays the current selections.

5. Click OK two times to close both dialog boxes.

You can remove fill colors, patterns, and gradients assigned to a cell selection by clicking the No Fill option on the Fill Color button’s drop-down menu on the Home tab.

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.

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.

How do you create a pattern in Excel?

How do I add patterns to an Excel chart?

How do I get Excel to recognize a pattern?

Does Excel 2007 have combo charts?