How to Use Format Painter for Multiple Cells in Excel (7 Methods)

Get FREE Advanced Excel Exercises with Solutions!

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.

Dataset

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.

Using One click process to use format painter in excel for multiple cells

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.

Gif for one click process

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.

Utilizing Double Click process to use format painter in excel for multiple cells


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.

Engaging Go To dialog box to use format painter in excel for multiple cells

  • 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.

Incorporating Shortcut keys to use format painter in excel for multiple cells


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.

Applying Format Painter in multiple sheets to Clone Column Width

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.
Employing Format Painter to Copy Conditional Formatting

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.

Implementing Format Painter to Format Shape Quickly

📌 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.

Practice Section


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.


Related Articles


<< Go Back to Format Painter in Excel | Excel Cell Format | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Nuraida Kashmin
Nuraida Kashmin

Nuraida Kashmin, Bachelor's degree in Mechanical Engineering from Rajshahi University of Engineering & Technology. Since 2021, she wrote 45+ articles on Excel problems and reviewed over 1000 articles. Currently working as a Project Manager, she is responsible for developing and implementing content strategies, managing writers and editors, staying updated on new technology, analyzing data, and tracking content performance indicators. Her interests include Project Management, Creative Writing, Digital Marketing, Reporting, Monitoring & Documentation, and Online Advocacy for SAP &... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo