How to Copy and Paste in Excel Without Changing the Format

Selecting the Whole Data Set

Today I’ll be showing how to copy in Excel without changing the format from a data set and then paste it.


How to Copy and Paste in Excel Without Changing the Format

First Copy Your Data Set to Copy and Paste in Excel Without Changing the Format

Let us have a dataset like this. 

We have a dataset of various fruits, with the price per kg of them available in the market, with the quantity each of them bought and the total cost spent.

The total cost is the product of Price per Kg and Quantity. So the formula of each cell of Column E (Total cost) is:

=C4*D4

Data Set to Copy and Paste in Excel Without Changing the Format

    Step 1: Select the first cell of the dataset you want to copy. In this example, I select the title “Fruit Name”.

    Selecting the First Cell

    Step 2: Now drag the bold Plus Sign(+) to select all the rows and columns you want to copy. In this case, I select the whole data set.

    Small Tips:

    Step 3: Right-click on your mouse and select Copy.     

    OR

    Press Ctrl + C on your keyboard.

    OR

    Select the Copy option from the Excel Toolbar. It is on the leftmost side in the upper toolbar under the Home option. Home>Copy. Look at the Figure.

    Copy Option in Excel Toolbar to Copy and Paste in Excel Without Changing the Format

    Step 4: After successfully copying the desired cells, you will see the border of the cells somehow highlighted like this. This means you have successfully copied the cells.

    Border of the Cells Highlighted After Being Copied to Copy and Paste in Excel Without Changing the Format


    Similar Readings:


    Then Paste to Copy and Paste in Excel Without Changing the Format

    1. Choosing Paste Option from the Excel Toolbar

    Step 1: First click on the desired cell where you want to copy the contents. It may be on the same worksheet or another worksheet.

    In this example, I am selecting a cell from another worksheet.

    Step 2: Now navigate to the Paste option in the Excel Toolbar under the Home menu. Home>Paste.  See the following figure.

    Paste Option in Excel Toolbar to Copy and Paste in Excel Without Changing the Format

    Step 3: Click on the drop-down menu (small inverse triangle just below the word “Paste”) associated with the Paste option. You will get these options.

    Paste Menu Options in Excel

    Step 4: From the Paste menu, select either Paste or Keep Source Formatting or Keep Source Column Width.

    Note: The best option is to choose Keep Source Column Width option. It pastes everything including the formula, format and column width of the source cell. Other options do not keep the column width intact.

    You will get the copied cells pasted with the format intact.

    Pasted with Format to Copy and Paste in Excel Without Changing the Format

    OR

    Click the Paste Special option.

    Paste Special Option to Copy and Paste in Excel Without Changing the Format

    You will get a dialogue box like this.

    Choose All from the Paste menu.

    None from the Operation menu.

    Keep the Skip blanks and Transpose tool unchecked, just as shown in the following figure.

    Paste Special Dialogue Box Filled to Copy and Paste in Excel Without Changing the Format

    Then click OK.

    You will get the same result as earlier.

    Note: If you do not want to paste everything of the source cell, just some particular things, then this Paste Special dialogue box will be very helpful. 


    2. Choosing Paste Option by Right Clicking the Desired Cell

    If you do not want to follow the previous procedure, you can follow this procedure and paste without changing the format. 

    Step 1: Select the first cell where you want to paste the database. This may be in the same worksheet or another worksheet. Just like this.

    One Cell from Another Worksheet is Selected to Copy and Paste in Excel Without Changing the Format

    Step 2:  Right-click on your mouse. You will see options like this.

    Paste Options after Right Click to Copy and Paste in Excel Without Changing the Format

    Step 3: See there are a few options under the menu Paste Options. Move your cursor and click on Paste.

    You will see everything including the format has been pasted. Just like the earlier one.

    Pasted with Format to Copy and Paste in Excel Without Changing the Format

    OR

    You may choose the Paste Special option. 

    Then choose either Paste or Keep Source Formatting or Keep Source Column Width option.

    Paste Special Options from Right Click to Copy and Paste in Excel Without Changing the Format

    You will find everything including the formats pasted beautifully.

    OR

    You may again click Paste Special from the above options.

    Paste Special after Right Click to Copy and Paste in Excel Without Changing the Format

    You will get the same dialogue box as above.

    Paste Special Dialogue Box to Copy and Paste in Excel Without Changing the Format

    Choose All from Paste menu, None from Operation menu and keep the Skip blanks and Transpose tool unchecked, just as shown in the figure below.

    Then click OK.

    You will get the same result as earlier.

    Pasted with Format to Copy and Paste in Excel Without Changing the Format


    3. Choosing Paste Option from Keyboard Shortcut (Ctrl + V)

    If you do not want to follow any of the above two methods, then follow this method.

    Step 1: Select the cell where you want to paste the database. This may be in the same worksheet or another worksheet.

    Step 2: Now click Ctrl + V on your keyboard. You will see everything has been pasted, formats and formulas. Just like the previous ones.

    Pasted with Format to Copy and Paste in Excel Without Changing the Format

    Step 3: You can finish here. Or you may dig a little deep. You will see a small box called Ctrl in the rightmost bottom corner of the pasted cells. Like this:

    Control Option after Pasting to Copy and Paste in Excel Without Changing the Format

    Step 4: Click on Ctrl. You will find the same box as earlier.

    Then follow any of the above two procedures.


    Conclusion

    Using these methods, you can copy and paste data from one cell to another without changing the format pretty conveniently. Do you know any other method? Let us know in the comment section.


    Further Readings

    Rifat Hassan

    Rifat Hassan

    Hello! Welcome to my profile. Here I will be posting articles related to Microsoft Excel. I am a passionate Electrical Engineer holding a Bachelor’s degree in Electrical and Electronic Engineering from Bangladesh University of Engineering and Technology. Besides academic studies, I always love to keep pace with the revolution in technology that the world is rushing towards day by day. I am diligent, career-oriented, and ready to cherish knowledge throughout my life.

    We will be happy to hear your thoughts

    Leave a reply

    ExcelDemy
    Logo