How to Use the Format Painter in Excel: Copy Formatting (2023)
Cell formatting is fun — it lets you change the text alignment, orientation, cell borders and even lock your cell.
But what if you need to format more than one cell? Formatting each cell individually can be too time-consuming and tiring.
Well, the Format Painter tool is here to help you with all your formatting problems now. 😃
It looks like a paintbrush, and you can format multiple cells in one go using format painter. If you seek to know all about it – continue reading the article.
Download our free sample workbook here to tag along the guide.
Table of Contents
How to Copy Formatting with the Format Painter
Using the Excel Format painter is pretty simple.
Say you have two columns, and you want to copy the formatting of the selected cell.
In the image below, we want to copy the formatting of cell A2 and paste it to the adjacent column B.
To copy the formatting:
1. Select the cell.
2. Click on the format painter button on the home tab in the ribbon. The paintbrush icon represents it.
The cell gets selected, and a moving border appears around the cell, highlighting it.
3. Select all the cells you want to paste formatting to while dragging the mouse pointer.
4. Release the mouse button, and the copied formatting appears on the selected range of cells as:
In our case, the selected cells’ font size, style, and typeface change to the format of cell A2. The data entered in the cells remain intact.
3 Format Painter Tricks in Excel
Below are some ingenious format painter tricks to copy formatting in Excel. And we can bet you don’t know some of them. 😎
How to Use the Excel Format Painter on Multiple Non-adjacent Cells
Okay, so you can copy formatting in Excel using Format painter, But how do you copy the format to non-adjacent cells?
Well, here’s a trick for that.
1. Select the cell you want to copy the formatting of.
2. Double click format painter option on the Home Tab.
3. Select the non-contiguous cells you want to paste formatting to.
4. The formatting appears.
How to Use Format Painter on an Entire Column
The Format painter option works well when copying formats of multiple cells. But what if you want to copy the formatting to an entire column or row?
There are two common ways to copy the formats of one column to another. One uses the Format Painter, while the other uses the Paste Special option.
1. With the format painter, select a single cell with the desired format.
2. Click the paintbrush icon to copy the formatting.
3. Paste the formatting to multiple rows or the entire column by clicking on its heading.
4. The formatting appears in the column.
5. If you want the column width to be copied as well, ensure the sample column has the same formatting overall.
6. Select the sample column heading and click the format painter button.
7. Click the heading of the target column.
8. The formatting, including the width, appears.
You can also use the Paste Special method.
9. On the same workbook, select the cell heading comprising the formatting.
10. Press CTRL + C or Copy button to copy formatting. A moving border pops up.
11. Select the heading of the column you want to paste formatting to.
12. Right-click on the selected range of cells to paste the formatting.
13. Click on the Paste Special formatting option.
14. The Paste Special dialog box pops up. Click the Formats option.
15. The formatting appears.
Format Painter Shortcuts
While all these methods are straightforward, they require the mouse cursor turns frequently. Some people prefer working with the keyboard at all times.
Unfortunately, Excel does not offer any particular shortcut for formatting cells. However, you can use some keyboard shortcuts to perform tasks.
Format Painter Keyboard Shortcut
- Select the cell with the desired formatting.
- Press Alt + H – to activate the Home Tab.
- Release the Alt button.
- Press F and P – to activate the Format painter.
- The Formatting icon becomes active.
- Click on the targeted column heading to paste the formatting.cuts to perform tasks.
Paste Special Keyboard Shortcut
You can also copy formatting in Excel using the Paste Special shortcut, similar to conditional formatting.
- Select the cell or cells with the desired format.
- Click CTRL + C to copy the formatting.
- Choose the cell or cells where the formatting is to be posted.
- Press CTRL + Alt + V. The Paste Special dialog box appears.
- Choose Formats, and the formatting is done.
If you are working with the 2013 or 2016 version, you can try the following shortcut:
- Follow the above procedure till before the shortcut keys.
- Press Shift + F10.
- Keeping holding the Shift key and press S + R.
- The formatting appears.
For a little insight, the Shift + F10 shows the dropdown menu. The Shift + S chooses the Paste Special option, and the Shift + R applies the formatting.
Copy Formatting with Copy Paste
You can copy the formats of a cell with the help of copy-paste as well.
1. Select the cells whose formatting you want to copy.
2. Copy the formatting of the cell.
3. You can press CTRL + C to copy the cell’s formatting or press the Copy icon under the Home Tab.
4. Once the highlighted border appears around the cell, click on the cell you want to paste formatting to.
5. You can click the paste button on the Home Tab in the Clipboard group or open the paste menu from the right-click.
6. Click the Paste Special option at the bottom of the paste menu.
7. You can do the same using the right-click context menu.
8. In the same worksheet, choose the Paste Special option.
9. The Paste Special dialog box appears.
10. Click on the Formats option under the Paste group on the Paste Special dialog box. This will paste the new formatting while keeping the cell values intact.
11. Press Ok.
12. The formatting appears.
Copy Formatting With the Fill Handle
The fill handle is more than often used for copying data or formulas down to the last cell. But did you know it can copy formatting as well?
Copying formatting using a fill handle is by far the most interesting method to copy formatting in Excel. You can do it in less than 5 seconds! 😁
1. Select the cell you want to copy the formatting of.
2. You will see a small square at the bottom right corner of the selected cell.
3. Hovering the mouse cursor turns it into a black cross.
4. You can either double-click the fill handle or drag it down to copy the series. The value of the first cell will be copied till the last cell.
5. Click on the Autofill options box at the bottom right of the copied cells.
6. From the dropdown autofill menu, select Fill Formatting Only option.
7. The desired formatting appears with the original values of the cells.
8. You can paste values to other cells that are adjacent.
9. Drag the Fill handle to select the other column.
10. Click Fill Formatting Only from the autofill menu.
11. The existing formatting is copied to all the cells.
That’s it – Now What
In this article, we learned how to use format painter in excel. From its shortcuts to copying formats of entire columns to secret underused features.
We also saw how we could copy formats using alternate methods like Fill Handle and Paste Special.
Format painter is by far the most used tool of Excel. It can improve your Excel sheet formatting from 👎 to 👍 in seconds. However, Excel has much more to offer alongside formatting.
Like the fundamental VLOOKUP, IF, and SUMIF Excel functions. Enroll yourself in my 30-minute free email course to learn these functions (and more!)
Other resources
Copying formatting is much more than what can be explained in this article. Learn more about copy formatting in Excel here.
Also, if you like this article, you might want to read related Excel tutorials. Like how to Wrap Text in Excel, fix Excel Formulas, delete Blank Rows, and much more.