How to Copy and Paste Without Changing the Format in Excel

Get FREE Advanced Excel Exercises with Solutions!

To copy and paste in Excel without changing the format means to move or duplicate data keeping all source formatting intact in the destination. This formatting includes style, color, size, etc.

In this Excel tutorial, you will learn how to copy and paste in Excel without changing the format.

Say, we have the following dataset. We will copy the source data and paste it into a new location and will keep all the formatting unchanged.

Copy and paste in Excel without changing the format

After reading this article, you will learn the following ways to copy and paste data in Excel without changing the format:

– Using the ‘Keep Source Column Widths (W)’ command
– Using simple paste commands and then adjusting the column width using the Paste Options button
– Using Paste Special feature

Finally, we will share 2 mouse techniques to copy and paste Excel cells without changing the format.

Note: We have used Excel for Microsoft 365 to prepare this article. But you can use any version available to you.


Watch Video – Copy and Paste Without Changing the Format in Excel



1. Quickest Method: Use the ‘Keep Source Column Widths (W)’ Command

You can copy and paste in Excel without changing the format using the ‘Keep Source Column Widths (W)’ command. This command is available in the Paste options list of Excel. Using this command, you can copy and paste all contents of source cells including the column widths.

To copy and paste using the ‘Keep Source Column Widths (W)’ command, follow the steps below.

Step 1: Select source cells B6:C16 and press Ctrl+C to copy them.

Copy data using keyboard shortcut

3 Ways to Copy Cells in Excel

  • Select cells ⇒ press Ctrl+C.
  • Select cells ⇒ Clipboard group of commands ⇒ Click the Copy button.
  • Select cells ⇒ Right-click your mouse ⇒ Click Copy.

Step 2: Click on cell E6ClipboardPaste drop-down.

Step 3: Choose the Keep Source Column Widths (W) option from the appeared options.

Use the Keep Source Columns Widths (W) feature from Clipboard group to copy and paste without changing format

Note:
You can also use the Keep Source Column Widths (W) command from the context menu to copy and paste without changing the format.

Use the Keep Source Columns Widths (W) feature from Context Menu to copy and paste without changing the format


2. Adjust Column Widths Using Paste Options Button after Pasting Cells Keeping Source Cells’ Format Intact

To copy and paste without changing the format, you can use the Paste command from the Clipboard group of commands and context menu. Or apply a keyboard shortcut. However after copying cells, the column widths are not usually copied, so you may not see data sometimes.

Column not changing according to source data when pasting

Once you have pasted cells, you can use the Excel Paste Options button that appears when you copy cells and then paste. It appears in the bottom right corner of the destination cells.

Paste Options button in Excel

How to Enable Paste Options Button?

  • Go to File ⇒ Options or, press Alt + F + T ⇒ Advanced option Cut, copy, and paste section Check the Show Paste Options button when content is pasted option.
  • Press the OK button.

Enabling Paste Options in Excel

Now let’s see the whole method step by step.

Step 1: First, select range B6:C16 and press Ctrl+C to copy the cells.

Copy data to paste without changing format

The dotted line around the selected range indicates that our data has been copied to the clipboard.

Step 2: Move the cursor to the first cell of the destination range which is cell E6.

Step 3: Press Ctrl+V.

Paste data using Keyboard shortcut

Step 4: Click on the down arrow of the Ctrl button at the right-bottom side on the pasted data. Or, you can click on the Ctrl button from the keyboard.

Step 5: From the Paste Options dialog box, click on the Keep Source Column Widths (W) option.

Keep source cell width intact using the Paste Options feature

Finally, the column width of the pasted data is the same as the source data. In the below section, we will show more ways to use the Paste Options button in Excel to copy and paste without changing the format.

2 More Ways to Paste Cells in Excel

You can also paste cells keeping the previous cells’ formatting in the following 2 ways. Then use the Keep Source Column Widths (W) option from the Paste Options button to make the column width the same as the source cells.

  • Copy B6:C16 Move to cell E6  go to Home ⇒ Clipboard group ⇒ Click on Paste.

