Find here the quick solutions to fix the “Microsoft Excel cannot paste the data” error when you are copying and pasting data from a different Excel workbook, worksheet, or a third-party app source.

The error Excel cannot paste the data could be rare but definitely annoying if it keeps showing up when you are working on a critical report. You may wonder why Excel is unable to paste data when it is the most popular spreadsheet software. There could be many reasons behind the error but the fixes are truly easy. Keep reading to learn them all!

Insert More Columns

When you import or copy a large dataset containing more columns than the destination worksheet, you see the following copy-pasting Error in Excel:

There are not enough columns to accept the information that you’re trying to paste.

Microsoft Support

To deal with this error message, just cancel the pasting process or press OK on the warning you see. Then, follow these steps to fix the issue:

1. Click on any of the column heading like A, B, C, or D, and right-click.
2. Select Insert on the context menu.

3. Excel will shift the selected column to the right and insert a new one in its place.
4. Repeat the process until you have added enough blank columns on the worksheet to accommodate the dataset you want to paste here.
5. Now, re-attempt pasting the data and this time you should not see the error.

Need to insert many columns in your Excel worksheet? You can use the following Excel VBA code. You shall also learn the steps to create a VBA macro and run it on Excel below:

1. Open the Excel workbook where you want to insert the new columns.
2. Call the Excel VBA Editor by pressing Alt + F11 keys together.
3. On the toolbar of VBA Editor, click Insert and choose Module to insert a new module.
4. Paste the following code into the module’s code window.

Sub InsertColumns()
    Dim ws As Worksheet
    Dim insertRange As Range
    
    ' Set the worksheet where you want to insert the new columns
    Set ws = ThisWorkbook.Worksheets("Sheet1") ' Replace "Sheet1" with your desired worksheet name
    
    ' Set the range where you want to insert the new columns
    Set insertRange = ws.Range("A1") ' Replace "A1" with the starting cell of your desired range
    
    ' Insert 5 new columns at the specified range
    insertRange.Resize(, 5).Insert Shift:=xlToRight
End Sub

5. Modify the ws variable to specify your desired worksheet. For example, you can replace "Sheet1" with the name of your worksheet.
6. Modify the insertRange variable to specify the starting cell of your desired range. For example, you can replace "A1" with the cell where you want to start inserting the new columns.
7. If you need more than 5 columns, replace the number 5 in the above code with a number of your choice.
8. Save the code and close the Excel Visual Basic Editor.

9. Run the macro by pressing Alt + F8, selecting the InsertColumns macro, and clicking on the Run button.

Excel will insert five new columns to the left of the selected cell in the above VBA script.

Note: By using any VBA macro, you make irreversible changes to your worksheet. So, create a backup of the worksheet before using any VBA codes in this article.

Also read: How To Use The VBA Code You Find Online

Resolve Cell Formatting Mismatch

Before pasting a large dataset from another source, ensure the cell format is similar to the content you are copying there. Here is how you can accomplish this:

1. Select the column or columns for which you need to change the cell formatting.
2. Press Ctrl + 1 to open the Format Cells dialog box.
3. Choose the desired category from the left-side menu.
4. You can choose the Number, Currency, Text, etc., formatting according to the source dataset.
5. Click OK to apply the changes.

This method is okay if the dataset you are pasting from a different worksheet is small. If it is a huge dataset, you can automate the process by creating and running a VBA macro using this Excel VBA script:

Sub FormatCellRanges()
    Dim SourceRange As Range
    Dim DestinationRange As Range
    Dim SourceCell As Range
    Dim DestinationCell As Range
    Dim Format As Variant
    
    ' Set the source range (the range containing the formatting to be matched)
    Set SourceRange = Range("A1:A10") ' Replace with your desired source range
    
    ' Set the destination range (the range where the formatting will be applied)
    Set DestinationRange = Range("B1:B10") ' Replace with your desired destination range
    
    ' Loop through each cell in the source range
    For Each SourceCell In SourceRange
        ' Find the corresponding cell in the destination range
        Set DestinationCell = DestinationRange.Cells(SourceCell.Row, SourceCell.Column)
        
        ' Copy the formatting of the source cell to the destination cell
        SourceCell.Copy
        DestinationCell.PasteSpecial Paste:=xlPasteFormats
        Application.CutCopyMode = False
    Next SourceCell
End Sub

The process to create a macro and run it is the same as explained above in this article. Before you can use this code, ensure you make the following changes:

• SourceRange = Range("A1:A10"): In this code element, enter the source reference.
• DestinationRange = Range("B1:B10"): Here Range("B1:B10") instructs Excel to paste data into the cell range B1:B10. Change it according to the destination cell range.

Select Cell Range of Same Size as Copied Data

Sometimes you are unable to copy data in Excel and get the following error or warning message:

“The Copy area and the Paste area are not the same size and shape.“

Microsoft Support

This error will show up on your worksheet if you are trying to copy a dataset, for example, A1:E100 to a destination range F1:I100. Excel should automatically adjust the destination cell range, but sometimes it may not do so. To fix this issue, follow these instructions:

1. Count the column headings of the source dataset you are copying.
2. Now, highlight the destination for the copied data accordingly. See the image below for clarity:

Unmerge Merged Cell Ranges

Suppose you are trying to copy and paste a dataset into the destination cell which is merged to the nearby cells. Then, you will see the following error code:

To fix this, you must unmerge the cells in the destination cell range. Here are the steps you should follow:

1. Highlight the destination cell range where you must copy a dataset.
2. Press the following keys on the keyboard to unmerge the highlighted section on the worksheet:

Alt > H > M > U

Sometimes, you may need to do this more than once if the issue exists in more than one worksheet or workbook. In that situation, use the following VBA script to automatically unmerge merged cells in the highlighted area.

Sub UnmergeSelectedRange()
    Dim rng As Range
    Dim mergedCells As Range
    Dim cell As Range
    
    ' Check if a range is selected
    If TypeName(Selection) <> "Range" Then
        MsgBox "Please select a range before running this macro.", vbInformation
        Exit Sub
    End If
    
    Set rng = Selection
    
    ' Loop through each cell in the selected range
    For Each cell In rng
        ' Check if the cell is merged
        If cell.MergeCells Then
            ' Store the merged cells range
            If mergedCells Is Nothing Then
                Set mergedCells = cell.MergeArea
            Else
                Set mergedCells = Union(mergedCells, cell.MergeArea)
            End If
        End If
    Next cell
    
    ' Unmerge the merged cells range, if any
    If Not mergedCells Is Nothing Then
        mergedCells.UnMerge
    End If
End Sub

Use Paste Special to Paste the Data

Suppose, you are trying to copy a dataset from an infographic, website, or Word document. You might get the “Microsoft Excel cannot paste the data” error here as well. However, you can get rid of the error by using the Paste Special tool of Excel when copying data or content from an external source. Find below the simple steps:

1. Copy the content or data from the source.
2. Go to your worksheet and select the cell where you want to paste the copied data.
3. Right-click and hover the cursor over the Paste Special overflow menu.
4. Go to the Other Paste Options section and select Picture.

5. Excel will paste the data or content as a picture.

Enable Ignore Dynamic Data Exchange

The Dynamic Data Exchange (DDE) protocol is a collection of guidelines and messages that enable different applications on your Windows PC to share data from the same memory pool. This also allows updating the dataset in all the applications that imported data from one another using DDE.

If you face issues when pasting large datasets in your Excel worksheet, you can enable Ignore DDE setting. Several users reported that this trick also works. Here is how you can do this:

1. Click the File tab on your Excel worksheet.
2. Select the Options button on the sidebar.
3. The Excel Options dialog box will open.
4. Click the Advanced category on the left.
5. Scroll down to the General section on the right and select the option Ignore DDE…
6. Click OK and close the dialog box.

Now, you can try again to copy and paste the data

Save Excel File in XLSX Format

The latest Excel desktop apps, like Excel 2019, Excel 2021, and Excel for Microsoft 365 saves all the Excel workbooks as XLSX file. However, it is possible you get an Excel workbook in XLS format from a colleague or a client. If you try to copy and paste a large dataset in it containing 65,536+ rows, you shall get an error. To resolve this, you must convert the XLS file to XLSX by following these steps:

1. Click the File tab and choose Save As on the sidebar.
2. Click the Browse button under the Save As section.

3. Choose a destination directory on the Save As dialog box.
4. Click the Save as type drop-down menu and choose XLSX as the target format.
5. Click Save.

Repair the Excel File

Sometimes, Excel is not able to copy and paste data because of issues in the Excel file itself. To check if you can fix the issue by repairing it, try these steps:

1. Close the worksheet.
2. Open Excel by clicking its icon on the Start menu of your PC.

3. A blank Excel worksheet will open. Click the File tab.
4. Click Open on the sidebar.
5. Hit the Browse button.
6. Go to the directory of the faulty workbook and select the file.
7. Click the drop-down arrow on the Open button and choose Open and Repair.

8. Hit Repair on the pop-up that shows up.

9. Excel will open the file, repair it, and show this dialog box:
10. Click Close to complete the process.

Now that you have attempted repairing the Excel file, try to copy and paste your dataset and see if Excel can paste the data or not.

Restart Excel Desktop App in Safe Mode

When Excel Add-ins interfere with the functions of the software, you may also experience the “Microsoft Excel cannot paste the data” error. To fix this, you need to launch Excel without loading the installed Add-ins. The best way to accomplish this is by launching the Excel desktop app in safe mode. Here are the steps you can follow:

1. Close the Excel workbook.
2. Select the Excel file in its directory.
3. Press the Ctrl key and double-click the Excel file to open it.
4. If there is no response on the first attempt, double-click again. Don’t release the Ctrl key yet.
5. Excel will show a warning about restarting it in safe mode.
6. Click Yes to launch the app in safe mode.

Now, try to copy and paste the dataset and see if the issue got fixed or not. If you were able to copy data, save the file. Next time, open the file normally to load the Excel Add-ins.

If you continue to experience the issue in normal mode, uninstall any recently installed Excel Add-in to fix the issue for good.

Conclusion

So, these are all possible and reliable ways to fix the “Microsoft Excel cannot paste the data” error. Try the methods in the order of their appearance to invest less time in troubleshooting. Comment below to let other readers know which method worked for you.

Have you come across this issue before? How did you solve the problem? Let me know in the comments!