How to Use Format Painter to Copy Formatting in Excel (5 Ways)

Format Painter is one of the most used tools to copy formatting in Excel. Format Painter drastically reduces a 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 article, we are going to learn 5 simple methods to use the Format Painter to copy formatting in Excel.

Download Practice Workbook

Introduction of Format Painter in Excel?

Format Painter is one of the most versatile tools of Excel. By using the Format Painter, we can format a countless number of cells just with one click. Imagine the pain of formatting each and every cell of a spreadsheet manually. This is not only a tiresome job, but it will also consume a huge amount of time. But no worries! The Format Painter is here to the rescue. It can copy the formatting of a cell to any number of cells while keeping the cell values of each cell unchanged.

5 Simple Methods to Use Format Painter to Copy Formatting in Excel

In this section of the article, we will discuss the detailed steps to use the Format Painter to copy formatting in Excel.

Not to mention that we have used Microsoft Excel 365 version for this article, you can use any other version according to your convenience.

1. Using Format Painter for a Single Cell

By using Format Painter, we can copy formatting for a single cell with just one click.

In the following dataset, we have a Targeted Formatting column, which is properly formatted. And some Unformatted Cells. In those Unformatted Cells, we need to copy the formattings of the Targeted Formatting column.

Let’s follow the steps mentioned below to copy the formatting using the format painter for a single cell.

Steps:

• Firstly, select cell B5 as marked in the following image.
• After that, go to the Home tab from the Ribbon.
• Following that, select the Format Painter option from the Clipboard group.

• Next, click on cell C5 as marked in the image given below.

Consequently, you will see that the cell of the Unformatted Cell column has the same formatting as the cell of the Targeted Formatting column.

By following the same steps, we can get the rest of the data as depicted in the following image.

2. Utilizing Format Painter for a Range of Cells

For a range of cells, utilizing the Format Painter to copy the formatting, is a fast and efficient method. In the following dataset, we have a formatted Sample Column and an Unformatted Column. We need to copy the formatting of the Sample Column to the Unformatted Column by using the Format Painter.

Let’s follow the steps mentioned below to copy formatting using Format Painter.

Steps:

• Firstly, select the data from Column B.
• After that, go to the Home tab from the Ribbon.
• Now, click on the Format Painter option from the Clipboard group.

• Subsequently, click on cell C4 as marked in the following picture.

Consequently, you will see that the formatting from Sample Column is copied to the Unformatted Column.

3. Applying Format Painter to Copy Conditional Formatting

Format Painter is a versatile tool of Excel. With the help of Format Painter, we can also copy the Conditional Formatting into the targeted cells.

In the following dataset, we have Marks of some Students for 3 subjects. Here, if the marks are higher than 80, then the cell color will be Green. On the other hand, if the marks are lower than 40, then the cell color will be Red.

Let’s follow the below-given steps to copy formatting using Format Painter.

Steps:

• Firstly, select the data of Column C as marked in the following image.

• Following that, go to the Home tab from the Ribbon.
• After that, click on the Format Painter option.

Note: You can also select the data of Column D, as they have the same Conditional Formatting Rules.

At this stage, the formatting from Column C is copied.

• Now, click on cell E6 as marked in the following picture.

Consequently, the conditional formatting should be copied to Column E as shown in the following image.

4. Using Format Painter for Non-Contiguous Columns

In simple words, Non-Contiguous Columns are columns that are not joined together. We can use the Format Painter to copy formatting for such disjointed columns.

In the following dataset, we have Marks of some students in 3 subjects. But here all the cells are not formatted properly. Now we will use the Format Painter to copy formatting for the rest of the cells altogether.

Note: We have used the same conditional formatting here as we had used in Dataset 3.

Now, let’s learn the steps mentioned below to do this.

Steps:

• Firstly, select the data from Column C as shown in the image given below.

• Afterward, go to the Home tab from the Ribbon.
• Subsequently, double-click on the Format Painter option as marked in the image given below.

Note: You can also select the cells from Column E as they have the same Conditional Formatting Rules.

As a result, formatting of the Column C is now copied.

• Now, click on the marked cells shown in the following image.

At this stage, you will see that all the cells are now with proper formatting as marked in the following screenshot.

5. Utilizing Format Painter to Copy Shapes in Excel

By utilizing the format painter we can copy any shapes in Excel in a flash.

In the following dataset, we have a Sample Shape that is properly formatted. On the other hand, we also have an Unformatted Shape. We need to copy the formatting of the Sample Shape to the Unformatted Shape.

Now, let’s follow the steps mentioned below to use the Format Painter to copy the formatting of the Sample Shape.

Steps:

• Firstly, select the Sample Shape.
• After that, go to the Home tab from the Ribbon.
• Subsequently, click on the Format Painter option.

Now, the formatting of the Sample Shape is copied.

• Next, click on the Unformatted Shape.

Congratulations! You have successfully used the Format Painter to copy the formatting of the Sample Shape to the Unformatted Shape.

Conclusion

Finally, we have to the end of the article. I sincerely hope that this article was able to guide you to use the Format Painter to copy formatting in excel. Please feel free to leave a comment if you have any queries or recommendations for improving the article’s quality. To learn more about Excel, you can visit our website, ExcelDemy. Happy learning!