How to Copy Formatting in Excel to Another Sheet (3 Easy Ways)

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.

How to Copy Formatting in Excel to Another Sheet: 3 Easy Methods

In this article, we will discuss 3 methods of 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 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 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.

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

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 Group Worksheet (2).

Steps:

• Firstly, press Ctrl and select the Group Worksheetsheet.
• 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.

Download Workbook

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 to generate 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.

Related Articles

<< Go Back to Copy Paste in Excel | Learn Excel