EXCEL'S FORMAT PAINT

Lesson 12 - Using the Format Painter to Copy a Cells Format

THE FORMAT PAINTER

When working in Excel there are times when you may wish to copy the formatting of a cell or range of cells to another location without copying the content of the cell(s). To make this simple, Excel has what is known as the Format Painter. This can only be found on the Home tab, is represented on the toolbar by a paintbrush and is immediately to the left of the Undo icon. To be honest this is much the same as using the Paste special and selecting Formats as the option. The big advantage is that it’s much easier and quicker to access and the copy and paste range do not have to be of the same shape and size. So you can get a feel for this feature try these two simple exercises.

1. Type any number in cells A1:A5.

2. Type any numbers in cells B1:B10.

3. Now highlight (select) cells A1:A5 and click the $ (dollar icon) on the Formatting toolbar.

4. Now click the B (Bold) and I (Italic) icons, also on the Formatting toolbar.

5. Click the Format Painter icon and then click cell B1.

You should now have painted the formatting we applied to cells A1:A5 to B1:B5. You would also have noticed there was a paintbrush symbol next to our mouse pointer, which disappeared once we had selected cell B1.

Now Using The Undo Feature Undo Steps 3 To 5 So That All We Have Is The Unformatted Numbers In A1:A5 And B1:B10.
Now Again Apply The Same Formatting To These Cells; Eg. $, Bold And Italics, A1:A5. With Cells A1:A5 Selected, Double Click The Format Painter
Again Click B1 And The Formatting Should Be Painted To Cells B1:B5. The Difference This Time Is That The Paintbrush Symbol Should Still Be Present. This Is Because We Double Clicked The Format Painter Instead Of Single Clicking It
Click Cell B6 And Cells B6:B10 Will Also Have The Formatting Applied.
To Clear The Format Painter, Either Single Click The Format Painter Icon Or Push Esc On The Keyboard.

You can see a single click will apply the Format Painter once, while a double click will keep the Format Painter active until we either click the icon again or push Esc.

We mentioned above that the copy and paste range do not need to be of the same shape and size when using the Format Painter, to see this follow these steps.

1. Type any numbers in cells C1:G1.

2. Select cell A1 and then single click the Format Painter.

3. Now select cell C1 and with the left mouse button still held down drag to cell G1 and then release.

You should now have the same formatting applied to cells C1:G1 as you have in A1.

So as you can see the copy and paste range does not need to be of the same shape and size, unlike the Paste special.

Go back to:

Lesson 1 - Excel Fundamentals

Lesson 2 - Starting Excel and Excel Workbooks

Lesson 3 - Excel Toolbars and Task Panes

Lesson 4 - Excel Worksheets

Lesson 5 - Excel Cells and Navigating a Worksheet

Lesson 6 - Excel Cut/Copying and Pasting Data