How to Use Format Painter in Excel (7 Ways)

Whenever you need to copy format in Excel the Format Painter tool is the best to do it. It is used to change a string or cell content format with the same format of previously copied format. Format Painter in Excel allows us to copy formatting from a range of cells and paste it somewhere else it either can be in the same worksheet or other worksheets/workbooks.

To make the explanation more understandable I’m going to use a sample dataset of an online fruit store. The dataset contains 4 columns that represent fruit order and delivery details. These columns are Product Name, Order Id, Price, and Status.

to Use Format Painter in Excel


Download to Practice


7 Ways to Use Excel Format Painter

1. Using Format Painter to Copy Cell Formatting

If you want, you can use the Format Painter in Excel to copy a cell format.

Here, I want to format the Order ID column depending on the values of the Status column. I want to copy the format of the E4 cell of the Status column to apply it in the Order ID column cell.

Using Format Painter to Copy Cell Formatting

Let’s dive into the procedure,

To start with, select the cell or cell range from where you want to copy Format.

➤ I selected cell E4.

Now, open the Home tab >> select Format Painter

If you want you can use the shortcut, press ALT, H, F, P (remember to press these keys one by one) to trigger Format Painter. For more shortcut ways to use Format Painter, you can check this Format Painter Shortcut in the Excel article.

Next, you will see an icon of Format Painter.

Place the icon in the place or cell where you want to paste the copied format.

➤ I hovered the icon in the C4 cell.

Finally, click on the cell.

Using Format Painter to Copy Cell Formatting

Hence, the format will be applied to the resultant cell C4.

Read more: Formula to Copy Cell Value and Format in Excel


2. Using Format Painter to Copy Formatting to Range of Cells

Format Painter not only supports a single cell but also you can use it for the range of cells.

Here, I want to copy the format of Status column cells where the delivery status is Delivered. And I will apply it in the Order ID column.

To perform this task, I sorted the values of the Status column using the Sort command of Excel.

Using Format Painter to Copy Formatting to Range of Cells

To start with, select the cell range from where you want to copy Format.

➤ I selected cell range E4:E8.

Now, open the Home tab >> select Format Painter

Then, an icon of Format Painter will appear.

Place the icon in the place or cell where you want to paste the copied format.

➤ I hovered the icon in the C4 cell.

Finally, click on the cell.

Therefore, the copied format will be pasted in the C4:C8 cell range.

Using Format Painter to Copy Formatting to Range of Cells

Read more: How to Custom Format Cells in Excel


3. Using Format Painter in Multiple Columns

Format Painter gives us the opportunity to copy format in multiple columns or non-adjacent cells.

Here, I want to copy the format of the Product Name column to the Order ID, Price and Status column.

Using Excel Format Painter in Multiple Columns

To begin with, select the cell range from where you want to copy Format.

➤ I selected cell range B4:B13.

Now, open the Home tab >> select Format Painter (double click)

Then, an icon of Format Painter will appear.

Place the icon in the place or cell where you want to paste the copied format.

➤ I hovered the icon in the C4 cell.

Finally, click on the cell.

Using Excel Format Painter in Multiple Columns

⏩ Now, the copied format is applied to the C4:C13 cell.

Again, place the Format Painter icon on the next column and click there.

⏩ Now, the copied format is applied to the D4:D13 cell.

Again, place the Format Painter icon on the next column and click.

Using Excel Format Painter in Multiple Columns

⏩ Here, the copied format is applied to the E4:E13 cell.

Read more: How to Use Format Painter in Excel for Multiple Cells


4. Use Format Painter Row-By-Row

In case you want to copy the format row by row you can do it by using the Format Painter. Because one column may contain different types of formatting.

Here, in the Status column, I have two types of values one is Delivered, and another is Pending where different formatting is used.

I want to copy the format row by row in the Order ID column.

Use Excel Format Painter Row-By-Row

To start the procedure, select the cell range from where you want to copy Format.

➤ I selected cell range E4:E13.

Now, open the Home tab >> select Format Painter

Then, an icon of Format Painter will appear.

Place the icon in the place or cell where you want to paste the copied format.

➤ I hovered the icon in the C4 cell.

Finally, click on the cell.

Use Excel Format Painter Row-By-Row

As a result, the copied format will be pasted in the C4:C13 cell range row by row.

Read more: How to Use Excel Cell Format Formula


Similar Readings


5. Using Format Painter to Copy Conditional Formatting

Excel Format Pointer also supports Conditional Formatting while copying format.

To explain the procedure, I’m going to use the dataset of particular customer expenditure.

Here, I want to apply Conditional Formatting in the Expenditure column.

Using Excel Format Painter to Copy Conditional Formatting

To start with, select the cell range where you want to apply Conditional Formatting.

➤ I selected the cell range D4:D11.

