How to copy paste data in Excel
When we work with Excel worksheets, we may require the same data at multiple places. Each time writing the same data takes a lot of time. So, it is good to use the copy and paste option in Excel. It will save your time and effort to do the same task again and again.
Not only Excel but almost all text and image editors allow copying data and pasting it to another place. For this, they all have a common copy and paste shortcut command. Ctrl+C to copy the data and Ctrl+V to paste the data.
In this chapter, we will take examples to brief the copy-paste options in Excel and how they are used in Excel.
Excel copy paste options
Excel enables the user to copy the data from one or more cell and paste to another cells, worksheet, or wherever you want. Besides the simple copy-paste operation, it also offers an option of special paste.
Note: The simplest trick to copy and paste data is – Select targeted data + press Ctrl+C + go where you want to paste the data + press Ctrl+V.
Excel provides different types of paste options to paste the data differently like simple paste and special paste. We will show you all these methods in this chapter with their detailed example.
The one biggest advantage of copy and paste option is that – it does not remove the data from where you have copied. Whereas, if you cut and paste the data from one place to another, it removes the data from the previous place.
Step to simple copy-paste in Excel
These steps are containing an example of the simple copy-paste of data. This data does not contain any formula, formatting, or validation. It needs only a few simple steps, same as other editors and you can easily copy the data to another place.
Following are the steps to copy the data and paste it to another place by using a simple copy-paste operation.
Step 1: We have this data stored in an Excel worksheet. Select the data which you want to copy or use the Ctrl+A to select all data.
Step 2: We have selected the first four rows including header, to copy from this Excel sheet. You can also select a particular cell, row, or column data.
Step 3: Now, use the Ctrl+C shortcut command to copy the selected data. (You can also copy the data by right-clicking on selected data and then choose the Copy command.)
Step 4: Go to the place where you want to paste the data and take the cursor there, then use the Ctrl+V shortcut command to paste the copied data.
We have added a new sheet in the same Excel file to place the copied data.
Step 5: You can see that the data is successfully pasted to another place without any interruption. Similarly, you can paste the data wherever you want.
Increase the size of the column width to make the data completely visible. Now, save the file to keep the changes done here.
Note: When you copy and paste the data to another place, the original data does not remove from the place you copied. It still remains at its location.
Step 6: You can also go back to the sheet from where you copied the data to see that it is still there.
It was an example of simple copy-paste.
Paste special method
The above steps we have described are only for simple data to copy-paste. A simple copy-paste operation will not work if your data contains any formatting, formula, or validation applied to it. It would help if you used Paste Special option to copy-paste the values with formatting.
We will define the steps for it. This method will take more steps than the above example. Following are the steps to copy the data and paste it with or without formatting applied to it using Paste Special method to another place by using simple copy-paste operation.
When to use this method?
For example, we have a dataset with formula, formatting, or conditional formatting applied on this data. If you simply copy-paste this column value, cell reference will be changed. This will cause that all values become 0 and formatting will also not keep as it is.
Hence, we will suggest you use the Paste Special method. We will use Paste special option to keep the formatting even after pasting the data.
Examples
In this chapter, we will show you three different examples to illustrate the usage of the Paste Special method. With the help of them, you will better learn that when and how to use Paste Special method.
Learn these examples in detail one by one.
Copy only value without color formatting
Sometimes, we apply color formatting on Excel data to analyze the data based upon certain conditions. We use conditional formatting on Excel data for this. In case If we need this data at another place but without color formatting, the simple copy-paste method does not work. It copies the data along with color formatting.
We can use Paste Special option to keep the only data without color formatting. Copy the targeted data and paste it using Paste Special method. You have to choose a format while pasting the data without formatting.
Let’s see with an example to copy data without color formatting –
Step to copy and paste special
Step 1: We have the following data with color formatting in an Excel worksheet. Select this data and copy it using the Ctrl+C shortcut command.
Step 2: We want to paste this data into a new worksheet. So, press CTRL+N command to create a new worksheet.
Step 3: Right-click here and choose Paste Special option from this list.
Step 4: A Paste Special panel will open where select Value radio button under Paste option and click OK.
Step 5: Now, you will see the copied data is pasted along column width and width of the column has not destroyed as a simple paste.
Copy with column width
You have seen that when you copy the data and paste it simply, the width of the column is changed. We have to increase and decrease the width of the column manually. It looks something like this –
We have a solution for it, i.e., Paste Special. You can copy and paste the data with column width using it. Paste Special method enables several advance features to keep the formatting and formulas as it is. It keeps the cell formatting same.
Let’s see with an example to copy data with column width –
Example
Step 1: Copy the Product column data simply using the Ctrl+C shortcut command.
Step 2: Go to the cell or worksheet where you want to paste the data.
Step 3: Right-click here and choose Paste Special option from this list.
Step 4: A Paste Special panel will open where select column width radio button under Paste option.
Leave other options as default and click OK.
Step 5: Now, you will see that it has been automatically adjusted the column width according to the width of the copied data.
Step 6: Now, use the CTRL+V shorted key to paste the copied data here in the selected cells/column (Column F).
You can note that the width of the column has not been destroyed as simple paste. Similarly, you can keep the formatting with the help of Paste Special option.
Copy only value not formula
Sometimes, users only want to copy the value, not the formula applied to it. If we simply copy and paste the values from one place to another, formula applied to those values will also come with it. It might be possible that the cell reference changes, and you got an incorrect value (0 or anything else).
For example – total price = Price*Quantity, i.e., D= B*C. It means column D value comes with the multiplication of price and quantity.
See how formula come with it when we paste the data simply –
We have a solution for it, i.e., Paste Special. Use the Paste Special method to paste only value not the formula, along with it.
Let’s see for its solution using Paste Special method –
Example
Step 1: Copy the Product column data simply using the Ctrl+C shortcut command.
Step 2: Go to the cell or worksheet where you want to paste the data.
Step 3: Right-click here and choose Paste Special option from this list.
Step 4: A Paste Special panel will open where select Values radio button under Paste option.
Leave other options as default and click OK.
Step 5: Now, you will see that copied data is pasted to column F successfully.
Step 6: You can click any of the cells of column F (pasted data) and see that there is no formula is applied on the pasted value now.
Now, these values are simple values with no formula.
