Having trouble with copying and pasting data in Excel? Copied the data but can’t paste it without changing the format? Come on! Have a break. Today I’ll be showing how to copy in Excel without changing the format from a data set and then paste it.
Download Practice Workbook
You can download the practice book from the link below.
Watch Video – Copy and Paste Without Changing the Format in Excel
Copy the Dataset in Excel
Let’s say, we have a dataset of various fruits, with the price per kg of them available in the market, the quantity each of them bought, and the total price of the corresponding fruits.
The total cost is the product of Price per Kg and Quantity. So the formula of each cell of Column E (Total cost) is:
Let’s start the procedure!
Step 1: Select the first cell of the dataset you want to copy. In this example, I select the title “Fruit“.
Step 2: Now hold the Fill Handle tool with the cursor and drag it to select all the rows and columns you want to copy. You can also press CTRL+SHIFT+END In this case, I select the whole data set.
- 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.
Press CTRL + C on your keyboard.
Select the Copy option from the Excel Toolbar. It is on the leftmost side in the upper toolbar under the Home option.
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.
Paste the Copied Data Without Changing the Format
You can paste the copied data with any of the following ways.
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 the Paste option in the Excel Toolbar under the Home menu and 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 3: Select Paste or Keep Source Formatting or Keep Source Column Width from the Paste menu.
💭Note: The best option is to choose the Keep Source Column Width option. It pastes everything, including the source cell’s formula, format, and column width. Other options do not keep the column width intact.
- You will get the copied cells pasted with the format intact.
- Click the Paste Special option.
- You will get a dialogue box like this.
- Choose All from the Paste menu and None from the Operation icon, keep the Skip blanks and Transpose tools unchecked. 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.
Read More: How to Copy and Paste Exact Formatting in Excel (3 Quick Ways)
- Exchange (Copy, Import, Export) Data Between Excel and Access
- Copy and Paste Multiple Cells in Excel
- How to Copy the Same Value in Multiple Cells in Excel (4 Methods)
- Copy and Paste is Not Working in Excel (9 Reasons & Solutions)
- How to Use VBA to Paste Values Only with No Formatting in Excel
2. Choosing the Paste Option by Right-Clicking the Desired Cell to Keep Format
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. Choose Paste from the Paste Options.
- You will see everything including the format has been pasted. Just like the earlier one.
- You may choose the Paste Special option.
- Then choose either Paste or Keep Source Formatting or Keep Source Column Width option.
- You may again click Paste Special from the above options.
You will get the same dialogue box and the same result as described in the previous method.
Read More: How to Copy and Paste in Excel without Formulas (7 Easy Tricks)
3. Using Keyboard Shortcut to Paste Data Without Altering Format
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.
- 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.
- Click on Ctrl. You will find the same box as earlier.
Then follow any of the above two procedures.
Read More: Formula to Copy and Paste Values in Excel Automatically
Using these methods, you can copy and paste data in Excel without changing the format pretty conveniently. If you have any better method, don’t forget to share them in the comment box.
Have a great day!
- How to Copy Excel Sheet to Another Sheet with Same Format
- Copy a Worksheet in Excel
- How to Copy and Paste in Excel with Merged Cells (2 Methods)
- Copy Visible Cells Only in Excel
- How to Copy Visible Cells Only without Header Using VBA
- Paste Options in Excel: A Complete Guideline
- How to Copy and Paste in Excel When Filter Is On (5 Methods)