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

Get FREE Advanced Excel Exercises with Solutions!

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 article, we are going to learn how to use Format Painter to copy formatting in Excel with 5 simple methods.


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.


How to Use Format Painter to Copy Formatting in Excel: 5 Practical Examples

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

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


1. Use of Format Painter to Copy Formatting for a Range of Cells

For a range of cells, using the Format Painter to copy the formatting, is a fast and efficient method. Here you can copy the formatting for a range in two ways.

One is, selecting the formatted range manually and after clicking the Format Painter selecting the 1st cell of another range.

Another way is you can drag the cells with Format Painter to copy the formatting.


1.1 Format Painter with Selection

In the following dataset, we have a Sales column with proper formatting. But the Expenses and Profit columns are unformatted. We need to copy the formatting of the Sales column to the Expenses and Profit columns by using the Format Painter.

Here, we will select the formatted range for copying this Currency format.

How to Use Format Painter to Copy Formatting in Excel

  • Select the formatted range. Here, this range is C5:C14.
  • Now, from the Home tab >> click on Format Painter >> select D5 cell.

As a result, you will see that D5:D14 cells are formatted. Similarly, you can format the Profit column too.

Use of Format Painter for a Range of Cells

Note: In the case of a small dataset, we can format more than one column at a time. To do that, select the formatted range >> from the Home tab >> click on Format Painter >> select the ranges for formatting. You should select adjacent columns here with the same range length.

1.2 Dragging Format Painter to Copy Formatting

In our dataset, we have different Date formats in the Date column. Now, by dragging the Format Painter, we will copy a particular Date format of the B5 cell to the rest of the cells.

Dragging Format Painter to Copy Formatting

  • First, select the B5 cell (formatted one).
  • Then, from the Home tab >> click on Format Painter >> now drag the cursor from B6 to B14 cells.

Copying Date Format

So, you will get the same Date format for all in the Date column.

Read More: [Fixed!] Format Painter Not Working in Excel


2. Use Format Painter with Single Click to Copy Formatting of a Single Cell of Another Sheet

By using Format Painter, we can copy formatting for a single cell with just one click. Moreover, this Format Painter can copy a format from one sheet to another.

In the following dataset, we have a formatted column header named Profit Margin in the worksheet named Single Click-Another Sheet. Now, we want to copy this format to a column header in another worksheet named Dataset.

Use Format Painter with Single Click to Copy Formatting in Excel

  • Go to the worksheet named Single Click-Another Sheet >> select cell F4 as marked in the above image.
  • Secondly, go to the Home tab >> select the Format Painter option under the Clipboard group.

Applying Format Painter in another sheet

  • Next, go to the target worksheet named Dataset.
  • Click on cell F4 (unformatted one).

showing output after copying format

  • So, you will see that the F4 cell gets the same formatting.

Read More: How to Use Format Painter Shortcut in Excel


3. Apply 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 the marks of some students for 3 subjects. Here, if the marks are lower than 40, then the cell color will be red. We have a formatted range of marks in physics. Now, we want to apply this conditional formatting for the marks of chemistry and biology.

Apply Format Painter to Copy Conditional Formatting in Excel

  • Select the data range of Column C >> go to the Home tab >> click on the Format Painter option. So, the formatting from Column C is copied.

Using Format Painter tool

  • Now, click on cell D5 to apply the conditional formatting for marks of chemistry.

Applied Conditional formatting with Format Painter

The conditional formatting should be copied to Column D. You can do the same process for Column E also.


4. Use Format Painter with Double Click to Copy Formatting for Non-Contiguous Columns

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

In the following dataset, we have the marks of some students in 3 subjects. Also, we have marks in two different sections. But here all the cells are not formatted properly. Basically, the format is if the marks are lower than 40, then the cell color will be red.

Now we will use the Format Painter to copy the formatting of C5:C10 cells for the rest of the cells altogether.

Use of Format Painter with Double Click to Copy Formatting in Excel

  • Here, select the range C5:C10 >> go to the Home tab>> double-click on the Format Painter option. As a result, the formatting of the range is now copied.
  • Now, click on the D5, E5, C14, D14, and E14 cells.

At this stage, you will see that all the cells are now with proper formatting.

 

Note: Double-clicking on the Format Painter will allow you to copy a format more than once. Also, for stopping the copying of the format, you need to click on Format Painter again.

Read More: How to Use Format Painter Multiple Times in Excel


5. Use Format Painter to Copy Formatting of Shapes in Excel

By using 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.

Use of Format Painter to Copy Formatting of Shapes

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

  • Select the sample shape >> Go to the Home tab from the Top Ribbon.
  • Click on the Format Painter option. So, the formatting of the sample shape is copied.
  • Next, click on the unformatted shape.

Copy formatting of shape

Congratulations! You have successfully used the Format Painter to copy the formatting of the sample shape to the unformatted shape.


How to Use Paste Options to Copy Formatting in Excel

You can use one of the Paste options in Excel to copy formatting.

In our dataset, the 1st cell of the Date column (B5 cell) has an individual format (Day- 1st 3 letters of Month- Year). Now, we want to copy this formatting to other cells of the Date column.

How to Use Paste Options to Copy Formatting in Excel

  • First, we have to copy the cell with the proper format. To do so, select B5 cell >> from the Home tab >> click on Copy. Here, you can also use a keyboard shortcut CTRL+C to copy the B5 cell.
  • After copying the B5 cell >> select B6:B14 cells >> Right-click to get the Context Menu Bar.
  • Now, from the Context Menu Bar >> choose Formatting (R) from Paste Options.

Use of Paste Options in Excel

As a result, you will get the same formatting for B6:B14 cells.


How to Use Auto Fill Options to Copy Formatting in Excel

You can also use Auto Fill Options to copy a specific format for others. Like the previous one, we will copy the format of the B5 cell to others.

  • So, click on B5 cell >> at the rightmost corner of the cell, and you will get the Fill Handle icon >> click on that >> drag the mouse up to the B14 cell.

Using the Fill Handle icon in Excel

  • After releasing the mouse, you will get the Auto Fill Options. Now, choose Fill Formatting Only.

How to Use Auto Fill Options to Copy Formatting in Excel

Lastly, you will get the format for others.

Formatted Date


Frequently Asked Question

1. What is the shortcut in Excel to copy formatting?

Answer: Click on the cell with format >> press Alt+H+F+P to activate Format Painter >> click on the cell where you want to copy the formatting.

2. Can I use Format Painter to copy formatting between different workbooks in Excel?

Answer: Yes. Simply open both workbooks and use Format Painter as you would normally.


Practice Section

In the workbook, you will find a sheet named Practice Section, from which you can practice the given methods.

Practice Section


Download Practice Workbook

Download the following practice workbook. It will help you to realize 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 queries or recommendations for improving the article’s quality.


Related Articles

Zahid Hasan
Zahid Hasan

Hello and welcome! Thank you for visiting my profile. I am currently employed as an Excel & VBA Content Creator at ExcelDemy. My most recent academic qualification is a BSc (Eng) from the Bangladesh University of Engineering and Technology. Industrial and Production Engineering was my major. I constantly attempt to think creatively and find a simple answer.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo