Copying and Pasting data is one of the most performed actions in Excel. There is a lot more lớn copy-pasting then simple Control + C and Control + V.

Bạn đang xem: How to use paste special in excel 2010

In this tutorial, you will learn all about the Excel Paste Special feature and useful Excel Paste Special Shortcuts.


What is Excel Paste Special?

What happens when you copy a cell in excel và paste it somewhere else?

It copies not only the nội dung of the cell but also the format such as background color, font color, borders(as shown below).

*

But what if you want to copy only the cell contents and not the formatting, or only the formatting and not the values, or only the formula within it?

You can’t do that with a simple copy paste but you can bởi that using Excel Paste Special functionality in Excel.

Accessing Excel Paste Special Options?

There are a couple of ways you can access the Excel Paste Special features.

The first step is to copy the cells that you want lớn paste. Once you copy it, here are three different ways khổng lồ access paste special in Excel:

Using the Ribbon: Go to home –> Clipboard –> Paste –> Paste Special. This will mở cửa the Excel Paste Special Dialogue box.
*
Using Keyboard Shortcut: Use the Excel Paste Special Shortcut – alt + E + S + V.
*
Using Right-click Menu: Right Click in the cell where you want lớn paste, & then select paste special.
*

All these three options would mở cửa a Paste Special dialogue box, as shown below:

*

Understanding Excel Paste Special Dialogue Box

It could be overwhelming to lớn see so many options for something as simple as copying & pasting in Excel. However, when you go through these, it makes a lot of sense khổng lồ have these options available.

If you have a good grip on these Excel Paste Special options, it can save you a lot of time (and you know time is money!).

So let’s learn more about these options:

Paste Options in the Paste Special Dialogue Box:

*

These options enable you to paste selectively. For example, you can choose to only paste formulas, or values, or formats, or comments, etc.

To use these, simply copy the cells, go the cell where you want khổng lồ paste it, open the paste special dialogue box, and select the desired option.

For example, suppose you have a data set where you have formatted the data using borders và fill colors. If you want to lớn copy only the values, copy the data, go to lớn the destination cell where you want the data lớn be copied, xuất hiện the Excel Paste Special dialog box, and select Values. This will only copy the data và not the formatting.

Operation Options in the Paste Special Dialogue Box:

Operations options give you a quick way to lớn perform the given operations without applying a formula or directly changing the cell contents.

*

For example, let’s say you have some numbers that are in millions, and you want to lớn convert it into billions. One way of doing this would be to create a formula where you divide the number with 1000.

Here is a better way:

In an empty cell, enter 1000. Select this cell & copy it (Control + C). Select the cells that you want to lớn convert from million to lớn billion. Mở cửa the Paste Special Dialogue box và select the Divide option.

That’s it! It will convert the numbers into billions.

In a similar way, you can use other operations as well – Add, Subtract, or Multiply using Paste Special.

Additional Excel Paste Special Options

Apart from the Paste and Operation option, there are two additional options in the Excel Paste Special dialogue box.

*

Skip Blanks: This would skip copying và pasting the blanks. A practical use if this could be when you have data split into 2 columns and you want to lớn combine it in a single column. Something as shown below:
*

Excel Paste Special Shortcuts

If you start using Excel Paste Specialwhen you copy and paste data in Excel, knowing a couple of keyboard shortcuts will save you a lot of time.

Here is the danh sách of the shortcuts


ToPaste Values only – Alt+E+S+V + Enter. ToPaste Formatting only – Alt+E+S+T+ Enter. ToPaste Comments only – Alt+E+S+C+ Enter. lớn set column width the same as that of the copied cells – Alt+E+S+W+ Enter. Tocopy but in a transposed manner – Alt+E+S+*+E+ Enter (striking that extra ‘E’ at the kết thúc checks the transpose option. The * indicates that you can use it with any of the above-mentioned shortcuts. So for example, khổng lồ copy values in a transposed manner you would use alternative text ESV
E+ Enter).

Hope these Excel Paste Special shortcuts are helpful.

Xem thêm: Cách Làm Dầu Gạo Tại Nhà - Chân Thực Cách Làm Dầu Cám Gạo Tại Nhà

While you may not remember these all, it’s a good practice khổng lồ consciously use these for a couple of times & then you will know these lượt thích the back of your hand.