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.
- 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.
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.
- 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.
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.
- 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.
- Next, go to the target worksheet named Dataset.
- Click on cell F4 (unformatted one).
- 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.
- 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.
- Now, click on cell D5 to apply the conditional formatting for marks of chemistry.
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.
- 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.
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.
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.
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.
- 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.
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.
- After releasing the mouse, you will get the Auto Fill Options. Now, choose Fill Formatting Only.
Lastly, you will get the format for others.
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.
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.
