[Solved] Excel Copy Paste Loses Formatting

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.

Download Practice Workbook

You can download the practice book from the link below.

5 Solutions If Copy Paste Loses Formatting in Excel

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 5^th Row of our dataset is ideal. In this row, all the data are in Italic format and cell C5 has a specific Fill Color.

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.

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

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.

• 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 (i.e. copy formula, formatting, series, value, etc.). 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.

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

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

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

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.

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

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. For more queries, kindly visit our website ExcelDemy.

Happy Excelling!