How to Multiply in Excel Using Paste Special Option

Sometimes in Excel, you need to perform some quick calculations. And, you can use paste special for these quick calculations instead of formulas.

In paste special, you have four different options [Add, Subtract, Multiply, and Divide] which you can use.

And today in this post, I'd like to show you how can you use paste special to multiply and (subtract, divided, and minus) as well.

Multiply In Excel Using Paste Special Option

Multiply in Excel using Paste Special Option

Let’s say, you have a data table with call tariff rates from India to other countries. And due to some hike in cost, you need to increase your tariff rates by 20%.

Now, rather than using the formula you can use paste special's multiply option here.

  1. Select the cell in which you have your %age of increment(I have used 1.2 for increasing the tariff by 20%) and copy it.
  2. Now, select the entire range on which you want to apply the calculation.
  3. Use shortcut key [Alt + E + S] to get the paste special dialog box.
  4. Go to Operation, select the “multiply” option, click okay.
Multiply In Excel Using Paste Special Option in Cells

In the above example, we have used an increased rate of multiplication. You can use the same way to add, subtract, and divide as well.

And When You have Cells with Formulas

Now, this is one of the most amazing things you can do with paste special. If you have some cell where you already have formulas, you can still use paste special.

Multiply In Excel Using Paste Special Option with formulas

In above example, the price is the sum of fixed cost (E2) and variable cost (E3). We have increased the price by 32.25%.

Paste Special for Dynamic Calculations

One more useful thing you can do with paste-special is, you can perform a dynamic calculation.

Let's say, you have a data set of five products for different zones and you have to calculate an incremental price for all the five products using different incremental rate.

Just select the all the cells having incremental rates and follow the same steps which we have used above.

Use Paste Special Operations for a Quick Multiply in Excel with dynamic calculations

Shortcuts Keys

  1. Multiply -  Alt + V + H + M
  2. Divide -  Alt + V + H + I
  3. Add -  Alt + V + H + D
  4. Subtract -  Alt + V + H + S

Conclusion

Using paste special to multiply and for other small calculations is a time saver. If those calculations are one time then there is no need to use formulas.

This method is quick, simple, and easy.

Now tell me one thing. Have you ever tried this before?

Share your views with me in the comment box, I'd love to hear from you. And, please don't forget to share this tip with your friends.

Content Protection by DMCA.com
2018-11-16T06:13:33+00:00

8 Comments

  1. SANDEEP BHARGAVA 17 Dec, 17 at 7:12 pm - Reply

    Quite useful as always, but in dynamic calculations if the sequence of products changes or lets say if one product is not available for any state (lets say in North Zone Helmet is not sold by the company) whole calculation gets wrong.
    What can be done in this case

    • Puneet Gogia 19 Dec, 17 at 6:20 am - Reply

      There can be two different scenarios, one is you don’t have the row for North-Helmet: in this case, the entire equation is going to disturb. And the second is you have the row but the value is zero for the price: in this case when you multiply that zero you’ll get zero in the end value.

      Yes, in the first scenario, we have the issue.

  2. Patty Ball 4 Jan, 17 at 7:06 pm - Reply

    silly question/comment, but isn’t 20% .2 and not 1.2? What am I missing? Other than that, thanks for the info

    • Puneet Gogia 5 Jan, 17 at 6:08 am - Reply

      No Patty, it isn’t.

      Let me tell you.

      If you want to increase value by 20% then you have to raise current value to 120%. Like, for 10, 2 is 20% but the final value will be 12 (100%+20%).

      That’s why 1.2 or 120% you can say.

      Good day.

  3. ratanak 22 Feb, 16 at 3:09 am - Reply

    Thanks for sharing such informative tricks.keep sharing

    • Puneet Gogia 22 Feb, 16 at 5:26 am - Reply

      Thanks Ratanak

  4. Ankur Shukla 20 Feb, 16 at 6:32 am - Reply

    Thanks Puneet for sharing the valuable info… 🙂 keep it good work and share it around the world 🙂

    • Puneet Gogia 20 Feb, 16 at 8:07 am - Reply

      Thanks Ankur

Leave A Comment