Paste Special in Excel

What Is Paste Special In Excel?

Paste Special in Excel is a feature that enables one to paste the required data of the copied cells. It also offers options to perform arithmetic operations with the copied data.

The Excel Paste Special shortcut is Alt + E + S, followed by the letters to pick the required options. It will control how the pasted content should appear or function in spreadsheets.

For example, the table below contains a column of data, with the column width being 17.73.

Paste Special in Excel - 1

Suppose the requirement is to set the width of column C the same as that of cell A1. Then, we can use the Column widths option in the Paste Special feature in Excel to achieve the desired outcome.

Paste Special in Excel - 2

In the above example, select cell A1 and press Ctrl + C to copy the cell. And then, apply the Paste Special in Excel shortcut for the Column widths option, Alt + E + S + W + Enter.

The above steps will open the Paste Special dialog box, here, choose the Column widths option, and set column C width as 17.73, the cell A1 width.

List of Top 10 Excel Shortcuts

The Paste Special feature in Excel provides various formatting and calculation options. The following are the top ten options, along with their Excel shortcuts.

  1. Paste Special as All.
  2. Paste Special as Formulas.
  3. Paste Special as Values.
  4. Paste Special as Formats.
  5. Paste Special as Comments.
  6. Paste Special as Validation.
  7. Paste Special as Add.
  8. Paste Special as Subtract.
  9. Paste Special as Multiply.
  10. Paste Special as Divide.

Shortcut #1 – Paste Special as All

  • Description

The All option in the Paste Special feature helps paste the copied cell data, including the applied formatting. Thus, it is equivalent to the Paste option, which we typically use to paste data.

The Paste Special in Excel shortcut to access the All option is Alt + E + S + A.

  • Example Scenario

The first table contains two values and the modulo using the MOD().

Paste Special in Excel - Paste Special as All

Suppose the requirement is to determine the modulo of the number and divisor given in the second table and display the result in cell C5, which has no format.

  • Solution
Paste Special as All - Step 1
  • Step 2: Select the target cell C5, press the keyboard shortcut Alt + E + S + A to open the Paste Special window, and choose the All option.
Paste Special as All - Step 2a

Finally, click OK to close the dialog box, and we will see the MOD() output in cell C5 with the updated cell references. The copied cell C2 format gets applied in cell C5 (The cell data is Bold, and the cell has borders on all sides).

Paste Special as All - Step 2b


Excel VBA – All in One Courses Bundle (35+ Hours of Video Tutorials)

