Paste Options in Excel:

By default, when you copy and paste the data in Excel, everything in the source cell will be pasted into the destination cell when you use CTRL + V. It’s not the only way. Excel has so many paste options depending on our needs.

The paste options feature lets you choose how to paste the copied content into the destination cell. When you copy the data, Excel saves the data to the clipboard. When you paste the data, you can access the paste options menu to control how the data is pasted using the available paste options.

How To Open Paste Options Menu:

1. Copy the desired data from the cells using the keyboard shortcut CTRL +C.
2. Navigate to the destination cell where you want the data.
3. Now paste the data using any of the below options.
   • Press CTRL + V
   • Right-click on the destination cell and select Paste from the context menu.
4. After pasting the data on the destination cell, a paste options menu will appear near the pasted data with various paste options.
5. Select the required paste option from the menu.

Available Paste Options In Excel:

Paste:

This option pastes the copied cells into the destination cell without any modifications.

Here in the below example, cell E10 contains the SUM function which calculates the sum of the range B5:B9. Let’s copy and paste the E10 data to H10 using the paste option.

Here is the result.

Formulas:

This option pastes the formulas from the copied cells into the destination cell with adjusted correct cell references.

This will paste only the formula into the destination cell and adjusts the cell reference.

Formulas & Number Formatting;

This option pastes the formulas and number formatting from the copied cells into the destination cell with adjusted correct cell references.

Here I’m adding number formatting to the E10 cell and copied and pasts the same in H10 using the Formulas & Number Formatting option.

Here is the result. It copied only the formula and number formatting to cell H10.

Keep Source Formatting:

This option preserves the original formatting of the copied cells into the destination cell.

No Borders:

This option removes the borders and pastes only data into the destination cell.

When I copy cell E10 and paste the data into the destination cell H10 and select the No Borders option, this will remove the borders and pastes only cell content.

Here is the result.

Keep Source Column Widths:

This option pastes everything from the copied cells into the destination cell, including the source cells’ column width.

Here I’m increasing the column width of E. Once I copy and paste the E10 data into cell H10 and select the Keep Source Column Widths option from the paste options, it will paste everything including the source cell column width.

Transpose:

This option allows you to reorient the data. This means the data copied in the rows is pasted into columns and vice versa.

Here I’m copying the E5:E10 range cells data and pasting using the transpose option. once I click on the transpose the data from the rows will be pasted into columns. Here is the result.

Values:

This option pastes only formula results without formatting from the copied cells into the destination cells.

Values & Number Formatting:

This option pastes the formula results with number formatting from the copied cells into the destination cells.

Values & Source Formatting:

This option pastes the values with number formatting from the copied cells into the destination cells.

Formatting:

This option pastes only formatting from the copied cells into the destination cells.

Paste Link:

This option pastes the copied cell reference into the destination cell. If you change the source cell data those changes will reflect into the destination cell.

Picture:

This option pastes the picture.

Linked Picture:

This option pastes the copied image with a link to the source cell.  If you make any changes to the source cell image those changes will reflect into the destination cell image.

Paste Special:

If you want further options, you can use paste special.

How To Open Paste Special Menu:

1. Copy the data.
2. Navigate to the destination cell where you want to paste.
3. Right-click on the selected cell and choose the Paste Special option from the context menu.
4. Paste Special dialog box appears with various options.
5. Choose the required paste option from the menu.

Here are the available options. Here you can find some of the options described above.

All:

This option pastes everything into the destination cell.

Comments and Notes:

Pastes only Comments and Notes that are attached to the copied cells into the destination cells.

Validation:

Pastes only the data validation rules of the copied cells to the pasted cells.

Here I’ve added data validation rules to cell C5 to allow only text length between 2 to 5. Then I pasted the data to E5 using the paste special option and selected validation. Now C5 data validation rules will apply to E5. it will only allow text length between 2 to 5.

Here is the result.

All Using Source Theme:

Pastes all copied content and theme formatting.

All Except Borders:

This option pastes everything including content and formatting except borders.

All Merging Conditional Formats:

Pastes the content and conditional formatting of copied cells into the destination cells.

Operations:

Excel also allows you to do some math calculations on the copied data before pasting using the below operations.

In order to do the mathematical calculations using paste options, first we need to have some data in destination cells. Now copy the data from the source cells and paste the data into destination cells using the paste Special option and select the required math operation from below.

Here I’ve some data in the cells E5:E9 & G5:G9. Now I’ll add, subtract, multiply and divide the source data (E5:E9) from the destination cells(G5:G9) using the paste special option.

None:

No mathematical operation will be applied to the copied data.

Add:

This option adds the copied data to the data in the destination cells.

Subtract:

This option subtracts the copied data from the data in the destination cells.

Multiply:

This option multiplies the copied data from the data in the destination cells.

Divide:

This option divides the copied data from the data in the destination cells.

Skip Blanks:

This option does not replace the values in destination cells when blank cells exist in the copied area.

Here a blank cell existed in the copied range and I’ll paste the copied cells using the skip blanks option in paste special. Let’s see the result.

Next time when you’re working in Excel don’t forget to use these Paste options to save time and effort.