Format Painter is one of the most used tools to copy formatting in Excel. Format Painter drastically reduces the load of formatting for each cell manually. You won’t ever return to manual formatting after you learn how to use the Format Painter.
In this Excel tutorial, you will learn everything about Format Painter and how to use Format Painter to copy formatting.
As you can see in the image below, we have copied the formatting from the column ‘Sales” to the columns “Expenses” and “Profit”.
What is Format Painter?
In Microsoft Excel, the Format Painter is a powerful tool that allows you to copy the formatting from one cell or range of cells and apply it to another.
The Format Painter is a quick and efficient way to replicate formatting without having to manually adjust each cell. It’s convenient when you want to maintain a consistent look across different parts of your Excel worksheet.
To find the format painter:
- Navigate to the “Home” tab in the Ribbon.
- Look for the “Format Painter” icon. It usually resembles a small paintbrush.
Copying the Formatting from a Cell or Range Using Format Painter
If we have a dataset with a column “Sales” that contains the desired formatting. We will copy this formatting to the columns “Expenses” and “Profit” with Format Painter.
To copy formatting using Format Painter:
- Select the cell with the desired formatting that you want to copy.
- Click on the Home tab > Format Painter.
- Select the range where you want to apply the formatting.
- Release the button on the mouse.
This will apply the formatting to the selected range of cells.
Note: You can either press the “Esc” key or click on the Format Painter button again to deactivate Format Painter.
Read More: [Fixed!] Format Painter Not Working in Excel
4 Cases of Copying Formatting Using Format Painter
Case 01: Copying the Formatting of Shapes in Excel
You can also copy the formatting of shapes easily.
To copy the shape’s formatting, follow the steps below:
- Select the shape with the desired formatting.
- Go to the Home tab > Format Painter.
- Again, select the shape where you want the formatting to be applied.
- Release the mouse button to apply the formatting to the selected shape.
This will copy the desired formatting and apply it to your desired shape, as shown in the image.
Case 02: Copying the Formatting from Different Sheet
To copy formatting from a different sheet using the Format Painter in Excel, follow these steps:
- Open both sheets.
- Ensure that both the source sheet (containing the cell with the desired formatting) and the destination sheet are open in your Excel workbook.
- Go to the source sheet and click on the cell with the formatting you want to copy.
- Go to the Home tab > Format Painter.
- Navigate to the destination sheet where you want to apply the formatting.
- Click on the cell or drag over the range in the destination sheet where you want to apply the formatting.
- Release the mouse button.
This will copy the formatting from one sheet to another, as shown in the image below.
Note: If you want to apply the formatting to multiple locations, you can double-click on the Format Painter button instead of clicking it once. This keeps the Format Painter active until you manually turn it off.
Read More: How to Use Format Painter Shortcut in Excel
Case 03: Copying Conditional Formatting with Format Painter
Copying conditional formatting using the Format Painter is a straightforward and user-friendly process. With a simple click, the Format Painter allows you to duplicate complex formatting rules without the need for in-depth Excel expertise.
Assuming we have a dataset that contains “Student Name” and marks in various subjects. We have applied conditional formatting in cells of the column “Physics” where the mark is greater than 40.
To copy this conditional formatting and apply it to the other column:
- Select the range of cells that contain the desired conditional formatting.
- Go to the Home tab > Format Painter.
- Select the range where you want to apply this formatting.
- Release the mouse button, and this will apply the conditional formatting to the selected range.
This will copy the desired conditional formatting to the range.
Case 04: Copying Formatting from One Column to Another Column Row-by-Row
In those previous cases, the Format Painter does not copy the column width for each cell.
To copy formatting along with column width, follow the steps:
- Click on the desired column header (e.g., Column C) from where you want to copy the desired formatting.
- Go to the Home tab and hit Format Painter.
- Click on the column header where you want to apply the formatting (e.g., Column E) along with the column width.
- Release the button on the mouse, and you’re done—the formatting is applied instantly!
Note: You can also use the Fill Handle or Paste Special feature to copy the formatting.
Download Practice Workbook
Download the following practice workbook. It will help you understand the topic more clearly.
Conclusion
Finally, we are at the end of the article. I sincerely hope that this article was able to guide you on how to use Format Painter to copy formatting in Excel. Please feel free to leave a comment if you have any questions or recommendations for improving the article’s quality.
Frequently Asked Questions
What is the shortcut in Excel to copy formatting?
Click on the cell with the format > press Alt+H+F+P to activate Format Painter > click on the cell where you want to copy the formatting.
Can I use Format Painter to copy formatting between different workbooks in Excel?
Yes. Simply open both workbooks and use Format Painter as you would normally.
Is Format Painter used for copying formatting?
Yes, that’s correct. The Format Painter tool is used for copying formatting from one place (such as text or an object) and applying it to another. It’s a time-saving feature in many applications, including Microsoft Word, Excel, and PowerPoint.
Related Articles
- How to Use Format Painter in Excel for Multiple Sheets
- How to Use Format Painter for Multiple Rows in Excel