Or,

  • Copy range B6:C16 Move to cell E6 right-click your mouse Choose Paste (P) from the Paste Options: section of the context menu.

3. Using Paste Special Dialog Box to Copy and Paste Keeping Format Unchanged

In this section, you will learn how to use the Paste Special feature or dialog box to copy and paste in Excel without changing the format. We can avail of this feature using the keyboard shortcut (Ctrl+Alt+V), from the Clipboard group, and Context Menu.

Follow these steps.

Step 1: Copy the source data.

Step 2: Move to cell E6 and click on the down arrow of the Paste option from the Clipboard group.

Step 3: Click on the Paste Special option from the shown options.

Avail Paste Special feature from the Clipboard group

The Paste Special window appears.

Step 4: Select All in Paste and None in the Operation section (those are the default options).

Step 5: Click on the OK button.

Choose desired options in the Paste Special window

Data has been pasted but cell width is not the same.

Data copied by applying the Paste Special feature

Again, we will use the Paste Special option to keep the cell width intact.

Step 6: Move to the 1st cell of the pasted data that is cell E6 and press the right button of the mouse.

Step 7: Choose the Paste Special option from the Context Menu.

Avail Paste Special from the Context Menu

Step 8: Choose the Column widths option in the Paste section and click on the OK button.

Choose Column widths in the Paste Special window

You can see the cell width of the pasted data is the same as the source data.

Column width is intact after applying the Paste Special feature

Read More: How to Copy Formatting in Excel


2 Mouse Tricks to Copy and Paste in Excel Without Changing the Format (Except for Column Width)

In this section, you will learn how to copy and paste in Excel keeping the format unchanged except the column width. One of the advantages of those methods is that we do not need to copy data separately, copy and paste will be completed in a single attempt.

i. Dragging with the Right Mouse Button

This is one of the easiest ways to copy and paste data with format. We will just select and drag that range using the right button of the mouse.

  • First, select range B6:C16.
  • Move the mouse to the edge of the selection range and wait until the four-directional sign appears.
  • Click and hold the right button of the mouse and drag the mouse to an empty space to paste.
  • Next, release the mouse button and a menu appears.
  • Click on the Copy Here option and data will be pasted from the source.

Copy and paste data by dragging the right button of the mouse

Read More: Copy Formatting in Excel to Another Sheet


ii. Press and Hold the Ctrl Key and Drag with the Left Mouse Button

In this section, we need to hold the Ctrl key and drag the left button of the mouse to copy and paste data with formatting.

  • Select range B6: C16 and hold the Ctrl key.
  • Move the cursor to any edge of the dataset and wait until the small plus (+) symbol appears.
  • Then, click on the left button of the mouse and drag the cursor to an empty cell.
  • Finally, release the left button and we will see that the data has been pasted to the new location.

Copy and paste data by holding the Ctrl key and dragging the left button of the mouse

Read More: Copy and Paste Formulas Without Changing Cell References


In this article, we have shown how to copy and paste data in Excel without changing the format. To copy-paste data without changing formatting, you can use the Keep Source Column Widths (W) command. Once you have have pasted data with a common paste command like Ctrl+V or paste commands from the ribbon or context menu, you can adjust the column width using Keep Source Column Widths command from the Paste Options button. You can also do all these using Excel’s Paste Special feature. Lastly, we have shown 2 tricky mouse techniques to copy and paste data without changing the format. But these tricks are unable to copy the column widths; keep this in mind! If this article is helpful, let us know, and place your query in the comment box. Happy Excelling!


Related Articles


<< Go Back to 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.
Alok Paul
Alok Paul

Alok Paul has completed his B.Sc. in Electronics and Telecommunication Engineering from East West University. He has been working on the ExcelDemy project for more than 2 years. He has written 220+ articles and replied to numerous comments. He is experienced in Microsoft Office, especially in Excel. He also led some teams on Excel and VBA content development. He has a keen interest in Advanced Excel, Data analysis, Excel Pivot Table, Charts, and Dashboard. He loves to research... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo