Masum Mahdy
Paste options in MS Excel give us the opportunity to choose certain elements and keep them in the destination cells. In this article, we will show all the Paste Options, necessary Paste Shortcuts, and Issues with Paste Options with proper illustrations.
Read More: How to Copy and Paste in Excel Using VBA (7 Methods).
Download Practice Workbook
You can download the practice workbook from the following link, and practice along with this.
How to Add Paste Options Button in Excel
First of all, we are going to show how to add a paste options button. If you add this, you can choose options from the button after you copy and paste content to a location. Just do the following.
- First, go to File tab > Options
A dialog box will appear.
- Then, go to Advanced > Cut, Copy, and Paste section > Mark the Show paste options button when content is pasted checkbox.
- Finally, press OK.
Using Excel Paste Options from the Ribbon, Context Menu, or Paste Options Button
Paste Options are visible in the Paste menu which is located in the Home tab’s Clipboard group. We can find them in the Context menu with the mouse right-click. Not only that, paste options are available in a paste options button. The following images show where the Excel paste options locate.
 |
 |
 |
Now, we are going to use the following dataset to demonstrate different paste options and their respective outputs.
Next, let’s see all the paste options, their icons, and their outputs in a table.
| Options | Icon | Paste Attributes & Outputs |
|---|---|---|
| Paste (P) |  |
» Each and Everything from the source dataset except the column widths. |
| Formulas (F) |  |
» Paste the formulas together with the values from the source dataset. You don’t have to enter the formula manually. |
| Formulas & Number Formatting (O) |  |
» Formulas from copied cells and number format. |
| Keep Source Formatting (K) |  |
» Keep the formatting of the source dataset intact, at the same time pasting other cell contents (except Column width). |
| No Borders (B) |  |
» Paste everything but the borders. |
| Keep Source Column Widths (W) |  |
» Everything from the source dataset, i.e. the values, formats, formulas, etc. including the column widths. |
| Transpose (T) |  |
» Transpose the pasted rows to columns and vice versa (formulas, formats, etc. are the same as source). |
| Values (V) |  |
» Only the values (text, number, etc.). Number format, fill color, etc. won’t be copied. |
| Values & Number Formatting (A) |  |
» Only values (text, number, etc.) and number format (not other formattings, formulas, etc.). |
| Values & Source Formatting (E) |  |
» Paste the values and source formats (not the formulas). |
| Formatting (R) |  |
» Formatting only, i.e. number format, fill color, border, font size, etc. |
| Paste Link (N) |  |
» Creates a link with the source dataset so that any change in values (not format) there will be reflected in the pasted data. |
| Picture (U) |  |
» Paste a picture of the original dataset. |
| Linked Picture (I) |  |
» A linked image of the source dataset. Any change in the source causes the same change in the image. |
Utilizing Paste Options Shortcuts
- Paste (P): ALT + H + V + P
- Formulas (F): ALT + H + V + F
- Formulas & Number Formatting (O): ALT + H + V + O
- Keep Source Formatting(K): ALT + H + V + K
- No Borders (B):ALT + H + V + B
- Preserve Source Column Widths (W): ALT + H + V + W
- Transpose (T): ALT + H + V + T
- Values (V): ALT + H + V + V
- Values & Number Formatting (A): ALT + H + V + A
- Values & Source Formatting (E): ALT + H + V + E
- Formatting (R): ALT + H + V + R
- Paste Link (N): ALT + H + V + N
- Picture (U): ALT + H + V + U
- Linked Picture (I): ALT + H + V + I
Further Readings
- How to Copy and Paste Exact Formatting in Excel(Quick 6 Methods)
- How to Copy and Paste in Excel Without Changing the Format
Using Paste Special Tool in Excel
We can get to the Paste Special window in one of the following ways.
1. The First method, using the keyboard shortcut from Excel 2007 and later versions: ALT+CTRL+V
2. Next, keyboard shortcut for all versions: ALT+E+S
3. Alternatively, navigate to the Home tab > the Clipboard group > the Paste menu > Paste Special option
4. Finally, go to the Context menu (right-click on the mouse) > the Paste Special drop-down > Paste Special option
Note: However, the Paste Special option will not be visible unless you copy a cell or range of cells first.
Related Content: How to Use Paste Special Command in Excel.
You will find 11 paste options that we have in the context menu or paste menu from the ribbon. Besides, the Paste Special tool also provides more options.
1. Paste Comments and Notes
With this option, you can paste the notes and comments alone from cells, excluding other cell contents.
- To begin with, copy the selection and click on the destination cell.
- Next, get to Paste Special dialog box with ALT+CTRL+V.
- After that, just select the Comments and Notes radio button > Press OK.
Or Keyboard Shortcut-1: ALT+CTRL+V+C > Press OK.
Keyboard Shortcut-2: ALT+E+S+C >Press OK.
Output:
2. Paste Validation
To know how to create a validation in Excel, click here.
To paste a validation, do the following:
- First, select the Validation radio button > Press OK.
Or Keyboard Shortcut-1: ALT+CTRL+V+N > Press OK.
Keyboard shortcut-2: ALT+E+S+N >Press OK.
Implementing Paste Special Shortcuts in MS Excel
- Paste (A): ALT+E+S+A, or CTRL+ALT+V+A
- Formulas (F): ALT+E+S+F, or CTRL+ALT+V+F
- Values (V): ALT+E+S+V, or CTRL+ALT+V+V
- Formats (T): ALT+E+S+T, or CTRL+ALT+V+T
- Formulas & Number Formats (R): ALT+E+S+O+R, or CTRL+ALT+V+R
- Values & Number Formats (U): ALT+E+S+U, or CTRL+ALT+V+U
- Transpose (E): ALT+E+S+E, or CTRL+ALT+V+E
- All Using Source Theme (H): ALT+E+S+H, or CTRL+ALT+V+H
- All Except Borders (X): ALT+E+S+X, or CTRL+ALT+V+X
- Column Widths (W): ALT+E+S+W, or CTRL+ALT+V+W
- Comments and Notes (C): ALT+E+S+C, or CTRL+ALT+V+C
- Validation (N): ALT+E+S+N, or CTRL+ALT+V+N
2 Tricky Options to Paste Values in Excel
All the Paste Options for Values (and other attributes) are already discussed in this article. Now we are going to show two more ways.
Read More: How to Copy the Same Value in Multiple Cells in Excel (4 Methods).
1. Using a Mouse Trick (Right Button Press and Hover)
This technique is exceptional, but a very fast and time-saving one. You just have to do the following.
Step 1: First, select the range of cells.
Step 2: Next, hover your mouse cursor just on the borderline of the selected area.
Step 3: Then, when the sign in the following picture appears, just right-click your mouse and drag it to the destination cell.
Step 4: After that, select Copy Here as Values Only option. And you will get the desired output.
2. Applying Advanced Filter in Excel
I don’t know if anyone would like to paste values using the Advanced Filter. But still, this is another way and a funny one! Just follow the steps below.
- Go to the Data tab > Sort & Filter group > Advanced Filter option.
The Advanced Filter pop-up will appear. - Now, choose Copy to another location.
- Then, select the List range, i.e. B4:E9 here.
- Select the paste location in the Copy to box.
- Finally, press OK.
Paste Options Not Working in Excel
If the Paste Special option is lost or not working properly, most probably you have one of the following issues.
Reason 1: Paste Options Button Not Available
We have already discussed it in the How to Add Paste Options Button in Excel section.
Reason 2: A Third-party Add-in Preventing Paste Special from Working
In this case, first, you should try running MS Excel in Safe Mode. To do that, execute the following steps.
- For this, go to Start and press and hold the CTRL hotkey.
- Then, keep CTRL pressed and press on Excel.
- Now, keep the CTRL key held until the following message box appears.
- Lastly, press YES.
Now, you are running Excel in safe mode, and then the paste options should work fine.
Conclusion
Hope you will find this write-up effective to utilize various Excel paste options. The workbook is there for you to download and practice yourself. If you have any questions, comments, or any kind of feedback, please let me know in the comment box.
