The “Cannot paste the data” error in Excel usually happens when you copy a group of cells and try to paste them into a space that doesn’t match in size or format. This can be annoying, especially when you’re working with large amounts of data. The good news is that you can fix this problem easily by understanding what causes it and using the right solution.
Below are some common situations and how to fix them, along with a real example you can try in Excel.
Example Scenario Causing This Error:
Let’s take an example where copying data from three columns and pasting it into a single column causes this error.
|
Product |
Quantity |
Price |
|---|---|---|
|
Apple |
50 |
$1.2 |
|
Orange |
30 |
$1 |
When I try to copy the values from B2:D4 and paste them into cell E2, Excel shows a ‘Cannot paste the data’ error.
How to Fix This Error?
Yes, this can be fixed. You might need to make some changes or handle the cell values differently. Some of the possible fixes are listed below.
Fix 1: Match the Destination Range Size
- Instead of selecting just E1:E2, click only on E1 (a single cell).
- You can press CTRL + V to paste.
- Now, Excel will automatically paste the entire 3×3 block starting from E1.
Note: Always click on the top-left cell of the target area when pasting. Don’t pre-select a differently sized range.
Fix 2: Use Paste Special to Convert Format
Sometimes, this error can be caused by incompatible formatting or merged cells.
- You need to choose and copy cells B2:D4.
- Right-click on the destination starting cell (e.g., E1).
- Click the option Paste Special.
- Choose Values or Values and Number Formats, then click OK.
Pasting the values using the Paste Special option
Note: This is useful if the source contains formulas, merged cells, or special formats that cannot be directly pasted.
Fix 3: Unmerge Cells Before Copying or Pasting
If the copied data contains merged cells, then the Excel application will throw you the Cannot Paste the Data Error.
- For testing, you can merge the cells B2:C2.
- Then, try to copy the values from the cells B2:D4 and paste them into other cells.
This will cause the error. To overcome the issue, you must need to unmerge the cells first and copy the cells’ values.
- You need to choose the source cells.
- Now, go to the Home tab -> Click on Merge & Center -> Select the Unmerge Cells option.
Unmerging cells
- Now, you can copy and paste the cell values as usual.
Note: You need to avoid merged cells in tables or datasets that you plan to copy or analyze to prevent this kind of error.
Fix 4: Clear Filters Before Copying
If you are trying to copy & paste the values from filter-applied cells, you might encounter this issue.
- Firstly, you need to convert the range B2:D4 to a table using CTRL + T.
Convert cell values into a table.
- Then, you need to apply a filter on the column Product and select only “Apple.“
- Now, copy the visible data and try to paste it elsewhere.
Pasting the values in the new cells’ location might not work properly because Excel only copied the visible filtered cells. You can fix this issue by following the steps below.
- Using the Go To Special option will fix this temporary issue:
- You need to use the keyboard shortcut key F5 -> and you need to choose the Special option -> Now, select the Visible cells only and finally, click the OK button to complete the process.
- Now, you can copy and paste the values into the new cell, won’t cause any issues.
Fix 5: Paste Into a New Sheet
If none of the above solutions work for you, then the issue might be with the sheet or the formatting.
- In the current sheet tab at the bottom, you need to right-click on it and choose Insert -> Select Worksheet -> click the OK button.
Right-click on the sheet tab and choose the Insert option.
Now, you need to choose a worksheet and click the ok button.
- This will open a new sheet named “Sheet2“.
Sheet2 has been created.
- In the new sheet, click on cell A1 and paste the copied data.
This method will work especially when the original sheet has protection, hidden columns, or any strange formatting rules.
Fix 6: Use Keyboard Shortcut Instead of Right-Click Paste
This error may get triggered due to background clipboard glitches, so you can use the keyboard shortcuts such as CTRL + C and CTRL + V for copying the cell values and pasting the cell values.
General Solutions to Fix this Error in Excel:
The solutions listed below are general and not based on the values in the cells.
#1 Restart the Excel File:
- Save the currently working Excel file.
- Close the Excel application.
- Restart the Excel application and open the worksheet from the recent files list.
This process is simple yet effective. Restarting the Excel application will solve most of the errors that occur in worksheets.
#2 Disable the Macro Express Application:
This issue often happens on Windows if you’re running a Macro Express application. To fix it, check the background processes before copying data from an Excel sheet and close the Macro Express app if it’s running.
#3 Ignore DDE (Dynamic Data Exchange):
Ignoring Dynamic Data Exchange errors will fix the Excel cannot paste the data error. So, you need to disable the Dynamic Data Exchange (DDE) option.
- You need to choose the File and then choose the Options.
Open Options from Excel
- In the Excel Options window, you need to choose the Advanced tab from the left menu.
- Scroll down to the General Options settings.
- Now, you need to clear the checkbox next to Ignore other applications that use Dynamic Data Exchange (DDE).
Unchecking the DDE Checkbox will fix this error in Excel.
This is how you can fix this error in a way.
#4 Use XLSX Sheets Instead of XLS:
If you’re trying to copy a large amount of data, it’s better to use an XLSX file instead of the older XLS format. This is because XLS files are limited to 65,000 rows. So, if your data has more rows than that, you’ll likely see the “Excel cannot paste the data” error.
That’s it.