Shamima Sultana
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.
Download to Practice
7 Ways to Use Excel Format Painter
1. Using Format Painter to Copy Cell Formatting
If you want, you can use 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.
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.
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.
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.
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.
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.
⏩ 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.
⏩ 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.
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.
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
- How to Format Cell Based on Formula in Excel (13 Examples)
- Dealing with Time Format in Excel (5 Suitable Ways)
- Uses of CELL Color A1 in Excel (3 Examples)
- VBA to Format Cell in Excel (12 Ways)
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.
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 >=500This 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.
⏩ 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.
Hence, the values greater than or equal to 500 are highlighted with the selected color.
➤ 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 < 500This 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.
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.
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.
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.
Read More: [Fixed!] Format Painter Not Working in Excel (3 Possible Solutions)
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.
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.
Hence, it will paste the copied format to the F3:F13 cell.
Read More: How to Use Format Painter in Excel for Multiple Sheets
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.
