Paste special with simple mathematic operation + – * /

Answer to the 5 little Tips (tests) – Part 3/5

The task: Doing simple calculation (add, subtract, multiply, divide) without formula

Answer:  Copy and Paste Special…

excel-tips-copy-and-paste-with-operation

First of all, we need to understand what we want to perform: Convert currency (in HKD) into USD, by multiply the Exchange Rate (7.7) to the values in A5:A12 in the GIF above). And remember, no formula!

Step by step instruction shown in the GIF above:

  1. Copy the value (A2 in our example)
  2. Select the destination range (A5:A12) where you want the value (A2) to multiply to
  3. Right-click, Paste Special
  4. Select “Values” in the upper portion under Paste; “Multiply” in the middle portion under Operation. 
  5. OK

excel-tips-copy-and-paste-with-operation-2

As simple as this! 🙂

Try other operations (Add, Subtract, Divide) to see the results.

Limitation – The result is static.

It won’t change with the “Exchange Rate”.  Therefore it is only good for one-off task.

Wait… Can we do it with dynamic result?

Of course 🙂  Watch this:

excel-tips-copy-and-paste-with-operation1

Here’s the step by step instruction:

  1. In any empty cell, make a direct reference to the cell where the value resides by inputting =$A$2 (Tip: it has to be Absolute reference)
  2. Copy the cell with the formula (B2 in our example)
  3. Select the destination range (A5:A12) where you want the formula to multiply to
  4. Right-click, Paste Special
  5. Select “Formulas” in the upper portion under Paste; “Multiply” in the middle portion under Operation. 
  6. OK

excel-tips-copy-and-paste-with-operation-3

Now the helper cell (B2) can be clear.  Check the formula in the destination.  They become a formula:  =Original value * ($A$2)

Let’s try other operations yourself.  Also try and see what if you do not use absolute reference in the helper cell?

Advertisements
This entry was posted in Excel Tips and tagged . Bookmark the permalink.

Comments, suggestions, corrections are welcome.

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s