Humble Copy Amazing Paste

Its Copy Paste Again. To me a Nobel should be awarded to the one to introduced the idea of copy paste. The Greatest gift to the dead on the deadline professional. We have all done it and will do it till our last breath, then Why not familiarize ourselves with a few amazing things that could be done through this humble yet life saving tool.

We will deal with this in three parts :

1. Paste Options ,  2. Operations while Pasting,  3. Pasting differently

The below is the paste special screen. 

PART 1: PASTE OPTIONS

Values: 

This option copies only the value in the cell or range leaving behind the formats/formulas . Actually this is what happens when we take footballers from Club football to Fifa. They loose 

everything they have.

 

Formulas: This copies the formulas in the source cells to the destination.

Suppose we want to copy the % calculation from Tom’s Score card to Harry’s. This option is useful then. It can also be done with a simple copy paste but the formats also get copied that way. You can also drag the handle if the cells are adjacent.

Shortcut Key: After Ctrl + C , Press Alt + E followed by S & F

Formats: We are talking only formats here. The below example will illustrate that.

Now we want to copy the formats to the destination without touching the data. Paste Formats is at the rescue.

Shortcut Key: After Ctrl + C , Press Alt + E followed by S & T

Comments: Copying comments only from one cell to another.

I have copied the comments from Messi to Ronaldo. Both are great players to me.:)

Shortcut Key: After Ctrl + C , Press Alt + E followed by S & C

Validations: we have created a cell validation list as below and want to copy it to another cell . Note we do not want to copy the already selected option.

Shortcut Key: After Ctrl + C , Press Alt + E followed by S & N.

Column Width: Now we have the below two sets of data and we want to have the same column widths in harry as in Tom’s Data. This could be achieved by using the column widths option.

You Might ask what about The Row Heights, Can we not copy that ?? Sorry friends that needs a VBA Solution and I am not an expert at it, but I can still provide you with a solution if needed. Just Ask. 

Shortcut Key: After Ctrl + C , Press Alt + E followed by S & W

There are also other options available that could mix and match the requirements as below where the names are self explanatory.

1. All Using Source Theme

2. All except Borders

3. Formulas and Numbers formats.

4. Values and Number formats.

I will be following up this one with part 2 & 3 very very shortly.

Hope you liked this small piece of information. Please post your queries and I assure a reply within 24 hours.

Signing off.

Nishant