How to Copy Formatting in Excel to Another Sheet (4 Ways)

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. In this article, we’ll show you how to copy formatting to another sheet of the same workbook in Excel.

Suppose, I have a workbook with two sheets. The first one is about id info & the second one is salary Info.

data set

                                                                   The dataset in Two Sheets

Download Workbook

4 Easy Methods to Copy Formatting in Excel to Another Sheet 

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

Case 1: Copy the Formatting to a Single Cell

Step 1: Select a cell you want to copy the format, then Right-Click on the cell.

Step 2: From the pop-up options click on Copy.

copy single cell

You can simply avoid the steps by pressing the keyboard shortcut CTRL+C.

Step 3: Go to the cell (same or another sheet) and Right-click. Then click on the arrow sign next to the Paste Special option.

Step 4: Click on the Formatting icon.

paste single cell

Case 2: Copy the Formatting to a Range of Cells

Step 1: Select a range of cells, you want to copy the formatting

Step 2: Right-Click on any cell inside the selected range.

copy range

Step 3: Go to another sheet. Click on the arrow sign next to the Paste Special option.

Step 4: Click on the Formatting icon.

paste range

Read more: How to Copy Formatting in Excel

Method 2: Using Format Painter Option

Case 1: Copy the Formatting to a Single Cell

Step 1: Select a cell, you want to copy the format.

Step 2: Go to Home Tab, Click on Format Painter feature; then the mouse cursor turns into Plus Paintbrush icon.

single cell painter method

Step 3: Click on the cell in another sheet (you can use pressing Ctrl + PageUp/PageDown altogether to shift between excel workbook sheets; Left & Right). Only cell formation gets copied.

paste single cell

Case 2: Copy the Formatting to a Range of Cells

Step 1: Select a range of cells, you want to copy the formatting.

Step 2: Go to Home Tab, Click on Format Painter feature; then the mouse cursor turns into Plus Paintbrush icon.

copy range

Step 3: Go to another sheet, select the desired range to format & release.

paste range painter method

Format Painter Option allows copying formats with conditional formatting in another sheet.

Read more: How to Use Format Painter Excel Multiple Cells

Method 3: Using Excel Group Worksheet 

Step 1: Press CTRL  & click on the sheets below the workbook you want to format altogether.

excel group sheet

Step 2: After selection, any change in formatting one sheet automatically changes the other.

excel group work sheet

   Before & After Group Worksheet Method

Step 3: Double-click on any sheet to un-select.

Method 4: Copy Entire Sheet with Keep Formatting Intact  

Step 1: Click on the icon on the sheet & press Ctrl+C.

entire data copying

Step 2: Go to the cell (same or another sheet) and Right-click. Then click on the arrow sign next to the Paste Special option & click on Formatting.

Entire data paste

Conclusion

The methods mentioned above are most general in use & easy to use. 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.


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

ExcelDemy
Logo