In this article, we will present various examples of how to paste in Excel. These examples will demonstrate everything, from the process of applying basic paste commands to the application of special paste commands.
The “Paste” is a fundamental operation in Excel that allows us to copy and transfer data from one location to another. It is often used for copying and pasting data, replication of formulas, transposing a range, pasting only values, formats, or comments, etc. purposes.
Download Practice Workbook
You may download the following file and practice yourself.
How to Paste in Excel
Here, we will discuss the basic Paste commands.
1. Use Paste Command
Here, we have some data in the range F4:G15. We want to move this data to Cell D4. For this, first, select the range F4:G15 and click on the Cut option from the Clipboard menu.
Afterward, select Cell D4 and click on the Paste option from the Clipboard menu.
You can get the Paste command from the right-click of your mouse also.
The output looks like the following after you use the Paste option.
2. Paste Applying Keyboard Shortcuts
Excel has keyboard shortcuts for various Paste options. For example, we can paste any copied or cut range using the keyboard shortcut Ctrl + V.
We will have a similar output as the previous example.
3. Paste as Values
Here, we have applied the following formula to get the Tax values from salaries. Now, let’s assume you require only the values here.
=D5*18.5/100
First, select the range E5:E15 and click on the Copy option from the Clipboard menu or mouse right-click. You can also use the keyboard shortcut Ctrl + C to copy the range.
After that, select the Cell E5 >> click on the dropdown menu of the Paste option >> select the Values option.
This will substitute all the formulas with only values.
4. Paste Formulas Only
Conversely, if you want to paste formulas only, first, select the cell or range with the required formula and click on the Copy option from the clipboard menu.
Afterward, select the range where you want to paste the formula >> click on the dropdown menu of the Paste option >> click on the Formulas option.
The formula will be pasted into the selected cells.
5. Paste Transpose
To paste any range in transpose (i.e. interchanging rows and columns) in Excel, select the range and click the Copy option from the Clipboard menu or mouse right-click.
Select the cell where you want to paste the range >> right-click on the mouse >> select Transpose from the Paste Options.
The output looks like the following after using the paste option.
Read More: Use Paste Options in Excel
6. Paste Cell Formatting
Here, we have calculated the Balance after deducting the Tax from the Salary using the following formula:
=D5-E5
However, the cells are not formatted yet. To paste the existing formatting in these cells, first, select the range E4:E15 and click on the Copy option from the Clipboard menu.
After that, select Cell F4 >> click on the dropdown menu of the Paste option >> select the Formatting option.
The output after using the paste option is given below.
7. Paste Formulas Without Changing References
When we paste cells that include formulas, the cell references change accordingly. But sometimes, we may require pasting formulas without changing the references.
For that purpose, select the range you want to copy >> from the Home tab click on the dropdown menu of the Find & Select option >> select the Replace option.
In the Find and Replace dialog box, set the Find what option to = and the Replace with option to # >> click the Replace All button.
Now, select the range again and click on the Copy option from the Clipboard menu.
After that, select the cell where you want to paste the copied range and click on the Paste option from the Clipboard menu.
Next, select this pasted range >> from the Home tab click on the dropdown menu of the Find & Select option >> select the Replace option.
In the Find and Replace dialog box, set the Find what option to # and the Replace with option to = >> click the Replace All button.
The cell values will return to normal with cell references in the formulas being unaltered.
8. Paste Without Copying Hidden Cells
Here, we have a few hidden cells in our dataset. If want to avoid these hidden cells while pasting, then select the range >> from the Home tab, click on the dropdown menu of the Find & Select option >> select the Go To Special option.
In the Go To Special user form, select the radio button of the Visible cells only option and click on the OK button.
Afterward, click on the Copy option from the Clipboard menu.
Then, select the cells where you want to paste the copied range and click on the Paste option from the Clipboard menu.
As you can see, there are no hidden cells present in the pasted range.
9. Paste Non-Adjacent Cells
In all of the previous examples, we pasted cells that were adjacent. Here, we will discuss the process of pasting non-adjacent cells.
9.1 Cells Are Not in Same Row/Column
If we select non-adjacent cells from different rows or columns and try to copy them, we will get an error notification.
9.2 Cells Are in Same Row/Column
However, if the cells are from same row or column, then we can copy them.
And paste them to our desired location using the Paste option or the Ctrl + V command.
9.3 Copy and Paste Value from Above Cells
Here, we have a dataset that contains the sales values and profits of different departments in different years. However, there are a few blank cells in the Year column and we want to copy and paste values from the above cells here.
For that, select the range B5:B16 >> click on the dropdown menu of the Find & Select option >> select the Go To Special option.
Finally, in the Go To Special user form, select the radio button of the Blanks option and click on the OK button.
Enter the following formula in Cell B5 >> Hold the Ctrl key >> press the Enter key.
=B5
All the blank cells will be filled with copied values from the above cells.
10. Move Cells by Drag and Dropping
We can also apply the drag and drop method to move (i.e. cut and paste) any range. For that, select the range you want to move >> hover your mouse pointer around the border of the selected range >> when the Drag icon gets visible, drag the selected range to the required location.
The output looks like the following:
3 Suitable Examples to Use Paste Special in Excel
The Paste Special feature in Excel provides additional options for pasting data with more control over what is pasted. Apart from the paste options discussed in the previous section, we can use the following options.
1. Paste Comments
To paste comments from a cell, select the cell and click on the Copy option from the Clipboard menu.
After that, select the cell or cells where you want to paste the comment >> click on the dropdown menu of the Paste option >> select the Paste Special option.
Finally, in the Paste Special dialogue box, select the radio button of the Comments and Notes option >> click on the OK button.
The comment will be added to the selected cells.
2. Paste Column Width
When we paste something with large data (number or string) in any cell, later we have to adjust the column widths in those cells. With Excel’s Paste Special feature, we can paste any data with the source cell’s column width. We can even paste only the column width of any cell.
2.1 Column Width with Cell Content
To paste any range with column width and cell content, first, select the range and click on the Copy option from the Clipboard menu.
After that, select the cell where you want to paste the range >> click on the dropdown menu of the Paste option >> select the Keep Source Column Width option.
The selected range with be copied with the source cell’s columns width.
2.2 Only Column Width
Here, the elements of the Tax column are not visible due to the lesser column width. To paste only the column width of the Salary column, select any cell from that column and click on the Copy option from the Clipboard menu.
Afterward, select any cell from the Tax column >> from the dropdown menu of the Paste option, select the Paste Special option.
From the Paste Special dialogue box, select the radio button of the Column widths option and click on the OK button.
The elements of the Tax column are now visible as the column width has been increased.
Read More: How to Use Paste Special Command in Excel
3. How to Paste and Add/Subtract/Multiply/Divide At a Time in Excel
We can also perform operations like Addition, Subtraction, Multiplication, or Division while pasting values. Here, we present two ideal examples.
3.1 Replacing Percentages with Actual Values
Here, we have a list of salaries and the percentage of taxes for some employees and we want to replace the tax percentages with actual values.
First, select the range D5:D15 (i.e. salary values) and click on the Copy option from the Clipboard menu.
Afterward, select the range E5:E15 (i.e. tax percentages) >> click on the dropdown menu of the Paste option >> Select the Paste Special option.
Finally, in the Paste Special dialog box, select the radio button of the Multiply operation and click on the OK button.
The tax percentages will be converted to the actual values based on salary values.
Read More: How to Use Paste Options in Excel
3.2 Remove Multiple Hyperlinks At a Time
Here, we have the employee profiles linked to the employee names. If you want to remove the links from these values, then click on any random cell with numbers (e.g. Cell E5) and click on the Copy option from the Clipboard menu.
Afterward, select the range with links >> click on the dropdown menu of the Paste option >> select the Paste Special option.
In the Paste Special dialog box, select Add or any other Operation and click on the OK button.
As you can see, all links are now removed.
[Fixed!] Paste Special Not Working in Excel
If the Paste Special feature is disabled, we may not find it in the right-click menu. In such cases, we have to enable the Paste Special feature to work with it.
To start, go to the File tab.
Afterward, click on the Options menu.
Finally, in the Excel Options dialog box, click on the Advanced option >> scroll down and make sure the checkbox of the Show Paste Options button when content is pasted option is checked >> click on the OK button.
Things to Remember
- You can use the keyboard shortcut Ctrl + C to copy or Ctrl + X to Cut any data as an alternative to using the Clipboard or right-click menu.
- If you want to Copy a range while using Drag and Dropping method, hold the Ctrl key.
- We can’t copy cells that are non-adjacent and are not from the same rows or columns.
Frequently Asked Questions
- How do I paste data in Excel using keyboard shortcuts?
Answer: There are numerous keyboard shortcuts available for pasting data in Excel. Here, we have listed a few of them:
Purpose | Keyboard Shortcut |
---|---|
Paste the cell contents and formatting | Ctrl + V |
Paste only values | Ctrl + Shift + V |
Paste Only Formulas | First, Ctrl + Alt + V and then F |
Paste Transpose | First, Ctrl + Alt + V and then E |
Paste Only Formatting | First, Ctrl + Alt + V and then T |
Paste Only Comments | First, Ctrl + Alt + V and then C |
Paste Values and Number Formats | First, Ctrl + Alt + V and then U |
Paste Only Column Width | First, Ctrl + Alt + V and then W |
Add pasted data to the data in destination cells | First, Ctrl + Alt + V and then D |
Subtract pasted data to the data in destination cells | First, Ctrl + Alt + V and then S |
Multiply pasted data to the data in destination cells | First, Ctrl + Alt + V and then M |
Divide pasted data to the data in destination cells | First, Ctrl + Alt + V and then I |
- What is the difference between regular paste and paste special in Excel?
Answer: With regular paste, we can paste the copied or cut data into the selected cell or range exactly as it was copied (with formatting and formulas).
But the Paste Special feature allows us to apply additional options such as pasting only formulas, formatting, column widths, or comments. It also lets us perform various operations while pasting.
- Can I paste data from other applications, like Word or web browsers, into Excel?
Answer: Yes, you can paste data from other applications like Microsoft Word, web browsers, and many other sources into Microsoft Excel. We can copy text, numbers, dates, and other information from other applications and paste it into Excel. Excel will attempt to interpret the data and format accordingly.
However, If the data is not recognized properly or requires additional formatting, you may need to adjust it manually after pasting.
Conclusion
This concludes our article on how to paste in Excel. We discussed several examples of Paste and Paste Special features in Excel. We hope that the demonstrated examples were helpful in your quest for learning how to paste in Excel. Let us know your feedback. Visit our website ExcelDemy.com for more articles related to Excel.
<< Go Back to Copy Paste in Excel | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!