How to Copy and Paste in Excel Without Changing the Format?

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. 

Method #1: Use Keyboard Shortcuts

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:

1. Select the dataset and press Ctrl + C to copy it.

2. Select a cell in another location on the worksheet. 
3. Press Ctrl + V to paste a copy of the dataset in the new location.

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

Also read: Make all Cells the Same Size in Excel

Method #2: Use the Paste Special Dialog Box

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:

1. Select the dataset and copy it to the clipboard using any of the following ways: 

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.

2. Open a new worksheet and select a start cell for pasting the data. In our example, we select cell A1.
3. Open the Paste Special dialog box using any of the following ways:

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.

4. In the Paste Special dialog box that appears, select All in the Paste section and None in the Operation section and click OK.

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.

1. Click the header of row 2 and drag it down to the header of row 6 to select the five rows of our example dataset.

2. Select Format Painter in the Clipboard group on the Home tab.

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.

3. Open the worksheet that contains a copy of the dataset.
4. Select row 2 by clicking its header. Drag down to row 6 to apply the row height format.

When you release the mouse button the row height is applied to the dataset and it now looks exactly like the original.

Also read: Copy Conditional Formatting in Excel

Method #3: Use the Ctrl, Drag, and Drop Technique

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:

1. Select the dataset and press and hold down the Ctrl key.
2. Point to the bottom or right edge of the dataset. A plus sign appears close to the mouse pointer. 
3. Press and hold down the left mouse button and drag a copy of the dataset to a different location in the worksheet
4. Release the mouse button and a copy of the dataset is pasted in the new location.

Method #4: Use Right-click, Drag, and Drop Technique 

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:

1. Select the dataset and point either to the right or bottom edge of the dataset. A four-headed arrow appears on top of the mouse pointer.

2. Right-click and drag a copy of the dataset to the desired location. 
3. Release the right mouse button and select Copy Here on the shortcut menu that appears.

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.

Method #5: Use the Paste Options in the Clipboard Group

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:

• Paste
• Keep Source Formatting
• Keep Source Column Widths

Use the Paste option

We use the following steps:

1. Select the dataset and press Ctrl + C to copy it.=
2. Select a cell in another location on the worksheet.
3. Select the Paste option in the Clipboard group on the Home tab.

A copy of the dataset is pasted in the new location.

Use the Keep Source Formatting option

We use the following steps:

1. Select the dataset and press Ctrl + C to copy it.
2. Select a cell in another location on the worksheet.
3. Select the Keep Source Formatting option in the Clipboard group on the Home tab.

A copy of the dataset is pasted in the new location.

Use the Keep Source Columns Widths option

We use the following steps:

1. Select the dataset and press Ctrl + C to copy it.
2. Select a cell in another location on the worksheet.
3. Select the Keep Source Column Widths option in the Clipboard group on the Home tab.

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:

• How to Paste without Formatting in Excel (Shortcuts)
• How to Paste in a Filtered Column Skipping the Hidden Cells
• How to Copy Formatting In Excel
• How to Remove Table Formatting in Excel?
• How to Remove Conditional Formatting in Excel?
• Cut Paste in Excel (Shortcut)