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

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. 

Dataset to Copy

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.
Select and copy the dataset
  1. Select a cell in another location on the worksheet. 
  2. Press Ctrl + V to paste a copy of the dataset in the new location.
Data copied and pasted without changing the formatting

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

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. 

Dataset to Copy

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.

Copy the cells

Or

Right-click the selected dataset and select Copy on the shortcut menu.

Right-click and copy

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.

Dataset copied
  1. Open a new worksheet and select a start cell for pasting the data. In our example, we select cell A1.
  2. 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.

Right-click and then click on Paste Special

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.

Right-click and then click on Paste Special
  1. In the Paste Special dialog box that appears, select All in the Paste section and None in the Operation section and click OK.
Select all in paste special dialog box

The pasted dataset appears as below:

data pasted with formatting

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.
select rows
  1. Select Format Painter in the Clipboard group on the Home tab.
click on format painter

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.

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

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

data copied with formatting and row height

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. 

Dataset to Copy

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.
Dataset copied

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. 

Dataset to Copy

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.
Place the cursor at the edge of the selection
  1. Right-click and drag a copy of the dataset to the desired location. 
  2. Release the right mouse button and select Copy Here on the shortcut menu that appears.
Select copy here

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

dataset copied

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. 

Dataset to Copy

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:

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.
Select the Paste option

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

dataset copied

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.
keep source formatting

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

dataset copied

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.
keep source column width

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

dataset copied without changing formatting

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: