Excel Home Tab: Clipboard Options and Paste Special options explained in-detail

In previous post we have seen names of different tabs in Excel. Now we will explore each tab and corresponding subcategories.

Start with Home tab> Clipboard Options

In Clipboard we have below options

1. Copy: Short Cut CTRL+C
   • Copy as Picture
2. Paste: Short Cut CTRL+V for all options. Others options we will see later
3. Cut: Short Cut CTRL+X
4. Format Painter: Short Cut Alt+H+FP

Copy

• Keyboard Shortcut in excel to Copy is CTRL+C.
• To Copy, any cells data to other cells in Excel Copy option is used. In Copy option, we will duplicate the original data.
• Click Esc key to remove the copy mode.
• In Copy, we have other option Copy as a picture. This option will allow pasting the copied data in Image format.

Paste

• Basically Paste is of 2 types normal Paste and Paste Special( Paste special works only in Copy option ).
• A normal paste is just pasted as original data.
• Whereas paste special we have multiple options to Paste Data refer to image.
• Paste special we have different options categorized.
• Paste and Operation
• In addition to the above, we also have Skip blanks and Transpose

Below Dialogue box which appears while using Paste special option in Excel

Now, In Paste Special let us discuss each option in detail

Paste special options

1. All= Shortcut  Alt+E+S+A

• Description:- This option will paste data same like original data no changes. Please see below example.

2. Formulas= Shortcut  Alt+E+S+F

• Description:- This option will paste only formulas from original data to destination cells. Please see below example.

3. Values= Shortcut  Alt+E+S+V

• Description:- This option will paste only values from original data to destination cells. Please see below example.

4. Formats= Shortcut  Alt+E+S+T

• Description:- This option will paste only formats from original data to destination cells. Please see below example.

5. Comments= Shortcut  Alt+E+S+C

• Description:- This option will paste only comments from original data to destination cells. Please see below example.

6. Validation= Shortcut  Alt+E+S+N

• Description:- This option will paste only validation from original data to destination cells. Please see below example.

7. All using Source theme=Shortcut Alt+E+S+H

• Description:- This option same like paste all option no changes to original data. Please see below example.

8. All Except borders= Shortcut Alt+E+S+X

• Description:- This option will paste all except borders if original content has borders in destination cells you will not see borders. Please see below example.

9. Column widths= Shortcut Alt+E+S+W

• Description:- This option will paste only column widths of original content to the destination cells. Please see below example.

10. Formulas and number Formats= Shortcut Alt+E+S+R

• Description:- This option will paste only formulas and number formats from original content. Please see below example.

11. Values and number formats= Shortcut Alt+E+S+U

• Description:- This option will paste only values and number formats from original content. Please see below example.

12. All merging conditional formats = Shortcut Alt+E+S+G

• Description:- This option will paste only merge and conditional formats from original content. Please see below example.

Operation

1.None = Shortcut Alt+E+S+O

• Description:- This option will stop using operational options like Add, Subtract, Multiply, Divide. Please see below example.

2. Add = Shortcut Alt+E+S+D

• Description:- This option will allow you to add copied cell value to the destination cell value and show the result in destination cell. Please see below example.

3. Subtract = Shortcut Alt+E+S+S

• Description:- This option will allow you to subtract copied cell value from the destination cell value and show the result in destination cell. Please see below example.

4. Multiply = Shortcut Alt+E+S+M

• Description:- This option will allow you to multiply copied cell value to destination cell value and show the result in destination cell. Please see below example.

5. Divide = Shortcut Alt+E+S+I

• Description:- This option will allow you to divide destination cell value with copied cell value and show the result in destination cell. Please see below example.

Skip Blanks

• Shortcut: Alt+E+S+B
• In this option, if your copied data as blank cells, you pasted it which has data in place of blank cells, then blanks cells will not override the data. Please see below example.

Transpose

• Shortcut: Alt+E+S+E
• This option is used to copy the data in row-wise and paste in column wise and same way you can copy data in column wise and paste it in row-wise. Please see below example.

Combine 2 special options

We can also use multiple paste special options at the same time. Like below mentioned 2 examples. You may try others options. Refer to the workbook for example.

• Formulas + Add (Shortcut Alt+E+S+F+D)
• Formulas + Subtract (Shortcut Alt+E+S+F+S)

Cut

• Shortcut: Ctrl+X
• The cut option will move data from one place another place.
• Cannot use paste special in CUT option only Normal Paste will work. Paste special works only in Copy option.

Format Painter

• This option is the same as Paste special formats. Instead of using paste special you can also use format painter it is quickly accessible.

• Click here to download the Excel workbook of all Paste Special examples.
• Click here to complete the Excel Paste Special shortcuts quiz. Takes 5 minutes only.