How to Use Format Painter in Excel for Multiple Sheets

Looking for ways to know how to use Format Painter in Excel for multiple sheets? Sometimes, we have to copy the format into multiple sheets. We can easily do that with the help of the Format Painter feature. Here, you will find step-by-step explained ways to use Format Painter with 3 examples in Excel for multiple sheets.


Download Practice Workbook


3 Examples to Use Format Painter in Excel for Multiple Sheets

Here, we have 3 Excel Sheets named Math, Physics, and Chemistry which contain the Student ID, Name, and marks of that particular subject of some students. Now, we will use these sheets to show you how to use Format Painter in Excel for multiple sheets.

3 Examples to Use Format Painter in Excel for Multiple Sheets

This is the Math sheet containing the Student ID, Name, and Math scores of some students.

Now, this is the Physics sheet containing the Student ID, Name, and Physics scores of some students.

3 Examples to Use Format Painter in Excel for Multiple Sheets

Finally, this is the Chemistry sheet containing the Student ID, Name and Chemistry scores of some students.


1. Using Double-Click to Use Format Painter for Multiple Sheets to Copy Cell Format

In the first example, we will use Double-click to use Format Painter for multiple sheets. Here, we will format some Cells in the Math sheet and then copy the format to Physics and Chemistry sheets by using Format Painter.

Follow the steps given below to do it on your own.

Steps:

  • Firstly, select Cell range B4:D4 from the Math sheet.

Using Double-Click to Use Format Painter for Multiple Sheets to Copy Cell Format

  • Then, go to the Home tab >> click on Fill Color button >> select Blue, Accent 1, Darker 50%.

  • Again, from the Home tab >> click on Font Color button >> select White, Background 1.

Using Double-Click to Use Format Painter for Multiple Sheets to Copy Cell Format

  • After that, select Cell range B4:D4.
  • Then, go to the Home tab >> double-click on the Format Painter feature.

  • Now, go to the Physics Sheet.
  • Next, Click on Cell B4 to Paste the format in this sheet.

Using Double-Click to Use Format Painter for Multiple Sheets to Copy Cell Format

  • Then, go to the Chemistry Sheet.
  • Finally, Click on Cell B4 to Paste the format in this sheet.

Thus, you can use Double-click to use Format Painter in Excel for multiple sheets to copy cell format.


2. Using Format Painter for Multiple Sheets to Copy Conditional Formatting

We can also use Format Painter to copy Conditional Formatting in Excel for multiple sheets. Here, we will format some Cells using Conditional Formatting in the Math sheet and then copy the format to Physics and Chemistry sheets by using Format Painter.

Go through the steps given below to do it on your own.

Steps:

  • In the beginning, select Cell range D5:D10.
  • After that, go to the Home tab >> click on Conditional Formatting.

Using Format Painter for Multiple Sheets to Copy Conditional Formatting

  • Then, click on Highlight Cells Rules >> select Greater Than.

  • Now, the Greater Than box will appear.
  • Next, type 80 in the Format cells that are GREATER THAN box.
  • Then, select Green Fill with Dark Green Text format.
  • After that, click on OK.

Using Format Painter for Multiple Sheets to Copy Conditional Formatting

  • Now, you will see that Cells containing a Greater value than 80 have been formatted.

  • Again, go to the Home tab >> click on Conditional Formatting.

Using Format Painter for Multiple Sheets to Copy Conditional Formatting

  • Then, click on Highlight Cells Rules >> select Less Than.

Using Format Painter for Multiple Sheets to Copy Conditional Formatting

  • Now, the Less Than box will appear.
  • Next, type 40 in the Format cells that are LESS THAN box.
  • Then, select Light Red Fill with Dark Red Text format.
  • After that, click on OK.

  • Now, you will see that Cells containing values less than 40 have been formatted.

Using Format Painter for Multiple Sheets to Copy Conditional Formatting

  • Next, select Cell range D5:D11.
  • After that, go to the Home tab >> Double-click on Format Painter.

  • Now, go to the Physics Sheet.
  • Then, Click on Cell D5 to Paste the format into this sheet.

Using Format Painter for Multiple Sheets to Copy Conditional Formatting

  • After that, go to the Chemistry Sheet.
  • Finally, Click on Cell D5 to Paste the format in this sheet.

Thus, you can use Format Painter in Excel for multiple sheets to copy Conditional Formatting.


3. Copying Shape Format by Using Format Painter

Now, we will show you how to use Format Painter to copy Shape Formatting in Excel for multiple sheets. Here, we have a rectangle containing some text in each of the 3 sheets. Now, we will format the rectangle box in the Math sheet and then copy the format to Physics and Chemistry sheets by using Format Painter.

Follow the steps given below to do it on your own dataset.

Steps:

  • Firstly, go to the Math Sheet.

Use of Format Painter to Copy Shape Formatting

  • Then, select the Rectangle.
  • After that, go to the Shape Format tab >> click on Shape Style.

  • Next, select any format of your choice. Here, we will select Subtle Effect-Orange, Accent 2 format.

Use of Format Painter to Copy Shape Formatting

  • Now, you will see that Rectangle has been formatted.

  • Then, select the Rectangle.
  • After that, go to the Home tab >> Double-click on Format Painter.

Use of Format Painter to Copy Shape Formatting

  • Now, go to the Physics Sheet.
  • Next, click on the Rectangle to Paste the format into this sheet.

  • Then, go to the Chemistry Sheet.
  • Finally, Click on the Rectangle to Paste the format in this sheet.

Use of Format Painter to Copy Shape Formatting


Things to Remember

  • You must Double-click on the Format Painter button to copy any format in Excel for multiple sheets.

Practice Section

In this section, we are giving you the dataset to practice on your own and learn to use these methods.

Practice Section


Conclusion

So, in this article, you will find a step-by-step way to use Format Painter in Excel for multiple sheets. Use any of these ways to accomplish the result in this regard. Hope you find this article helpful and informative. Feel free to comment if something seems difficult to understand. Let us know any other approaches which we might have missed here. And, visit ExcelDemy for many more articles like this. Thank you!

Arin

Arin

Hello, I'm Arin. I graduated from Khulna University of Engineering and Technology (KUET) from the Department of Civil Engineering. I am passionate about learning new things and increasing my data analysis knowledge as well as critical thinking. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to it.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo