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…


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


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:


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


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?

About MF

An Excel nerd who just transition into a role related to data analytics at current company......😊 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: Logo

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