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.
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|
|Lesson 7 - Excel Copying with the Fill Handle|
|Lesson 8 - Excel Paste Special|
|Lesson 9 - Excel Insert Command|
|Lesson 10 - Excel's default options|
|Lesson 11 - Excel's Undo and Redo|
|Lesson 13 - Excel's Dates and Times|
|Lesson 14 - Excel's Custom Formats|
|Lesson 15 - Excel Formulas|
|Lesson 16 - Excel Cell References|
|Lesson 17 - Excel: Avoid Typing|
|Lesson 18 - Excel Formulae Arguments & Syntax|
|Lesson 19 - Excel Autosum Formula|
|Lesson 20 - Excel Auto Calculate|
|Lesson 21 - Excel's Insert Function|
|Lesson 22 - Excel's Useful Functions|
|Lesson 23 - Excel's Named Ranges|
|Lesson 24 - Excel's Constants and the Paste Name Dialog|
|Lesson 25 - Excel's Calculations|
|Lesson 26 - Excel Comments Cell|
|Lesson 27 - Excel Find and Replace|
|Lesson - 28 - Clear Excel Cell Contents|
|Lesson 29 - Effective Excel Printing 1|
|Lesson 30 - Effective Excel Printing 2|
|Lesson 31 - Sorting in Excel|
|Lesson 32 - Hide/Show Row/Columns in Excel|
|Lesson 33 - Auto-Formats in Excel|
|Lesson 34 - Creating a Basic Excel Spreadsheet|
|Lesson 35 - Excel Charting Lesson: The Basic Excel Spreadsheet|
|Lesson 36 - Excel Worksheet Protection|
|Lesson 37 - Excel IF Formula Nesting|
|Lesson 38 - Excel Function Now/Today Formulas|
See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets
Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.
Microsoft ® and Microsoft Excel ® are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft