Hint 1 - The Excel Format Painter
The Format Painter is one of the more useful Excel tools. You will find
the Format painter button on the Standard Menu Bar in Excel 2003 or the
Home Ribbon in Excel 2007. The Format Painter button looks like a
mini version of the image shown at the left and makes it easy to copy
the format (font, colors, size, weight,
background and border form and colors, etc.), from a selected worksheet
cell or group of cells, to another cell or group in the worksheet.
Most people figure out by themselves how to start using the Format painter tool - click the cell with the format you want to copy to another cell, click the Format Painter button, then click the cell you want to re-format, and bingo it's done. A good tool that one!
Hint 1.1 - You can also select a group of cells and copy the format(s) of all of them. - Here is how you do it:
- Select a rectangular group of cells by clicking a cell at one end of the group and holding the left mouse button down while moving the mouse across the group of cells, then letting go of the mouse button (this is called "rubber banding").
- You can actually select cells in one or more rows, but remember that the formatting you will copy will start from the top-left-most cell and the group must be in the shape of a rectangle. (In geek speak, the cells must be contiguous - adjoining each other.)
- You can also select a group, by first clicking the cell at one corner of the group, holding down one of the "Shift" keys, then clicking the cell at the diagonally opposite corner of the rectangle.
- Next click the Format Painter button.
- Then click the top left cell of the group you want to re-format.
- You can also hold down the "Ctrl" button while you click various cells to be selected for the Format Painter, but the cells must be contiguous, or only the format of the top left cell of the selected group will be used.
Hint 1.2 - You can copy format(s) from one worksheet to another. - Select a cell or select a rectangular group of cells, click the Format painter button, select another worksheet in the active workbook or select another open workbook
Hint 1.3 - Double click the Format Painter button to use it repeatedly - (This is a really neat feature!) Select a cell or a group of cells, double click the Format Painter button, then each time you click a cell (or the top-left of a group) the format(s) will be applied. To end this action, either press the "Esc" key, or just click the Format Painter Button a second time. (Coders please note: This will not work if you attach VBA code to the Worksheet Selection Change Event - just the single format operation repeatedly.)
Hint 1.4 - Use on cells with conditional formats - Excel Conditional formats can also be copied with the Format Painter tool from a cell or a group of cells. This can be a real time saver because of the number of steps needed to set up conditional formats.
Hint 1.5 - Word Format Painter Tool - You will also find a Format Painter tool in Microsoft Word and some other programs. It works in much the same way as the Excel tool. (except it works on single elements of text rather than cell groups and cells), select a sample, then single or double click the Format Painter button and apply the format as required.
Fellow Excel Users: If you discover other useful Format Painter tool behaviors, e-mail me about them .... Barry