We format data in Excel to change its appearance to make it more readable and appealing. The process of formatting can be tedious and time-consuming.
In the event we need to copy and paste the formatted data to a different location in the workbook, we would want to keep the format.
This is so that we do not have to again go through the time-consuming and tedious process of formatting the data.
This tutorial shows you 5 easy techniques for copying and pasting in Excel without changing the format.
Table of Contents
Suppose we have the following formatted dataset. It shows the amount of annual rainfall in five counties in cubic inches.
We want to copy this dataset and paste it into a different location of the worksheet without changing its format.
We want to use Ctrl + C and Ctrl + V keyboard shortcuts.
We use the following steps:
Note that this would also copy any formula that’s there in the cell to the destination cell, and adjust the formula reference accordingly
While this is the fastest method to quickly copy and paste in Excel without changing the formatting, one drawback here is that it will not copy the column width from the copy data set. when you copy the data into the destination cell, it will fit the data in the existing column width in the destination cells
Suppose we have the following formatted dataset. It shows the amount of annual rainfall in five counties in cubic inches.
We want to copy this dataset and paste it into a different worksheet or a different location in the same worksheet without changing its format.
We use the following steps:
Press Ctrl + C.
Or
Select Copy in the Clipboard group on the Home tab.
Or
Right-click the selected dataset and select Copy on the shortcut menu.
A moving or “marching ants” border is displayed around the dataset indicating that a copy of the dataset is on the clipboard ready for pasting.
Press Ctrl + Alt + V.
Or
Select Paste Special in the Clipboard group on the Home tab.
Or
In case you want to paste the data in the same worksheet containing the dataset, select a cell in a different location of the worksheet, right-click the dataset with the moving border and select Paste Special on the shortcut menu.
The pasted dataset appears as below:
The dataset has all the formatting of the original dataset except the height of rows 2-6 which has changed. Excel does not have a Paste Special option for row heights.
Note: You can press the Escape key to remove the moving or marching ants border around the dataset.
We can use Format Painter to copy row height from the original dataset and paste it into the copied dataset.
We use the following steps.
A moving border is displayed around rows 2-6 indicating that the format of the rows has been copied to the clipboard. Notice that the mouse pointer icon changes to a cross and brush icon.
When you release the mouse button the row height is applied to the dataset and it now looks exactly like the original.
Suppose we have the following formatted dataset. It shows the amount of annual rainfall in five counties in cubic inches.
We want to copy this dataset and paste it into a different location in the worksheet without changing its format. We want to use the Ctrl, drag, and drop technique.
We use the steps below:
Suppose we have the following formatted dataset. It shows the amount of annual rainfall in five counties in cubic inches.
We want to copy this dataset and paste it into a different location of the worksheet without changing its format. We want to use the right-click and drag-and-drop technique.
We use the following steps:
A copy of the dataset is pasted in the new location.
As you have already realized, this method can only work if you want to copy and paste the data in the same worksheet.
Suppose we have the following formatted dataset. It shows the amount of annual rainfall in five counties in cubic inches.
We want to copy this dataset and paste it into a different location of the worksheet without changing its format.
We want to use the following paste options in the Clipboard group on the Home tab:
We use the following steps:
A copy of the dataset is pasted in the new location.
We use the following steps:
A copy of the dataset is pasted in the new location.
We use the following steps:
A copy of the dataset is pasted in the new location.
In this tutorial, we have looked at 5 time-saving techniques for copying and pasting in Excel without changing the format.
In most cases, you should be fine using the simple Control + C and Control + V to quickly copy and paste data while keeping the same formatting as that of the copy cells.
And in cases where you also want to copy the column width or the row height, you can use other methods such as format painter or Paste Special dialog box.
Other Excel articles you may also like:
I am a huge fan of Microsoft Excel and love sharing my knowledge through articles and tutorials. I work as a business analyst and use Microsoft Excel extensively in my daily tasks. My aim is to help you unleash the full potential of Excel and become a data-slaying wizard yourself.
Thank you for the time and effort you give. You’ve helped so much. I have some to offer as well, but I’m still learning. Reply
MOST POPULAR ARTICLES
spreadsheetplanet.com is a participant in the Amazon Services LLC Associates Program, an affiliate advertising program designed to provide a means for sites to earn advertising fees by advertising and linking to Amazon.com