Maruf Islam
In this article, we’ll show you how to copy formatting to another sheet of the same workbook in Excel. Font, Number, Date types; Alignment; Bold, Italic Headings; Coloring; Designing; Size of cells; etc. are different kinds of formats available in Excel. From time to time, you may need to copy the formatting. This allows users to keep the consistency of the formatting of sheets in a workbook. Here, we will copy the format of a certain dataset and paste it to another sheet like the image below.
Download Workbook
3 Easy Methods to Copy Formatting in Excel to Another Sheet
In this article, we will discuss 3 methods of how to how to copy formatting in Excel to another sheet. Firstly, we will use the Paste Special command in three cases namely: for single cell, for a range of cells and finally for an entire sheet. Secondly, we will use the Format Painter option for copying formatting of a single cell or a range of cells. Finally, we will group 2 sheets and then change the formatting within the sheets.
1. Using Paste Special
In Excel, after applying Copy, the Paste Special option offers Paste Text, Paste Values, and other different options. One of them is Paste Formatting. This option allows users to copy just the formatting of a particular cell or a set of cells.
1.1 Using Paste Special in Single Cell
In this method, we will copy the formatting of a single cell and paste it to another cell by using the Paste Special command.
Steps:
- Firstly, choose the B4 cell and press Ctrl+C to copy the cell.
- Then, right click on the B14 cell.
- Next, from the available options, choose Paste Special.
- Finally, from the outstretched options, choose the Formatting sign.
- As a result, the formatting of the B4 cell will be copied into the B14 cell.
1.2 Using Paste Special in Range of Cells
Here, we will select a range of cells and copy their formatting. Then, we will paste the copied format into another sheet by applying the Paste Special command.
Steps:
- To begin with, select the B4:D14 range and press Ctrl+C.
- Next, right-click on the B4 cell in another sheet.
- Now, from the available options, select Paste Special.
- Finally, from the outstretched options, select the Formatting sign.
- As a result, the entire format of the B4:D14 will be copied to that new sheet.
1.3 Copying Entire Sheet
In this instance, we will copy an entire sheet. Next, we will use the Paste Special command to paste it to another new sheet while keeping the formatting of the first sheet.
Steps:
- First, select the entire sheet by clicking on the triangular sign on the top left corner of the sheet and press Ctrl+C.
- Then, choose the A1 cell of a new sheet and right click.
- Thereafter, from the prevailing options, choose Paste Special.
- Next, from the outstretched options, select the Formatting sign.
- Consequently, the entire format of the previous sheet will be copied to that new sheet.
Read more: How to Copy Formatting in Excel
2. Using Format Painter Option
The Format Painter is a handy tool that copies the format of a particular cell or a range of cells and allow users to paste it to another cell or sheet. In this method, we will use this tool to show how to copy formatting in Excel to another sheet.
2.1 Using Format Painter to Single Cell
In this sub method, we will use the Format Painter to copy the format of a certain cell and then paste only the format into another cell.
Steps:
- Firstly, choose the B4 cell.
- Secondly, go to the Home tab.
- From there, choose Format Painter.
- As a result, a plus sign with a brush sign will appear on the screen.
- Now, go to the B14 cell and left click on it.
- Consequently, the format of the B4 cell will be copied into the B14 cell.
2.2 Using Format Painter to Range of Cells
Here, we will use the Format Painter tool to copy and paste the format of a set of cells into another sheet. The formatting of the fisrt sheet will remain unchanged in the second sheet.
Steps:
- Firstly, select the B4:D14 range.
- Secondly, select the Home tab.
- Next, choose Format Painter.
- Consequently, a plus sign with a brush sign will appear on the screen.
- After that, go to the B4 cell of another sheet and left click on it.
- Consequently, the format of the B4:D14 cell will be replicated in the new sheet.
Read more: How to Use Format Painter Excel Multiple Cells
3. Using Excel Group Worksheet
In this final method, we will not copy any formatting of one sheet and paste it to another. Rather, we will create a group of sheets. Then, we will change the formatting of one cell in one sheet of the group. As a result, the formatting of the same cell will be changed in other sheets of the group. Here, we have two sheets which we will group together. One is named Group Worksheet.
Another is named as Group Worksheet (2).
Steps:
- Firstly, press Ctrl and select the Group Worksheet sheet.
- Then, select the Group Worksheet (2) sheet while pressing Ctrl.
- Now, format the B14 cell in the Group Worksheet sheet according to your desire.
- Here, we will change the Font Size, Fill Color of the cell value and make it Bold.
- Consequently, we will see that the B14 cell of the Group Worksheet (2) sheet will have the same formatting.
Conclusion
In this article, we have talked about 3 methods of how to copy formatting in Excel to another sheet. You can save hours of your time using them generating reports. Hope you find them convenient & user-friendly. Feel free to share your thoughts in the comment box. If you have any questions regarding this essay, feel free to let us know in the comments. Also, if you want to see more Excel content like this, please visit our website Exceldemy.Com and unlock a great resource for Excel-related content.
