[Solved] Excel Copy Paste Loses Formatting

Get FREE Advanced Excel Exercises with Solutions!

When you copy and paste data in a worksheet, sometimes Excel loses formatting after performing the task. There are several ways how you can avoid this formatting loss while you copy and paste data. In this article, I will show you some solutions to fix Excel copy paste loses formatting of the original.


If Copy Paste Loses Formatting in Excel: 5 Solutions

Let’s say, we have a dataset of some employees of an organization, their current salary, and 10% increment of their current salary.

We will use this dataset to show the solutions to the problem of copy-paste in Excel which loses formatting and ways to overcome them.

In this section, you will find 5 exclusive solutions for the problem of copy paste loses formatting in Excel. I will demonstrate them one by one here. Let’s check them now!


1. Using Format Painter

Let’s say, the format of 5th Row of our dataset is ideal. In this row, all the data are in Italic format and cell C5 has a specific Fill Color.

Excel Copy Paste Loses Formatting

Somehow the other rows have lost this formatting and we want to restore the formatting using the Format Painter command. In order to do so, follow the steps below.

Steps:

  • First of all, select the row in which you want to copy the format.
  • Then, go to the Home tab> click the Format Painter option from the Clipboard group.

Format Painter to fix Excel Copy Paste Loses Formatting

  • Then, drag your cursor to the end of the data range below the selected row. The cursor will show the Painter tool with it.

  • Hence, all the cells below the selected row will be formatted to the selected row.

Excel Copy Paste Restored


2. From Excel Ribbon

Well, for the same data, we will now show the use of Excel Ribbon to avoid losses of formatting. Let’s follow the steps below.

Steps:

  • Firstly, select the first row in which format you considered identical and go to the Ribbon> click Copy from Clipboard group to copy the range.

Fix Excel Copy Paste Loses from Ribbon

  • Now, select the range of cells that you want to be in the same format as the previously selected cells, go to the dropdown of the Paste menu, and choose Paste Formatting.

  • As a result, the cells will be formatted as the previously selected cells.


3. Using Fill Handle Tool

Fill Handle tool is a special feature of Excel that allows performing enormous tasks in Excel. Just proceed with the steps below to utilize this tool.

Steps:

  • Firstly, select the range of data and take your cursor to the right-end cell of the selected data range and your cursor will turn most likely to a plus sign “+” which is our desired Fill Handle tool. Drag this tool to the last end cell in which you want the format.

Fill Handle tool to Fix Excel Copy Paste Loses

  • After dragging, you will see that the data has been changed because of the filling series.
  • Now, click the dropdown of the Quick Analysis option and select Fill Formatting Only for only copying the format.

  • Now, the data will be restored and your desired formatting will be gained.


4. Utilizing Paste Special Option from Context Menu

Paste Special can be utilized in different ways. We will now apply this command from Context Menu (by right clicking the mouse). So, let’s start the process like the one below.

Steps:

  • First of all, select the range of data in which format you want to copy> press CTRL+C to copy that data range and the copied will be shown as a green dotted borderline.
  • Now, select the range of data which has doesn’t include the format as the copied cells.

Fixing excel Copy Paste Loses

  • Then, right-click the mouse, and a list of options will appear. Here, click the Paste Formatting option just described as the image.

  • As soon as you choose the command, your cells will be formatted as the copied range of cells.

Read More: [Fixed!] Copy and Paste Not Working Between Workbooks in Excel


5. Copy Format from One Sheet to Another

Let’s say, we have got the data below in an Excel worksheet. Somehow this data has lost the format and we want to restore the format or copy the format from another sheet and paste the format here.

Excel Copy Paste Loses Formatting

So, proceed in the steps below.

Steps:

  • First of all, select the range of data of the sheet in which format you want to copy and press CTRL+C to copy the data. This will copy everything in this data range (i.e. format, value, formula).

  • Then, go to the sheet that has lost the format. Click the first cell of that sheet and right-click on the mouse> choose the Paste Format command from the Paste options.

  • Hence, this data range will get the format of the selected sheet.

Read More: [Fixed!] Cannot Copy Merged Cells in Excel


Download Practice Workbook

You can download the practice book from the link below.


Conclusion

In this article, I have tried to show you some solutions to avoid Excel copy paste loses formatting. I hope this article has shed some light on your way to this. If you have better methods, questions, or feedback regarding this article, please don’t forget to share them in the comment box.


Related Articles


<< Go Back to Copy-Paste Not Working | Copy Paste in Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Rafiul Hasan
Rafiul Hasan

Rafiul Hasan, holding a BSc in Naval Architecture and Marine Engineering from Bangladesh University of Engineering & Technology, contributes significantly to the ExcelDemy project with almost 1.6 years of dedicated work. Currently an Excel and VBA Content Developer, he has a passion for problem-solving. Authoring over 100 articles for ExcelDemy showcases expertise in Microsoft Office Suites and Data Analysis. In addition to content development, Rafiul actively engages with the ExcelDemy forum, offering valuable solutions to user queries and... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo