Paste options in Excel

In Excel, copying and pasting are one of the most commonly used actions. When we refer to copy and paste, the first thing that comes to mind is CTRL + C (to copy) and CTRL + V (to paste). But do you know copying and pasting operations are not limited to this operation, and there is much more scope to do?

This tutorial covers all about pasting operations in Excel, including the Excel Paste Special feature and other helpful Paste shortcuts.

What is Paste Special option?

We all copy a cell in Excel and paste it into another cell. What happens after that?

Excel copies not only the text of a cell but also the formatting, including the font style, font type, font colour and other options.

There are certain situations where we only want to copy the contents of the cells excluding the formatting, or we want to duplicate only the formatting of the cells excluding the cell values, or to copy only the embedded formula. What to do in such cases as the simple copy-paste mechanism won't work?

For such cases, Excel has introduced the Paste Special Functionality that provides various pasting options. There are various predefined options available in Paste Special Window. For instance, you can only paste the formulas, or the values or the comments of any particular cell.

How to Access the Excel Paste Special feature?

There are various methods using which you can access the Paste Special option.

Select and copy the cells for which you want to apply the paste special option. Once copies you can use any of the below given method to access the Excel paste special feature:

Method 1: Using the Ribbon

Go to Home tab
From the Excel Clipboard section, go on the Paste section present on the extreme left corner.
Click on the paste dropdown. You will have the following window. Click on the Paste Special option. This will open the Excel Paste Special Dialogue box.

Method 2 : Using Keyboard Shortcut

Use the Excel Paste Special Shortcut - Alt + E + S + V.

Method 3: Using Right-click Menu

Right Click on the cell where you want to paste, and then select paste special.

Paste Options in the Paste Special window

As shown above, you will have the specified window. Excel provides various options to paste selectively. For example, you can choose to only paste formulas, or values, or formats, or comments, etc.

To use these options, all you need to do is to go to the specified and copy the values (press CTRL + C). Select the cells where you want to paste them and open the Excel Paste special window. From the resulting dialogue box, select the desired option. Below given is the table explaining all the options available in the Paste Special window:

S.No:	Format	Explanation
1.	Formatted Text	This format is applied when the user wants to retain the text and the formatting of the text taken from another program or web page.
2.	Unformatted Text	It is used whenever the user wants to take the text without formatting the Office program.
3.	Microsoft Office Drawing Object	You want the contents of the Clipboard to appear as a Microsoft Office drawing object.
4.	Picture (GIF)	This formatting feature is used whenever the user wants to add the Clipboard's data to arise as a Graphics Interchange Format (GIF) picture.
5.	Picture (JPEG)	This option enables the user to retain the Clipboard's data to occur as a JPEG (Joint Photographic Experts Group) picture.
6.	Picture (PNG)	This feature is used whenever you want to make the Clipboard's content appear as a Portable Network Graphics (PNG) picture.
7.	Picture (Windows Metafile)	This this certain option whenever you want the Clipboard's contents to occur as a WMF (Windows Metafile Format) picture. The most commonly used picture format is a 16-bit graphic.
8.	Picture (Enhanced Metafile)	This feature is handy if the user needs the Clipboard's contents to appear in an EMF (Enhanced Metafile) format. Try saving your picture in a 32-bit graphic format, as it supports more advanced graphics functions.
9.	Device Independent Bitmap	As the name suggests, this feature helps the user to convert the Clipboard's content into a Device Independent Bitmap (DIB).
10.	Bitmap	This Paste special option is used when the user wishes to make the Clipboard's contents appear as a bitmap. The BMP format is widely known for its colours since it can display millions of colours. Many different programs support bitmap files.

Paste Special Operation Options

Operations option is an exclusive feature of Paste Special. It enables the user to perform the required operations quickly by directly changing the cell contents without applying any Excel formula.

For instance, let's suppose you are given a dataset carrying numbers in millions, and you are asked to convert the given number into billions. The first approach that will come to you is to create an Excel formula where you will divide the given number by 1000. Though you can perform the same operation using the Paste special options as well.

Below given are the steps:

Select an empty cell and enter the number 1000 (since we want to convert it to billions).
Again, select the cell and copy the content (CTRL + C).
Using your mouse cursor select the given cells that you want to convert from million to billion.
From the home tab, go to paste section, click on the Paste Special option.
Excel will open the paste special dialogue window. Select the Divide option.
As a result, it will convert the selected numbers into billions.

Paste special is not limited to divide operation. You can choose any operations for your Excel data using the above steps. Go for it and give it a try now!

Additional Paste Special Options

Besides the above options, Excel Paste special provides two more features in its dialogue window.

Skip Blanks
Transpose

Let's cover both the options using examples.

Skip Blanks

While copying and pasting data on Excel collectively, many times we paste blank cells as well. And later we waste time by deleting.

With Paste special we can skip those blank cells directly by using the Skip Blanks option. This option skips copying and pasting the blanks.

For example, if you want to split your data into 2 columns and you want to combine it in a single column follow the below steps:

Select and copy the cells. Next, put your cursor on the cell where you want to paste your data.
Go to home -> click on paste-> select paste special.
From the paste special window, select the skip blanks option.
As a result, you will have the following output.

Transpose

We often transpose our data in Excel. What if we transpose the data easily before pasting it to new cells? As the name suggests, this paste special option transposes the data whenever you paste it into your Excel worksheet.

For example, if you want to transpose your data from horizontal orientation to vertical follow the below steps:

Select and copy the cells. Next, put your cursor on the cell where you want to paste your data.
Go to home -> click on paste-> select paste special.
From the paste special window, select the Transpose option.
As a result, you will notice it will automatically transpose the given data.

Excel Paste Special Shortcuts

Once you start using Excel Paste Special feature upgrading the skills will be essential. The better way to speed up the process and save time is by using a few keyboard shortcuts.

Following is the list of some shortcuts key that you can use while working with the paste special option:

Press shortcut key - 'Alt+E+S+V + Enter' to Paste only Values.
Press the shortcut key - 'Alt+E+S+T + Enter' to paste the formatting.
Press the shortcut key - Alt+E+S+C + Enter to paste your Excel comments.
Press the shortcut key - 'Alt+E+S+W + Enter' to set your worksheet column's width exactly the same as the copied cells.
Press the shortcut key - 'Alt+E+S+*+E+ Enter' to copy cells values but in a transposed format.

While it may be difficult to remember all these formulas in a single go, it's a good approach to use them consciously a couple of times, and then it will quickly come to you like the back of your hand.

Next TopicSquare Root in Excel

← prev next →