Today, I’ll show you how to use Paste Special in Microsoft Excel using various proven methods, real illustrations, and easy steps.

You often copy datasets and images from another worksheet, workbook, or Microsoft 365 app into Excel. That’s not all! You might copy data from external apps and databases into Excel. When you copy and paste data or any other supported content into Excel, the tool tries to match the formatting of the pasted content according to the destination worksheet’s formatting style.

However, you shouldn’t just restrict yourself to destination cell formatting. You can copy and paste formats and not any values, copy and paste only values, perform simple mathematical operations, like addition, subtraction, etc., when pasting data, and so on using the Paste Special tool of Microsoft Excel.

In this Excel tutorial, I’ll show you all the different ways to use this feature efficiently and effortlessly. Let’s get started!

Paste Values Only

The Paste Values Only feature in Excel’s Paste Special allows you to paste just the copied values from the copied source. It removes any formulas, formatting, or conditional rules. it ensures that the pasted data remains static and unaffected by changes in referenced cells. You might want to use it to preserve final calculated results, clean up data for sharing, or remove links to external sources.

To use this feature, copy content from a supported source and go to the destination Excel worksheet.

Select a cell where you’d like to paste the values from the copied source only.

Press Ctrl + Alt + V to bring up the Paste Special dialog box.

Select the Values option below the Paste column.

Click OK to apply the Paste Special rule.

Excel will copy only the data or text strings from the source cells or database, and not any source cell or database formatting.

Paste Formulas Only

You often need to reuse the same formula from another worksheet or workbook. If the cell range references remain unchanged and only the values differ, you can use the Paste Formulas Only feature. It helps quickly transfer one or multiple formulas to the destination range. This saves time and reduces the risk of manual errors from retyping formulas.

Firstly, locate the formula in the source dataset you wish to copy and transfer to the destination data table.

Make sure that the cell range references for the formula will be exactly the same as in the destination worksheet.

For instance, I’m exporting the multiplication formulas from D2:D7 in Sheet2 to D2:D7 in Sheet1 of the same workbook.

The cell range references are also similar. Also, there are no fixed cell range references. All are dynamic references so the formula will be adjusted automatically.

Now, select D2:D7 or the cell range you wish to copy in your worksheet and press Ctrl + C to copy the formula and results.

Go to the destination worksheet and select the exact same number of cells where you wish to paste the formula. If you choose 7 cells from the source worksheet, select 7 cells in the destination dataset as well.

Open the Paste Special dialog and select the Formulas option below the Paste column. Click OK to paste the formula and not the values.

Excel will copy, apply, and calculate the formulas in the destination cell range instantly.

Paste Formats Only

So, if you like the formatting of a cell or cell range in a worksheet from the same workbook or a different workbook, what you can do is look at the formatting style multiple times and try to apply that in the destination cell or cell range. Here, accuracy and consistency could be a problem, not to mention the time you’ll be investing in manually formatting cells.

Instead, you can use the Formats Only feature of the Paste Special tool to copy and paste the cell style from the source dataset.

Navigate to the source worksheet from which you’d like to copy.

Select the cell range and press Ctrl + C to copy the content of the cell range.

Now, go to the destination worksheet, select the cell or cell range, and press Ctrl + Alt + V to bring up the Paste Special dialog box.

Select the Formats option below the Paste column.

Excel will only copy and paste the cell styling from the source dataset to the destination cell or cell range.

Paste Comments and Notes

Do you need to copy a threaded comment or a note from one cell to another? You can use the Paste Comments and Notes feature of the Excel Paste Special tool. The working process of this feature is exactly similar to that of the other features you’ve seen so far.

Simply select a cell or a range of cells containing Notes and Comments. It could be both or either one. According to availability, Excel will copy and paste the content. Press Ctrl + C to copy the entire cell content.

Go to the destination worksheet and highlight a cell or cell range where you’d like to paste the copied Notes or Comments.

Press Ctrl + Alt + V to launch the Paste Special dialog box.

Select the Comments and Notes option below the Paste column.

Click OK to apply.

Excel will copy the comment or note from the source to the destination cell or cell range.

Operation Options

The Operation section in the Paste Special dialog box allows you to perform quick mathematical operations using common operators, like Add, Subtract, Multiply, and Divide.

You can simply copy the number by which you wish to multiply or divide values in a cell range and use the Paste Special dialog box to apply that mathematical operator seamlessly.

For instance, you need to find out the tax charged for a few products. The tax rate is given as 10% or 0.10. Now, you can simply copy the tax rate and multiply the product cost to get the tax amounts.

Create a Tax Amount column header in your worksheet and convert the percentage tax rate into decimals.

Now, copy the tax rate value by selecting its cell and pressing the Ctrl + C keys.

Select the Tax Amount column and press Ctrl + Alt + V to bring up the Paste Special dialog box.

Select the Multiply option in the Operation section of the Paste Special tool.

Click OK to apply the multiplication operation.

Excel will calculate the tax amounts for each product by multiplying the unit cost by the given tax rate.

Paste Link

The Paste Link feature in Paste Special allows you to create a dynamic link between the copied data and the destination cell. So any changes in the original data automatically update in the linked cells.

It works by pasting a formula that references the source cell instead of pasting a static value. You can use the Paste Link feature when you need real-time updates between different sheets or workbooks without manually re-entering data.

Let’s say, you wish to show the tax amounts calculated in the previous dataset as a dynamic value in a different worksheet, that might be the dashboard of the workbook. You want these tax amounts update automatically when you change the tax rate or product cost in the primary worksheet. Here, I’ll show you how to use Paste Link.

Go to the source worksheet and select the cells below the Tax Amount column. Press Ctrl + C to copy the values.

Now, go to another worksheet and select the destination cell range.

Press Ctrl + Alt + V to bring up the Paste Special dialog box.

Click on the Paste Link button in the bottom left corner of the Paste Special dialog box.

Excel will create a link to the source data.

Now, you can change the tax amounts in the source worksheet as shown above.

You’ll see that the same values have updated instantly in the other worksheet where you created a link to the Tax Amount column.

Paste Column Widths Only

The Paste Column Widths in Excel is a special paste option that transfers only the column widths from copied cells. You can find it in the Paste Special dialog box. It doesn’t copy the content, formulas, or formatting.

It helps maintain a consistent layout across sheets or tables by ensuring columns have the same width as the copied source. You might want to use it if you must match column widths while pasting data separately to keep a uniform structure.

To try it out, select a cell range from which you wish to transfer the column width.

Press Ctrl + C to copy the cell range.

Now, go to the destination cell range or column and press Ctrl + Alt + V to launch the Paste Special dialog box.

Select the Column widths option in the Paste section.

Click OK to transfer the column width from the source column to the

Excel will resize the column instantly according to the column width of the source cell range or column.

Conclusions

These are some of the ways to use the Paste Special tool in Excel to copy and paste data, formulas, formats, etc., in different ways.

This tool makes your Microsoft Excel data analysis and visualization tasks easier by offering the most popular commands in one dialog box.

You can use the comment box to write feedback about this Excel tutorial.