How to Quickly Do Calculations Using Paste Special on MS Excel

Sandeep Agarwal

MS-Excel-LogoMS Excel isn’t just about maintaining data in an organized row and column fashion. It’s real power lies in data manipulation.

Let me share an example. I stay in a rented apartment with 4 other friends. We maintain certain monthly expenses on an Excel sheet so that we can divide an equal share at the end of each month.

When some expense is incurred by a single head, it is easy to edit a single cell value. But, when a constant value needs to be operated on all the cells, it takes some effort. Say, for example, I want to increase the value of each cell by a specific number. It may appear simple with just 5 rows to be edited. But as the number of rows and columns increase, things start getting more complex.

Here’s what my sample data looks like and we are going to add the number 50 to all the numerical values present in the list. And, we are not going to edit each cell individually. Also, we aren’t going to apply any formula. There is another way out.

Sample-Data-Start

Quick Tip: How do you take your cursor to the next line on the same cell in Excel? Enter, takes it to the next cell, right? Try Alt + Enter.

Step 1: For our case, take any empty cell and type 50 in it. Then select the cell (not text), right-click on it and choose Copy.

Copy-Operational-Value

Step 2: Now, select the cells whose value you wish to edit. There are various ways to select multiple cells together and I assume you are aware of those.

Step 3: Once the selection is done, right-click and hit on Paste Special.

Paste-Special

Step 4: The moment you do that you will see a Paste Special modal window. And, as in our case we will choose the Add radio button from the Operation section.

Paste-Special-Add

Step 5: Hit Ok and see the values change instantly. Easy and interesting, right?

Sample-Data-Result

Let me give you another mathematical example. Say you have a sheet of certain items against cost. You decide to run an offer and give 10% discount on all items. Are you going to calculate each value externally and then edit in the sheet? No. You may simply take .90 as in Step 1 and they apply Multiply as in step 4.

I guess you can imagine the potential of such a paste option. So, in Excel, paste does not always mean copying a value from somewhere to paste it as it is. You can do a lot more.

Cool Tip: If you wish to apply such a trick on selected data you may apply filter to sort out data before you begin.

Conclusion

Think about a few scenarios for yourself and I am sure you will be able to fit this trick in some of your spreadsheets. If you have other tricks up your sleeve that complement the one we shared, we’d be more than happy to see them in the comments. Do let us know.

Also See
#msexcel #How-to/Guides

Join the newsletter