Tanjima Hossain
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.
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.
- 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.
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.
Read More: Copy Rows from One Sheet to Another Based on Criteria in Excel
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.
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.
- 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.
Read More: PasteSpecial Method of Worksheet Class Failed (Reasons & Solutions)
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.
- 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.
Read More: Run Time Error 1004: PasteSpecial Method of Range Class Failed
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.
- 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.
- Therefore, all the cells will have the same format and content.
Read More: How to Use Paste Options in Excel
Similar Readings
- How to Copy and Paste Pivot Table Values with Formatting in Excel
- Copy and Paste in Excel Without Hidden Rows
- How to Copy and Paste Excel Table into Outlook Email
- Copy and Paste Multiple Cells in Excel (7 Quick Ways)
- How to Copy Excel Sheet to Another Sheet with Same Format
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.
- 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.
Read More: Formula to Copy and Paste Values in Excel Automatically
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.
Related Articles
- How to Copy Visible Cells Only in Excel (4 Fast Ways)
- Paste Comma Separated Values into Excel (in Different Orders)
- How to Copy WhatsApp Group Contacts to Excel (with Easy Steps)
- Copy a Worksheet in Excel (5 Smart Ways)
- [Fixed!] Paste Special Not Working in Excel (4 Possible Solutions)
- How to Use Paste Name Dialog Box In Excel (3 Ideal Examples)