Now, open the Home tab >> from Conditional Formatting >> select New Rule

⏩ A dialog box will pop up.

From there Select a Rule Type

⏩ I selected the rule Use a formula to determine which cells to format.

In Edit the Rule Description, provide the following formula

=D4 >=500

This formula will check the cell D4 value is greater than equal to 500 or not. If the value is equal to or greater than 500 then the format will be applied to the cells otherwise no format will be applied.

Now, click on Format to choose the format.

Using Excel Format Painter to Copy Conditional Formatting

⏩ Another dialog box will pop up to choose the format.

From there you can select any color of your choice.

⏩ I selected the color Green then clicked OK.

➤ Now, click OK on the Edit Formatting Rule dialog box.

Using Excel Format Painter to Copy Conditional Formatting

Hence, the values greater than or equal to 500 are highlighted with the selected color.

Using Excel Format Painter to Copy Conditional Formatting

➤ Again, I selected the cell range D4:D11.

Now, open the Home tab >> from Conditional Formatting >> select New Rule

⏩ A dialog box will pop up.

From there Select a Rule Type

⏩ I selected the rule Use a formula to determine which cells to format.

In Edit the Rule Description, provide the following formula

=D4 < 500

This formula will check the cell D4 value is less than 500 or not. If the value is less than 500 then the format will be applied to the cells otherwise no format will be applied.

Now, click on Format to choose the format.

⏩ I selected the color Red to format the cell values.

Finally, click OK.

Using Excel Format Painter to Copy Conditional Formatting

Therefore, the formula is applied to the selected cell.

Now, I will use the Format Painter for Points column.

To start with, select the cell range from where you want to copy Format.

➤ I selected cell range E4:E11.

Now, open the Home tab >> select Format Painter

Next, an icon of Format Painter will appear.

Place the icon in the place or cell where you want to paste the copied format.

➤ I hovered the icon in the E4 cell.

Finally, click on the cell.

Therefore, it will paste the format in the E4:E11 cell range along with Conditional Formatting.

Using Excel Format Painter to Copy Conditional Formatting

Here, in the Points column the values greater than or equal to 500 are highlighted with green colors, and the values less than 500 are highlighted with red colors just by copying the format using the Format Painter.


6. Using Format Painter to Copy Shape Formatting

If you want to use Format Painter to copy shape formatting, then you are welcome to do it.

To explain the procedure, I inserted two shapes with different formats.

Here, I want to copy the format of the first shape to the second shape using the Format Painter.

Using Excel Format Painter to Copy Shape Formatting

To start with, select the shape from where you want to copy Format.

➤ I selected a rectangle shape.

Now, open the Home tab >> select Format Painter

Then, an icon of Format Painter will appear.

Place the icon in the place or cell where you want to paste the copied format.

➤ I hovered the icon in the Rectangle: Round Corners.

Finally, click on the shape.

Now, it will copy the format from one shape to another shape.

Using Excel Format Painter to Copy Shape Formatting


7. Format Inserted New Column

Almost every time we insert new columns in an Excel sheet depending on our needs. In those cases, Format Painter helps us to copy the format and give the sheet a unique format.

For the explanation purpose, I inserted some values in a new column.

Format Inserted New Column Using Format Painter

To start with, select the cell range from where you want to copy Format.

➤ I selected cell range D3:D13.

Now, open the Home tab >> select Format Painter

Then, an icon of Format Painter will appear.

Place the icon in the place or cell where you want to paste the copied format.

➤ I hovered the icon in the F3 cell.

Finally, click on the cell.

Format Inserted New Column Using Format Painter

Hence, it will paste the copied format to the F3:F13 cell.


Things to Remember

🔺 Excel Format Painter only copies the format of the cells, not the values.

🔺 You can change cell color, cell alignment, number formatting, border, etc. using the Format Painter.

The Format Painter tool supports the Conditional Formatting feature.

While using the Format Painter for non-adjacent cells or for multiple columns never forget to double click on Format Painter.


Practice Section

I’ve provided a practice sheet in the workbook to practice these explained examples.


Conclusion

In this article, I’ve explained 7 ways to use format painter in Excel. You can follow any of the methods of your choice. Lastly, if you have any kind of suggestions, ideas, or feedback please feel free to comment down below.


Further Readings

Shamima

Shamima

Hello! Welcome to my Profile. I am Shamima Sultana Rita. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to this. My last educational degree was BSc and my program was Computer Science and Engineering from East West University-Bangladesh. I am a Computer Science graduate with a great interest in research and development. I love to learn new things. Data Analysis is one of my favorite fields as I love to extract patterns based on problems. Here, I'm trying to provide enriched quality content regarding Excel basics to Advanced. Always try to gather knowledge from various sources and try to make innovative solutions.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo