Excel is sometimes unable to show the Paste Options, usually due to a setting, protected sheet, or corrupted Excel file. In this article, we will show how to enable or re-enable the Paste Options in Excel.
To demonstrate our solutions, we will use the following dataset data containing Country in column B and National Sport in column C.
Method 1 – Enabling Paste Option from Excel Advanced Options
The Paste Options are not showing the following image, but we can bring them back by changing some settings.
Steps:
- Go to the File tab and select Options from the menu.
- Select Advanced.
- Mark the box beside ‘Show Paste Options Button When Content Is Pasted’.
- Click OK.
The Paste Options are now displayed.
Read More: How to Paste From Clipboard to Excel Using VBA
Similar Reading
- Excel Formula to Copy Cell Value from Another Sheet (4 Examples)
- Copy Rows from One Sheet to Another Based on Criteria in Excel
- Automatically Update One Worksheet from Another Sheet in Excel
- How to Copy a Worksheet in Excel (4 Smart Ways)
Method 2 – Unprotecting a Protected Worksheet
In a protected sheet, the Paste Options are disabled to prevent editing of data in the sheet. Unprotecting the sheet will re-enable these Options.
In the following image, we have tried to copy and paste the cells in B4:C14 to cell E4.
We receive a message: “The cell or chart you’re trying to change is on a protected sheet. To make a change, unprotect the sheet. You might be requested to enter a password”.
So, to enable copy and paste in Excel, we’ll first have to unprotect the sheet.
Steps:
- Go to the Review tab.
- Click Protect >> Unprotect Sheet.
The Unprotect Sheet dialog box opens.
- Enter the password to unprotect the sheet.
Copy and paste options are enabled.
Read More: How to Disable Copy and Paste in Excel without Macros (With 2 Criteria)
Method 3 – Enabling Copy & Paste Options for Visible Cells Only
Suppose we have a hidden column in C, but we want to copy the visible cells only. If we copy the cells and paste them into cell F4, the hidden column C is also pasted.
Let’s prevent the hidden column from being copied.
Steps:
- Go to the Home tab.
- Click on Find & Select under the Editing group.
- Choose Go To Special….
- Alternatively, press Ctrl + G and click on Special… from the Go To window.
- Put the radio button on Visible cells only under the Select options.
- Click on OK.
Now only the visible cells that were copied are pasted.
Read More: How to Copy and Paste Multiple Cells in Excel (8 Quick Methods)
Related Articles
- Use Paste Name Dialog Box In Excel (3 Ideal Examples)
- How to Paste a List of Emails into Excel (2 Easy Ways)
- Paste Comma Separated Values into Excel (in Different Orders)
- [Fixed!] Paste Special Not Working in Excel (4 Possible Solutions)
- How to Copy Horizontal and Paste Vertical in Excel
- Copy and Paste Pivot Table Values with Formatting in Excel
- How to Copy and Paste in Excel Without Hidden Rows
