Mar 18 2017

How To Use Paste Special To Increase A Range Of Values All At Once

 

How To Use Paste Special – Multiply

 

What do you do if you want to quickly increase every value in a very BIG range of numbers, by a specific amount? For example, say that you want to increase all of the numbers in a large data range by 5%. If you have tens of thousands of rows and dozens of columns, that is definitely what Microsoft Excel experts would apply the term that is dear to their hearts, BIG Data. So just how much of an effort would this take? Do you need to be a Microsoft Excel data scientist to do this? The answer will come shortly.

 

Second example, an easier task. What if you wanted to do the same thing but for just a single column of numbers in Microsoft Excel, say 500,000 rows? Of course one possibility here is to temporarily create a “helper column”. In the helper column you enter a new formula that will multiply the cell by 105%, thus increasing the number by 5%. If you use this approach you would have some work to do however as you would need to take the time to copy the range of formulas and paste them as values over the range of data that you want to update. Once this is done you would need to delete the helper column. And in the end you have exactly what you were looking for.

 

You could apply this same approach to the first scenario, but you would need to insert dozens of helper columns, not just one. That is not that much more of an effort, but still it is more of an effort than needed. In fact even the single column scenario takes too much of an effort to accomplish the task at hand.

 

If you use what I am about to show you, you could accomplish the same thing in just a few seconds, without much thinking, or many steps. Basically a copy and paste special does the trick.

 

Follow these steps to markup a range of values all at once.
1. Select an empty cell.
2. Enter 5% into the cell.
3. Copy the value in the cell.
4. Select the range of cells that you wish to markup.
5. Select Paste Special.
6. Click on the option button for “Multiply”
7. Click the Ok button.

 
 

1. Select Cell Where You Will Enter Markup Value
 
Select the cell where you will enter the markup

 
 

2. Enter The Markup Value Into The Markup Cell
 

 
 

3. Copy The Value In The Markup Cell
 
Copy the value in the markup cell

 
 

4. Select The Range Of Cells That You Wish To Markup.
 
Select Paste Special

 
 

5. Select Paste Special.
 
Select the range of cells that you wish to markup

 
 

6. Click The Option Button To “Multiply”
 
Click the radio button to multiply

 
 

7. Click The OK Button.
 
Click the OK button

 
 

That is all that there is to it. Follow the steps above to markup a huge dataset by a specific amount, in seconds.

 
 

References

 

* A really good post on “Use Paste Special To Perform Calculations”

 

* This page covers what we are doing here, changing values in a range via Paste Special Multiply

 
 
 

* If you are looking for answers to your Microsoft application questions, and if you want those answers for free, then reach out to the amazing team of experts at Vbaexpress.com. VBAX is one of the leading Microsoft forums and it is.

* If you are an expert in any of the Microsoft applications, please join the team at Vbaexpress.com that answer the questions in Excel, Access, etc. It is a great way to be part of the Microsoft Excel community.

 

 

If you would like to hire someone to assist you with your Microsoft Excel needs, please give us a call or shoot us an email, we would be happy to assist you.

Call us now and get started today 877-392-3539

Free Consultation

 

click-here-to-contact-us

Random Posts

Did you find this post helpful? Try signing up for our free daily tips.

Enter your email address: 

          

LEAVE A COMMENT

Subscribe Form

Subscribe to Blog

Sign Up for Excel Hints

Free updates with Excel Hints, Tips & Tricks

Categories

Archives