If you want to learn Excel and VBA professionally, then ​Excel VBA All in One Courses Bundle​ (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.

Shortcut #2 – Paste Special as Formulas

  • Description

The Formulas option in Paste Special in Excel enables one to paste the formula from the copied cell to the target cell without the formatting. The Excel shortcut to access this option is Alt + E + S + F.

  • Example Scenario

The below tables contain a few celebrity quotes in the dataset.

Paste Special in Excel - Paste Special as Formulas

Cells D2:D3 show the corresponding columns A and B cells’ texts concatenated, but the formulas in the two cells are slightly different.

The requirement is to use cell D2’s formula to display the concatenated text in cell D6.

  • Solution

The Formulas option from the feature Paste Special in Excel can help us to copy the formula from the source cell and paste it into the target cell.

  • Step 1: Select cell D2, and press the keys Ctrl + C to copy the cell formulas.
As Formulas - Step 1
  • Step 2: Select the target cell D6, press the keyboard shortcut Alt + E + S + F to open the Paste Special window, and choose the Formulas option.
As Formulas - Step 2

Finally, click OK to view the required formula applied in cell D6.

As Formulas - Step 2b

The cell references in the copied formula get updated in the target cell. But cell D2 format is not applied in the target cell D6.

Shortcut #3 – Paste Special as Values

  • Description

The Values option helps make the best use of Paste Special in Excel. Using it, we can paste only the value from the copied cell, without the formula and formatting, into the target cell.

The keyboard shortcut to access the Values option is Alt + E + S + V.

  • Example Scenario

The first table contains the PMT excel formula inputs.

Paste Special in Excel - Paste Special as Values

We use the given data to calculate the annual payments we must make towards the loan amount of $50,000, using the PMT() in cell C8 as $12,194.53. The value font is red and within brackets (negative value), as it is an outgoing amount.

The requirement is to display the PMT() output in the target cell C10 without the formula.

  • Solution
  • Step 1: Select cell C8, and press the keys Ctrl + C to copy the cell content.
As Values - Step 1
  • Step 2: Select the target cell C10, press the keyboard shortcut Alt + E + S + V to open the Paste Special window, and choose the Values option.
As Values - Step 2a

Click OK, to close the window. We will see only the required annual payment value pasted in the target cell without the formula and cell or data formatting.

 As Values - Step 2b

Shortcut #4 – Paste Special as Formats

  • Description

Another practical use of Paste Special in Excel is the Formats option. It allows users to paste the copied cell’s format without the cell value or formula in the paste area.

The keyboard shortcut to use the Formats option from the Paste Special window is Alt + E + S + T.

  • Example Scenario

The first table contains Inventory A details.

Paste Special in Excel - Paste Special as Formats

The requirement is to create a table for Inventory B with the same format as the first table. Assume the target columns are G:I.

  • Solution
  • Step 1: Select the cell range A2:C11, and press Ctrl + C to copy the cells.
 As Formats- Step 1
  • Step 2: Select the first cell in the target cell range, G2, press the keyboard shortcut Alt + E + S + T to open the Paste Special window, and choose the Formats option.
As Formats- Step 2a

Click OK, to close the Paste Special window. We will see the source cells’ format copied in the target cell range G2:I11.

As Formats- Step 2b

The column headers (cells A2:C2 and G2:I2) in the two tables have the same format. And as required, the cell range I2:I11 has the Date format. The reason is that the corresponding source cells C3:C11 have the Date format.

So, the Formats option helps paste the cell and its content format in the target cell.

Shortcut #5 – Paste Special as Comments

  • Description

The Comments option in Paste Special in Excel allows pasting only the comments from the copied cells to the target cells. It does not paste the cells’ data, formats, or formulas.

The keyboard shortcut to apply the Comments option is Alt + E + S + C.

  • Example Scenario

Suppose there are two tables containing Teams A and B employee lists. And the cells in the first table show comments.

Paste Special in Excel - Paste Special as Comments

The requirement is to copy and paste the first table comments into the corresponding cells for the Team B employees in the second table.

  • Solution

First, we must select each source cell and use the option in the Review tab (highlighted in the image below) to hide the comment. It will ensure that the comment will be visible only when placing the cursor on the concerned cell.

Paste Special in Excel - As Comments - 1
  • Step 1: Select the cell range A2:A11, and press the keys Ctrl + C to copy the cells.
As Comments - Step 1
  • Step 2: Select the target cell range C2:C11, press the keyboard shortcut Alt + E + S + C to open the Paste Special window, and choose the Comments option.
As Comments - Step 2a

Click OK, to close the window. We can view the target cells with the comments copied from the first table.

As Comments - Step 2b

We can use the highlighted option in the Review tab to show all comments for verification.

As Comments - Step 2c

The above result shows that the Team B Employees list data remains the same but gets updated with only the copied comments from the first table.

Shortcut #6 – Paste Special as Validation

  • Description

The Validation option in Paste Special in Excel helps paste the excel Data Validation configurations from copied cells to the target cells.

The keyboard shortcut to use the Validation option from the Paste Special window is Alt + E + S + N.

  • Example Scenario

The table below contains the customer feedback criteria in column A and rating options in column B cells.

Paste Special in Excel - Paste Special as Validations

Suppose cells B2 and B3 have the validation setting (Using the Data Validation option in the Data tab) to enable a customer to choose the ratings.

As Validations - 1

The requirement is to copy the two validation settings and paste them into cells B4:B5 in the same order.

  • Solution
  • Step 1: Select cells B2:B3, and press the keys Ctrl + C to copy the cells.
As Validations - Step 1
  • Step 2: Select the first cell in the target cell range, B4, press the shortcut keys Alt + E + S + N to open the Paste Special window, and pick the Validation option.
As Validations - Step 2a

Click OK, to close the dialog box. The copied validation configurations from cells B2 and B3 will get pasted in the target cells B4 and B5, respectively.

As Validations - Step 2b
As Validations - Step 2c

Shortcut #7 – Paste Special as Add

  • Description

The Add option in Paste Special in Excel adds the copied cell value to the target cell data. The keyboard shortcut to access the Add option is Alt + E + S + D.

  • Example Scenario

The below table contains the students’ scores in Mathematics.

Paste Special in Excel - Paste Special as Add

The requirement is to add the project credits, 20, to the mathematics scores in column B.

  • Solution
  • Step 1: Select cell E1, and press the keys Ctrl + C to copy the cell content.
As Add - Step 1
  • Step 2: Select the target cells B2:B11, press the keyboard shortcut Alt + E + S + D to open the Paste Special window, and pick the Add option.
As Add - Step 2a

Click OK, to close the dialog box. We can view the updated scores of each student in column B, which is the sum of the specified Mathematic score out of 80 and project marks.

As Add - Step 2b
  • Step 3: Update the column B title to suit the revised total Mathematics scores.
As Add - Step 3

Shortcut #8 – Paste Special as Subtract

  • Description

The Subtract option in Paste Special in Excel subtracts the copied cell value from the target cell data. The keyboard shortcut to access the Subtract option is Alt + E + S + S.

  • Example Scenario

The following table shows a list of stocks and their prices.

Paste Special in Excel - Paste Special as Subtract

The requirement is to deduct the amount by which the stock prices dropped, $50, from the stock prices in column B.

  • Solution
  • Step 1: Select cell E1, and press Ctrl + C to copy the cell value.
As Subtract - Step 1
  • Step 2: Select the target cells B2:B11, press the keyboard shortcut Alt + E + S + S to open the Paste Special window, and pick the Subtract option.
As Subtract - Step 2a

Click OK, to close the window. We will see the cell E1 value of $50 subtracted from each stock price in column B.

As Subtract - Step 2b
  • Step 3: Update the column B title to suit the revised stock prices.
As Subtract - Step 3

Shortcut #9 – Paste Special as Multiply

  • Description

The Multiply option in Paste Special in Excel multiplies the target cell values by the copied cell data. The keyboard shortcut for accessing the Multiply option is Alt + E + S + M.

  • Example Scenario

The below table shows the results of four experiment trials.

Paste Special in Excel - Paste Special as Multiply

The requirement is to multiply the trial results in column B with the specified multiplication factors in the same order and show the updated values in column B.

  • Solution
  • Step 1: Select cells E1:E2, and press the keys Ctrl + C to copy the cell values.
As Multiply - Step 1
  • Step 2: Select the target cells B2:B5, press the keyboard shortcut keys Alt + E + S + M to open the Paste Special window, and choose the Multiply option.
As Multiply - Step 2a

Click OK, to close the Paste Special window and to view the updated results after multiplying the trial results by the respective multiplication factors in the specified order.

As Multiply - Step 2b
  • Step 3: Update the column title to suit the updated trial results.
As Multiply - Step 3

Shortcut #10 – Paste Special as Divide

  • Description

The Divide option in Paste Special in Excel divides the target cell values by the copied cell data. The keyboard shortcut for accessing the Divide option is Alt + E + S + I.

  • Example Scenario

The below table contains a list of products and their prices.

Paste Special in Excel - Paste Special as Divide

Assume the specified prices are incorrect, and we must rectify the price values by dividing them by the correction factor.

  • Solution
  • Step 1: Select cell E1, and press Ctrl + C to copy the cell content.
As Divide - Step 1
  • Step 2: Select the target cells B2:B6, press the keyboard shortcut keys Alt + E + S + I to open the Paste Special window, and choose the Divide option.
As Divide - Step 2a

Click OK, to close the window. We can view the corrected prices in column B.

As Divide - Step 2b

Each price value gets divided by the correction factor, $10.

  • Step 3: Update the column B title to suit the corrected price values.
As Divide - Step 3

The above four examples show that the target cells contain the values and not the respective formulas we applied using the Paste Special options.

It is not a case of Paste Special not working in Excel. Instead, the Paste Special feature helps perform the arithmetic operations on a cell range without using additional columns. Also, the result in the paste area has the same number format as the copied cell data.

On the other hand, we may face the scenario of the Paste Special not working in Excel if we have the Paste Special feature disabled. Otherwise, we have third-party add-ins in Excel, which conflict with the Paste Special options.

Download Template

This article must help understand the Paste Special In Excel, with its formula and examples. You can download the template here to use it instantly.

This has been a guide to Paste Special in Excel. Here we learn about top 10 excel shortcuts to paste data, along with examples & downloadable excel template. You can learn more from the following articles –

Reader Interactions

Leave a Reply

Your email address will not be published. Required fields are marked *