Arin Islam
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.
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.
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.
- 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.
- 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.
- 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.
Read More: [Fixed!] Format Painter Not Working in Excel (3 Possible Solutions)
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.
- 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.
- 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.
- Then, click on Highlight Cells Rules >> select Less Than.
- 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.
- 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.
- 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.
Read More: How to Use Format Painter in Excel for Multiple Cells (6 Quick Ways)
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.
- 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.
- 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.
- 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.
Read More: How to Use Format Painter Shortcut in Excel (5 Ways)
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.
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!
