How to Use Paste Options in Excel

Get FREE Advanced Excel Exercises with Solutions!

Paste Options in MS Excel gives 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.


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 > click 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.
Note: In Excel 2007, mark the Show Paste Options buttons checkbox.

How to Use Excel Paste Options: from 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 are.

Note: All the Paste Options will not appear unless you copy a cell or range of cells.
Excel Paste Options Button Excel Paste Options from the Ribbon Paste Options from the Context Menu

Now, we are going to use the following dataset to demonstrate different paste options and their respective outputs.

Sample Dataset for Paste Options Demo

Next, let’s see all the paste options, their icons, and their outputs in a table.

Options Icon Paste Attributes & Outputs
Paste (P) Paste (P) Icon

» Each and Everything from the source dataset except the column widths.Paste (P) Output: Paste Options in Excel

Formulas (F) Formulas (F) Icon

» Paste the formulas together with the values from the source dataset. You don’t have to enter the formula manually.Paste Formulas (F) Output: Paste Options in Excel

Formulas & Number Formatting (O) Formulas & Number Formatting (O) Icon

» Formulas from copied cells and number format.Formulas & Number Formatting (O) Output: Paste Options in Excel

Keep Source Formatting (K) Keep Source Formatting (K) Icon

» Keep the formatting of the source dataset intact, at the same time pasting other cell contents (except Column width).Keep Source Formatting (K) Output: Paste Options in Excel

No Borders (B) No Borders (B) Icon

» Paste everything but the borders.No Borders (B) Output: Paste Options in Excel

Keep Source Column Widths (W) Keep Source Column Widths (W) Icon

» Everything from the source dataset, i.e. the values, formats, formulas, etc. including the column widths.Keep Source Column Widths (W) Output: Paste Options in Excel

Transpose (T) Transpose (T) Icon

» Transpose the pasted rows to columns and vice versa (formulas, formats, etc. are the same as source).Transpose (T) Output: Paste Options in Excel

Values (V) Values (V) Icon

» Only the values (text, number, etc.). Number format, fill color, etc. won’t be copied.Values (V) Output: Paste Options in Excel

Values & Number Formatting (A) Values & Number Formatting (A) Icon

» Only values (text, number, etc.) and number format (not other formattings, formulas, etc.).Values & Number Formatting (A) Output: Paste Options in Excel

Values & Source Formatting (E) Values & Source Formatting (E) Icon

» Paste the values and source formats (not the formulas).Values & Source Formatting (E) Output: Paste Options in Excel

Formatting (R) Formatting (R) Icon

» Formatting only, i.e. number format, fill color, border, font size, etc.Paste Formatting (R) Output: Paste Options in Excel

Paste Link (N) Paste Link (N) Icon

» Creates a link with the source dataset so that any change in values (not format) there will be reflected in the pasted data.Paste Link (N) Output: Paste Options in Excel

Picture (U) Paste as Picture (U) Icon

» Paste a picture of the original dataset.Paste as Picture (U) Output: Paste Options in Excel

Linked Picture (I) Paste as Linked Picture (I) Icon » A linked image of the source dataset. Any change in the source causes the same change in the image.Paste as Linked Picture (I) Output: Paste Options in Excel

How to Use Excel Paste Options with 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

How to Use 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.

Paste Special Pop-up

You will find 11 paste options that we have in the context menu or paste menu from the ribbon. Besides, the Paste Special command 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 the 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:

Paste Comments and Notes


2. Paste Validation

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

How to Paste Values in Excel: 2 Tricky Options

All the Paste Options for Values (and other attributes) are already discussed in this article. Now we are going to show two more ways.


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.

  • First, select the range of cells.
  • Next, hover your mouse cursor just on the borderline of the selected area.
  • Then, when the sign in the following picture appears, just right-click your mouse and drag it to the destination cell.

  • 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.

  • First, 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.
  • Afterward, select the paste location in the Copy to box.
  • Finally, press OK.


Paste Options Not Working in Excel: 2 Reasons

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.

Run MS Excel in Safe Mode

  • Now, keep the CTRL key held until the following message box appears.

Do you want to start Excel in safe mode?

  • Lastly, press YES.

Now, you are running Excel in safe mode, and then the paste options should work fine.

〉〉 If the paste options are still not working, then installed add-ons are the culprit. You have to uninstall the add-ons one by one and find the add-on responsible for the issue. Keep it uninstalled, or disable it. Now the paste options will work!

Download Practice Workbook

You can download the practice workbook from the following link, and practice along with this.


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.


<< Go Back to Paste | Copy Paste in Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Masum Mahdy
Masum Mahdy

Abu Talha Masum Mahdy (Assistant Project Manager, ExcelDemy) has been working with the ExcelDemy family for over 2 years. He wrote 30+ articles and reviewed more than 1300 others for ExcelDemy. Currently, he is responsible for reviewing articles from 2 writing teams of ExcelDemy. He ensures correctness and fluency in his team write-ups. Mahdy holds a BSc in Naval Architecture and Marine Engineering from BUET. His interests are Project Management, Content Writing, SEO, Digital Marketing, Data Analytics and... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo