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

Get FREE Advanced Excel Exercises with Solutions!

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.

using paste special to show how to copy formatting in excel to another sheet

  • Then, right click on the B14 cell.
  • Next, from the available options, choose Paste Special.
  • Finally, from the outstretched options, choose the Formatting sign.

using paste special to show how to copy formatting in excel to another sheet

  • As a result, the formatting of the B4 cell will be copied into the B14 cell.

using paste special to show how to copy formatting in excel to another sheet


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.

using paste special to show how to copy formatting in excel to another sheet

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

using paste special to show how to copy formatting in excel to another sheet

  • As a result, the entire format of the B4:D14 will be copied to that new sheet.

using paste special to show how to copy formatting in excel to another 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.

using paste special to show how to copy formatting in excel to another sheet

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

using paste special to show how to copy formatting in excel to another sheet

  • Consequently, the entire format of the previous sheet will be copied to that new sheet.

using paste special to show how to copy formatting in excel to another 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.

applying format painter to show how to copy formatting in excel to another sheet

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

applying format painter to show how to copy formatting in excel to another sheet

  • Consequently, the format of the B4 cell will be copied into the B14 cell.

applying format painter to show how to copy formatting in excel to another sheet


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.

applying format painter to show how to copy formatting in excel to another sheet

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


Further Readings

Maruf Islam
Maruf Islam

My self, Maruf Islam, an engineer and Excel & VBA Content developer on Exceldemy. I enjoy solving problems, finding workable solutions, and most of the part I really like to take on challenges. On Exceldemy I write articles discussing various way outs of Microsoft Excel's stuck ons.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo