Paste Special function in Microsoft Excel

Hi All,

Do you know apart from only copy and pasting the whole content with formatting in Excel, we can paste a certain attribute such as Value only, Column Width, Formula only, formatting or comment in Excel.

Paste specials give you an opportunity to choose what we want to paste in cells. When standard copy/paste do not give appropriate results, Excel’s Paste special give a wide range of options in which we choose specials elements and mathematical elements.

For Example, if we do any calculation in to the cells and wanted to paste only it’s value in the same cell or somewhere in another sheet or we want to paste only the column width or paste only comments or wants to transpose the data or we can create a link between cells or between the sheets etc.

The Following screenshot demonstrates all the option available in Paste Specials.

Note: “Paste special” option available only once data is copied.

How to Use “Paste Special”-

1-      Copy the source cells or range. (use shortcut Key Ctrl+C)

2-      Select destination cell

3-      Home->Paste Drop Down-> Paste Specials(use shortcut Ctrl+Alt+V)

4-      Choose the option, Click Ok or Press Enter.

Yes! It is as simple as copy and paste😊

Shortcut keys for Paste Specials-

1-      Paste special shortcut key for Ms-Office 2007-2016 – Ctrl+Alt+V

2-      Paste special shortcut key for all Ms-Office Version- Alt+E then S

When you press the shortcut key (Ctrl+Alt+V ) you will get the following window-

We can take any option either click with Mouse or just press the respective alphabet key on the keyboard, which is defined by Underlined “_” , for example if we want to use column widths press Ctrl+Alt+V then W

Lets explore few most usable features listed under “Paste Special”:

1. Paste only “Values” in Excel-

Suppose we have an inventory sheet containing multiple functions/formulas. We need to share this sheet with our clients, however we only need to share value not formula. When we copy and paste the cells, Excel gives us a weird data.

So how we can paste only values from a database?

1-      Select the cell or range including formula then copy it (Ctrl+C)

2-      Click on Destination cell-> Home Tab-> Paste Special-> Values

Or Press Ctrl+Alt+V then V

4-      Ok

2. How to copy and paste “Comments” only :

In column “D”, cells indicate the presence of comments by showing red color triangle on the top right corner of the cell.

Steps to copy “Comments”-

1-      Select the source cells or range then Copy (Ctrl+C)

2-      Destination cells-> Ctrl+Alt+V then C

3-      Ok.

3. How to Paste “Column Width”:

Here, we need to copy column “A” range and paste only column width column “E” (refer: Screenshot)

Steps to paste “Column widths”:

1-      Copy column A

2-      Select destination cell ->Press Ctrl+Alt+V

3-      Either click on Option “Column Widths” or Press “W”

4-      OK

4. How to Paste “Chart Formatting”:

Paste special not only help us to paste values but also it can paste the formatting of charts. This can help us to maintain the consistency throughout all the charts.

Steps to paste “Chart Formatting” –

1-      Copy the source chart (Ctrl+C)

2-      Click on Destination chart

3-      Home Tab-> Paste special

4-      Select the option Formats

5-      Ok

5. How to convert Text into Numbers?

When we download or import a workbook from other sources, data doesn’t come in a desired format. Most of the time we face a problem that numbers are behaving like text and they are not converting into the number after applying the number formats. “Paste special” can be used here-

1-      Type Numerical 1 anywhere in you sheet-> Copy It

2-      Select the data range, which is coming into the text format.

3-      Ctrl+Alt+V

4-      Click on “Multiply”

5-      Ok

6. Data Transpose-

Sometime we need to convert row data into column and vice versa.

1-      Copy the source data

2-      Click on Destination cells

3-      Ctrl+Alt+V then E

4-      Ok

7. Add values to the Existing database–

Probably this operation we do on daily basis to add some value to the existing values. For example, this week total net sales would add to our existing  Net sales. Apart from using the function, we can do this with only copy and paste.

1-      Copy the Source data i.e. Column “C” (Refer screenshot below)

2-      Click on destination cells i.e column “A”

3-      Ctrl+Alt+V

4-      In the operation section click on Add

Note: There are four option –

1-      Add- To add the numbers

2-      Subtract- To subtract the numbers

3-      Multiply- To Multiply with numbers

4-      Divide- To divide the numbers

Problem: Paste special not working?

If the paste special option is not working properly, It’s likely to be because of one reason-

How will you recognize that your paste special option is not working- when you copy your data then do the right click, if paste special option is greyed out then might be its disabled in the background.

To cure this problem, follow the steps –

Fileà Optionà Advanceà check “Show paste options button when content is pasted”

This is how you can use paste specials options in your Excel sheet.

If you have any query suggestions, write down in the comment box.

Happy Learning 😊

351 total views, 3 views today