Are you really looking for 13 mind blowing pasting tricks in Excel?

Tips-&-Tricks Dec 02, 2019

Are you looking for the various pasting tricks in Excel?

If yes then this is the right blog to enhance your knowledge in Excel cut, copy, and paste jobs.

Today, handling data has become the primary responsibility for every company. It is the most important and necessary job and even data is also known as blood for the company that’s why data entry has become the prime job. Usually majority in market is busy in data entry jobs and the company owners and management takes decision basis the data and reports are maintained.

Now a days, major data base jobs are handled on Ms-Excel (The most Microsoft application that is used to store data and to make reports).

Let’s come to the point for that this blog is written.

Various pasting tricks in Excel –

Before starting to explain about pasting tricks of Excel. I would like to give you some basic GYAN that is important for data pasting.


You know, all the data in Excel is entered or stored in cells. When we talk about cut or copy of data in Excel that means we talk about cell. You should know that we don’t copy data instead we copy cells/range. You can paste every single feature of cell. We have many features with cell like Formula, value, format, comment, notes, validation etc. that we can paste separately to the destination or to the cell where you want to paste it.

Let’s start knowing the various pasting tricks those are important for everyone who need to work with data.

With this blog we’ll learn how to paste below qualities of cell.

Various pasting options

1.       All

2.       Value

3.       Formula

4.       Format

5.       Notes


7.       Validation

8.       Column width

9.       Calculation

10.   Transposition of data orientation

11.   Pasting pictures.

12.   Pasting links.

13.   Pasting linked pictures

Most of above features are available with the single user form that is Paste special and the picture of paste special is pasted below.

Image 1 - Paste Special

1.       All –

All means coping all the features of selected cells/range like value, formula, format, comments and notes and validation whatever a copied cell contains and paste it to the destination. You know this feature of paste special is like direct pasting by using shortcut keys CTRL + V or Enter. Please have a look at below example for the better understanding.

For example – There is a table in B6:G13 range that contains Students data with border and background color of field and white color of font.

Image 2 - Table 1

Now the idea is to paste it as it is at K6 that you can do by 4 ways. 1st you must copy the table range B6:G13 then use one of below pasting trick.

a.       By using CTRL + V.

b.       By using ENTER.

c.       By using CTRL + ALT + V to get paste special window and check the ALL option then press Ok.

d.       By using CTRL + ALT + V to get paste special window and check the ALL Using Source Theme option then press Ok.

Image 3 - Table Pasting All

1.       Formulas

Formulas means coping cells/range and pasting the real existing item of cells/range that can be anything like a value or Formula that really exists in cells/range.

For example – Lets take a table in B2:G9 range that contains students data with Total column that contains the total obtained marks. To calculate total marks of all subjects you can use SUM function like the example in below image.

Image 4 - Table with Formula

And if you want to apply the same formula in below cells to get total for all the students you should copy the cell G3 and use formulas option of Paste Special window with the selected cells. you will get this window by pressing CTRL + ALT + V once copy the cell.

Image 5 - Table with Formulas

Result -

And the you'll get the result like in the image pasted below.

Image 6 - Table with Formulas Result

2.       Values

Values means coping a cells/range and pasting the visible value that is in cells/range. It doesn’t matter whether it is a result of a formula.

Example - There are 2 tables in below image

Image 10 - Tables

In the above image, Table 1 doesn't contain cells background color while the table 2 doesn't contain data. Now If you want to paste Table 1 data in Table 2 without impacting the format of Table 2, you should copy Table 1 data and select the first cell of Table 2 then press CTRL + ALT + V to get the Paste Special window opened like in below image, select values and then press OK.

Image 11 - Copy Table 1

Get the result like below image.

Image 12 - Value Pasting

3.       Formats

Formats means coping a cell and pasting the format of source cells to the destination cells/range. It is used when we need to apply the format of source cells/range to other cells/range (destination cells/range).

For example – Lets take a table in B2:K9 range that contains data like in below image.

Image 7 - Table with value

And if you want to paste the format of records in 3rd and 4th row, you should copy the cells those format you want apply at other cells, select the destination cells/range and then use CTRL + ALT + V to get the Paste Special window opened.

Image 8 - Table with Paste Special window

Select the formats option of Paste Special and then press OK.

Image 9 - Table with same format.

4.       Comments and Notes

Comments and notes mean coping a source cells/range and pasting the comments or notes to the destination cells/range.

5.       Validation

Validation means coping cells/range and pasting the validation to the destination cells/range of the source cells/range.

Image - Drop-down

In the above table, we have validation in C3 cell that we copy and paste the validation in below cells/range by clicking the validation in Paste Special option.

6.       Operation

Operation means applying the calculations like Add, Subtract, Multiply, Divide for the value of copied source cells/range with the destination cells/range.

Image - Table with Hindi marks

Here we need to add 5 marks in Hindi marks of all the students. To add 5 marks, you can copy cell B1 that contains 5 as value and select the range B4:B7 and then select value and add options of Paste Special window.

Image - Table with Paste Special option.

Result :

Image - Table with Result

7.       Skip blanks

Skip blanks is means applying the format of filled cells/range to the destination cells/range. This is applied to identify the blank cells in formatted range.

Image - Table with debit amount

In the above table, we have 2 columns that capture debit amounts and the idea is to copy the amount of column B and paste into column C wherever cell is blank like below table. What exactly you have to do is to copy the cells in range B3:B11 and paste into column C3:C11 with the Skip blanks option of Paste Special window and get the result like below.

Image - Table with Skip blanks result

8.       Transpose

Transpose is applied to transform the orientation of range from horizontal to vertical and vice versa. You can also us various other features like value or format etc.

Image - Transpose data

9.       Paste Link

Paste Link means coping the cells/range at source location and pasting the link of source cells/range to the destination location.

10.   Paste picture

Paste Picture means coping the cells/range of source location using the paste picture feature that creates the picture of source cells/range.

11.   Paste linked picture

Paste linked Picture means coping the cells/range of source location using the paste linked picture feature that creates the linked picture of source cells/range.

Hope, it would have added in your knowledge of paste special techniques.

Thank You!

Best Excel training institute in Noida provides Basic Excel, Advance Excel and VBA offline and online classes.

If you want to learn Excel & VBA courses to be Excel expert, connect us on:

9599408486 / 8929231250

Manoj kumar

Corporate Trainer at ExcelHour. I have devoted over 9 years to various industries for learning multiple data challenges and trained Excel and VBA courses to the corporate along with offline training.

Great! You've successfully subscribed.
Great! Next, complete checkout for full access.
Welcome back! You've successfully signed in.
Success! Your account is fully activated, you now have access to all content.