How to Copy and Paste Exact Formatting in Excel(Quick 6 Methods)

Formatting is essential in Excel for various purposes. But it could be easier to copy and paste the exact formatting rather than doing it repeatedly.

If you are looking for quick methods to copy and paste exact formatting in Excel then you are in the right place. So, let’s get into the main article.

Download Excel Workbook

6 Ways to Copy and Paste Exact Formatting in Excel

In the table below I have 3 columns named Employee ID, Employee Name, Sales, and 9 rows.
I have a formatted row 4 where the contents are in Italic form and the Currency sign has been used in D4 and the row is filled with a Fill Color.
So, I want to do all these formats in the other remaining rows also.
Without doing these tasks again and again I will show some easiest ways to copy this formatting.

Data set

 

Method-1: Using Format Painter to Copy a Cell’s Format to another cell

Step-01: At first you have to select the formatted row and then follow the Home tab >> Format Painter

Format painter

Step-02: After that, you have to drag down and right your mouse all the way over the remaining rows.

using format painter

Step-03: After that, all of the formattings will be covered to the remaining rows of the table as below.

copy and paste exact formatting in Excel

However, this task can be done using a shortcut key. You just have to select the formatted row and then press
ALT+ H+F+P.
After that, the Format Painter option will be activated and you just drag it down all over the way of the table. Then the same result will occur.

Read more: How to Copy and Paste in Excel Without Changing the Format

Method-2: Using Shortcut Key to Copy Format

Step-01: In this method, you have to select the formatted row at first and then press CTRL + C. In this way, the formatted row will be copied.

using shortcut key

Step-02: After that right-click on your mouse and click on Paste Special option and then the Formatting option.

paste formatting

Step-03: After that, the following formatted table will appear.

formatted table

Method-3: Using Fill handle to Copy and Paste Formatting

Step-01: At first select the formatted row and hover your mouse around the end of this row and then a Plus sign like below will appear.

fill handle tool

Step-02: Then double click on this Plus sign and like the following figure the first row will be copied with both content and format.
To get only the formatting excluding contents you have to select Fill Formatting Only like below.

formatted data and format

Step-03: After that, the following formatted table will appear.

formatted table


Similar Readings:


Method-4: Creating Banded Rows in a Table Using Format Painter

In Excel, the term “Banded Rows” refers to the shading of alternating rows in a worksheet. In the following table, I have banded rows for the first two rows. Now I will copy this format to other remaining rows.

banded rows

Step-01: At first select the first two formatted rows and then follow Home tab >> Format Painter .

formatting banded rows

Step-02: Then drag your mouse all the way over the remaining rows of the table.

formatting banded rows

Step-03: Then the following table with banded rows will appear.

banded rows

Method-5: Copying and Pasting a Row’s Format including Content

Step-01: To copy a row’s format including its content you have to at first select the formatted row as below. Then press CTRL+C .

copying formatted row

Step-02: Then select the rest of the rows and right-click on your mouse and select Paste from Paste Options.

paste formatting

Step-03: Then the first row with its content and format will be copied in all other rows as below.

paste contents and format

Read more: How to Copy the Same Value in Multiple Cells in Excel

Method-6: Copying and Pasting a Table’s Content to another Table

Suppose, I want to copy the format of the left side table to the right side. To do this you have to follow the following steps.

copy table formatting

Step-01: At first you have to select the formatted table and then follow the Home tab >> Format Painter .

using format painter

Step-02: Then you have to drag down your mouse all over the way of the table you wanted to format.

table formatting

Step-03: In this way, the right side table will be formatted in the same way as the left side table.

formatted table

Conclusion

In this article, I tried to cover some easiest ways to copy and paste exact formatting in Excel. I hope this article will help you a lot. Feel free to comment if anything seems difficult to you. If you want to share further information related to this topic you can do this. Thank You.


Further Readings

Tanjima Hossain

Tanjima Hossain

Hello everyone, This is Tanjima Hossain. I have completed my graduation from BUET. Then I have started working as a technical writer in SOFTEKO. I have grown interest in technical content writing, research topics, numerical analysis related field and so I am here. Besides this I love to interact with different people and I love to spend my spare time by reading, gardening ,cooking etc.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo