Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

How to Copy and Paste Exact Formatting in Excel (3 Quick Ways)

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. Here, we will describe 3 easy and effective methods to do the task smoothly. So, let’s get into the main article.


Download Excel Workbook

You can download the Excel file and practice while reading this article.


3 Easy Ways to Copy and Paste Exact Formatting in Excel

The following dataset has the Employee ID, Employee Name, and Sales columns. We have a formatted Row 5,  where the Sales is in Currency sign and the row has been filled with a Fill Color. Here, we want to do all these formats in the other remaining rows as well. Without doing these tasks again and again we will show you the 3 easiest ways to copy and paste exact formatting. Here, we use Excel 365. You can use any available Excel version.

Dataset to Copy and Paste Exact Formatting in Excel


Method-1: Using Format Painter Feature to Copy and Paste Exact Formatting

In this method, we will use Format Painter to copy and paste exact formatting. We will show you 3 examples to use Format Painter.


1.1. Employing Format Painter Feature in a Range of Cells

Here, we will employ Format Painter to paste exact formatting in rows.

Steps:

  • In the first place, select the cells that already have the format.
  • Here, we selected cells B5:D5.
  • After that, go to the Home tab >> from the Clipboard group >> select Format Pinter.
  • Here, you can select the Format Painter by using the keyboard shortcut ALT+H+F+P.

Using Fomat Painter to Copy and Paste Exact Formatting in Excel

  • Then, put the format painter symbol in cell B6.
  • After that, you have to drag down and right your mouse all the way over the remaining rows.

  • Therefore, you can see the exact formatting has been pasted in others rows as well.


1.2. Creating Banded Rows

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

Creating Banded Rows to Copy and Paste Exact Formatting in Excel

Steps:

  • In the first place, select the first two formatted rows >> go to the Home tab >> select Format Painter.
  • After that, put the Format Painter on cell B7.

  • Then drag your mouse all the way over the remaining rows of the table.

  • As a result, the following table with banded rows will appear.


1.3. Copying and Pasting Formats to a New Table

Suppose, we want to copy the format of the left-side table to the right side. To do so, we will use Format Painter.

Copying and Pasing Exact Formatting in Table

Steps:

  • In the beginning, we have to select the formatted table by selecting cells B4:D12 >> go to the Home tab >> Format Painter.

  • Then we have to drag down your mouse all over the way of the table we wanted to format.

  • Therefore, the right side table will be formatted in the same way as the left side table.

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


Method-2: Using Paste Feature to Copy and Paste Exact Formatting

In this method, we will use the Paste feature to copy and paste exact formatting in Excel. We will be showing you three types of Paste features to paste copied format.


2.1. Employing Paste Special Feature

Here, we will use Paste Special feature to copy and paste exact formatting in Excel.

Steps:

  • First of all, we select Row 5 of the data table by selecting cells B5:D5.
  • Then, to copy these cells’ format, we press the CTRL+C keys.

Use of Paste Special Option to Copy and Paste Exact Formatting in Excel

  • Then, we select cells B6:D12, since we want the format of the copied cells to be pasted in these cells.
  • In addition, go to the Home tab.
  • After that, from the Paste group >> select Paste Special.

  • At this point, a Paste Special dialog box will appear.
  • Then, mark Formats, this will paste the formats only >> click OK.

  • Therefore, you can see all the rows have the same format.

How to Copy and Paste Exact Formatting in Excel


2.2. Pasting with Paste Formatting Option

Here, we will show you how you can copy and paste exact formatting using the Paste with Format feature.

Steps:

  • To copy a row’s format, you must first select the formatted row.
  • Here, we selected cells B5:D5.
  • Then press CTRL+C.

Use of Paste Formatting to Copy and Paste Exact Formatting in Excel

  • After that, select the rest of the rows where you want to paste the copied cells.
  • Here, we selected cells B6:D12.
  • After that, we right-click on our mouse >> select paste with formats from the Paste group.

  • Hence, all the cells of the dataset have the same formatting.


2.3. Applying Paste Content Option

In many cases, we need to copy and paste the exact formatting along with cell contents. Here, we will show you how you can do the task.

Steps:

  • To copy a row’s format and concert, you must first select the formatted row.
  • Here, we selected cells B5:D5.
  • Then press CTRL+C.

Copying and Pasting Exact Formats with Contents

  • Then, we select cells B6:D12, since we want the format and content of the copied cells to be pasted in these cells.
  • In addition, we right-click on the selected cells.
  • After that, from the Paste group >> select Paste with Content.

How to Copy and Paste Exact Formatting in Excel

  • Therefore, all the cells will have the same format and content.


Method-3: Utilizing Auto Fill Feature to Copy and Paste Exact Formatting

In this method, we will use the Fill Handle tool to automatically copy and paste exact formatting in Excel.

Steps:

  • First, select the formatted row, here we selected cells B5:D5.
  • After that, we will hover our mouse around the end of this row.
  • Then, a Plus sign like below will appear, which is the Fill Handle tool.
  • Moreover, we will drag down the Plus sign.

Utilizing Auto Fill Feature to Copy and Paste Exact Formatting in Excel

  • This will copy the cells with both format and content to the below cells.
  • To get only the formatting excluding contents we click on the drop-down arrow that appeared at the right-corner end of the last cell.
  • Then, we select Fill Formatting Only like below.

  • Hence, the following formatted table will appear.

How to Copy and Paste Exact Formatting in Excel

Similar Readings:


Practice Section

You can download the following Excel file and practice the explained methods


Conclusion

In this article, we tried to cover the 3 easiest ways to copy and paste exact formatting in Excel. We hope this article will help you a lot. Feel free to comment if anything seems difficult to you. You can do this if you want to share further information related to this topic. You can visit our website Exceldemy for more related articles. 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