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 will also consume a lot 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. You can use a Format Painter for multiple cells also. In this article, we’re going to elaborate on the use of Format Painter for multiple cells in Excel. So, let’s get started.
Download Practice Workbook
Download the following practice workbook. It will help you to realize the topic more clearly.
7 Methods to Use Format Painter for Multiple Cells in Excel
To use the Format Painter you need to create a dataset first. Here, we have taken a dataset of a List of Fruits and Their Colors. Now, we will use Format Painter to copy the cell format.
Not to mention, we have used the Microsoft 365 version. You may use any other version at your convenience.
1. Using One-Click Process
The Format Painter is an extraordinary tool to copy all the formatting such as color, row height, column width, shape, etc. Just click on the Format Painter and copy and paste the format. Follow the below steps to do that.
- Firstly, select the entire data range that you want to format.
- Secondly, hold the CTRL + C key to copy the cells.
- Thirdly, navigate to the Home tab>> choose Format Painter from the Clipboard section.
- Apparently, the mouse curse will turn into a paintbrush. Just click on the cells where you want to put the format.
Finally, the Color column will turn into the same color as the image below.
Additionally, we have attached a GIF of the full process of this method for better visualization.
Read More: How to Use Format Painter in Excel (7 Ways)
2. Utilizing Double-Click Process
The Format Painter can be used for multiple cells also. You can bring the paintbrush by double-clicking on the Format Painter command.
- Firstly, select the data range that you want to copy the formatting.
- Secondly, hover over the Home tab >> choose Format Painter from the Clipboard section by double-clicking on it.
- Finally, paste the format to your desired multiple columns and get the following outputs.
3. Engaging Go To Dialog Box
This method helps to select the data range manually where you want to put the formatting. The reference cells should be given in the Go To dialog box. Follow the simple steps stated below.
- Firstly, select the data range and copy it with the CTRL + C key.
- Eventually, go to the Home tab >> pick Format Painter.
- Consequently, press the F5 key.
- Sequentially, the Go To dialog box appears. Put the data range D5:D9 in the Reference box as we want to paste the formatting there.
- Lastly, hit OK.
You can see the formatting is done to your selected cells automatically like the image below.
Read More: How to Repeat Formula Pattern in Excel (Easiest 8 ways)
4. Incorporating Shortcut Keys
There are 2 useful ways to bring the Format Painter by using the keyboard. It saves you time while you use the shortcut key.
- Firstly, select the formatted cells.
- Secondly, copy the cell to the Clipboard, and press Ctrl+C.
- Then, select the required cell.
- Finally, press Shift+F10, S, R. The context menu will show up if we follow this order and select the option from that for formatting.
- Initially, select the formatted cells.
- At this moment, press Alt+H, F, P. This order takes us to the Format Painter.
- Now select the place where we want to copy the formatting.
You can use any of the methods and finally, your selected cells will be formatted like the image below.
- How to Custom Format Cells in Excel (17 Examples)
- Copy Cell Format in Excel (4 Methods)
- How to Format Text Using Excel VBA (12 Methods)
- Copy Formatting in Excel (3 Processes)
5. Applying Format Painter to Clone Column Width
You can also copy the column width with the Format Painter also. For this, we have taken a dataset where we put the data into two separate columns.
- Now, primarily, select the entire column by clicking on the column bar.
- Apparently, move to the Home tab >> select Format Painter.
- Lastly, paste the format to column C.
As you can see the column width of C is exactly the same as column B (see the below image).
Read More: How to Use Format Painter Shortcut in Excel (5 Ways)
6. Employing Format Painter to Copy Conditional Formatting
We use Conditional Formatting with the below dataset. It saves a lot of time. Here, we have a worksheet where we highlighted the Project’s amount greater than $2000.
Now, we want to set the same conditional formatting in column E.
- Select any of the columns where the Conditional Formatting existed and go to the Format Painter.
- Eventually, click on any cell of the required column where you want to put the formatting. You will get a proper idea by seeing the GIF below.
Finally, you will get the below output like the image below.
Read More: How to Use Format Painter in Excel for Multiple Sheets
7. Implementing Format Painter to Format Shape Quickly
We can copy one shape’s formatting to another by using Format Painter. Let’s say we have to format the Right shape according to the Left one.
- Choose the Left shape and Click on the Format Painter from the Home tab.
- Then select the shape which we want to format.
- Finally, we can see that the Right shape is formatted like the Left one.
Read More: [Fixed!] Format Painter Not Working in Excel (3 Possible Solutions)
We have provided a practice section on each sheet on the right side for your practice. Please do it by yourself.
That’s all about today’s session. And these are some easy methods to use format painter in Excel for multiple cells. Please let us know in the comments section if you have any questions or suggestions. For a better understanding please download the practice sheet. Visit our website ExcelDemy, a one-stop Excel solution provider, to find out about diverse kinds of excel methods. Thanks for your patience in reading this article.