The importance of Paste Special

Here I want to talk about Pasting in Excel.
I noticed at work that some of our colleagues are not aware that we can paste in many different ways in excel.

Paste icon

So what’s wrong with plain old Paste?

We could say that we over complicate things…

But when we use Paste special, we actually saving a ton of time.

 

In Excel picking up a cell or a range with Copy or Ctrl+C is not a simple affair.
You might look at the cell as it contains a number, a value that you want.
But when you hit Ctrl+C you pick up a bunch more attribute that belongs to the cell.

A cell contains your value and it displays your value.
Now, how your value gets there that is another thing.
Sometimes it is a straight up value, sometimes it is a result of a calculation that described in your cell as a formula.

But other than your value the cell also has attributes that gets copied over with a simple Ctrl+C/Ctrl+V (Copy and Paste)

 

These attributes are:

  • Formulas
  • Values
  • Formats
  • Comments
  • Validation parameters
  • Borders
  • Column/Row Widths and Heights
  • Formulas and number formats
Excel Paste Special options

As you can see the Paste Special have many many options for you to choose. 
Which one to use?

Well. How long is a piece of string?

It is all depends on your specific situation. 

Below I will name a few usage that I used in my time.
In reality you should experiment with this.

In what situations we would use Paste Special?

  1. Value something out. -Paste Values
    When you want to remove the formulas and want to leave the results/values only in the cells.
  2. Paste formulas only -Paste Formulas
    We do this when you have some fancy formatting already done in the cells you need to paste in. Instead of wrecking the fancy formatting you can just copy in the formulas
    only.
  3. When you only want to copy the formatting -Formats
    When you only want to copy over the fancy formatting or even conditional formatting.
  4. Only paste the comments out of a cell -Comments
    You can copy only comments from a cell. This is useful when you have to write the same comment on a cell. like a date.
  5. Validation rules! -Validation
    This is a very useful one when you have to set different validation rules on a bunch of cells
  6. To turn the copied cells 90 degrees -Transpose
    This is one of the basic ones of special paste. You use it when you need to turn the Copied cells from one way to another.

Calculator

Calculate with Paste Special!

In the Paste Special window you can find Mathematical operations too.
That means you can do the following simple math within Paste Special:

  • Add
  • Substract
  • Multiple
  • Divide

You can also skip Blanks if you have to and do numerous  cool things.

 

Did you know that we have a facebook page? Come and visit us at https://www.facebook.com/excelangelcom/

[et_pb_ccfcm_facebook_comments_module ccfcm_app_id=”339898187197719″ _builder_version=”4.6.1″ _module_preset=”default”][/et_pb_ccfcm_facebook_comments_module]