Lesson 12 - Excel's Format Painter

< Back to Search results

 Category: [Excel]  Demo Available 



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


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.

  1. 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.
  2. 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
  3. 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
  4. Click Cell B6 And Cells B6:B10 Will Also Have The Formatting Applied.
  5. 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
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

See also:

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



stars (0 Reviews)