Are you really looking for 13 mind blowing pasting tricks in Excel?
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.
Cell
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
6. Comments
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.
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.
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.
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.
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.
Result -
And the you'll get the result like in the image pasted below.
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
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.
Get the result like below image.
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.
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.
Select the formats option of Paste Special and then press OK.
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.
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.
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.
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.
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.
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.
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:
ExcelHour
9599408486 / 8929231250
Website: https://www.excelhour.in/