Rifat Hassan
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
Step 1: Select the first cell of the dataset you want to copy. In this example, I select the title “Fruit Name”.
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:
- If you want to select the whole column, select the first cell and then press Ctrl + Shift + Down Arrow.
- If you want to select the whole row, select the first cell and then press Ctrl + Shift + End.
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.
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.
Similar Readings:
- How to Copy and Paste Exact Formatting in Excel
- Copy and Paste Multiple Cells in Excel
- How to Copy the Same Value in Multiple Cells in Excel (4 Methods)
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.
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.
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.
OR
Click the Paste Special option.
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.
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.
Step 2: Right-click on your mouse. You will see options like this.
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.
OR
You may choose the Paste Special option.
Then choose either Paste or Keep Source Formatting or Keep Source Column Width option.
You will find everything including the formats pasted beautifully.
OR
You may again click Paste Special from the above options.
You will get the same dialogue box as above.
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.
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.
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:
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.
