Nuraida Kashmin
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.
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 to Use Format Painter in Multiple Cells in Excel
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.
📌 Steps:
- 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.
2. Using Double-Click Process to Use Format Painter in Multiple Cells
The Format Painter can be used for multiple cells also. You can bring the paintbrush by double-clicking on the Format Painter command.
📌 Steps:
- 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. Using Format Painter for Multiple Cells Through Go To Dialog Box in Excel
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.
📌 Steps:
- 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.
4. Applying Shortcut Keys to Use Format Painter
There are 2 useful ways to bring the Format Painter by using the keyboard shortcut. It saves you time while you use the shortcut key.
Method 1:
- 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.
Method 2:
- 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.
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.
📌 Steps:
- 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).
6. Using Format Painter to Copy Excel Conditional Formatting for Multiple Cells
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.
📌 Steps:
- Select any of the columns where the Conditional Formatting exists 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. Using Format Painter to Format Shape Quickly in Excel
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.
📌 Steps:
- Choose the Left shape and Click on the Format Painter from the Home tab.
- Then select the shape that 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
Practice Section
We have provided a practice section on each sheet on the right side for your practice. Please do it by yourself.
Download Practice Workbook
Download the following practice workbook. It will help you to realize the topic more clearly.
Conclusion
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. Thanks for your patience in reading this article.
