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)

Watch it in action in my YouTube channel:

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

Advertisements

About MF

An Excel nerd who doesn’t work on Excel most of the time at current job...... 🤔 Recently in love with Power Query and Power BI.😍 Keep learning new Excel and Power BI stuffs and be amazed by all the new discoveries.
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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.