How To Use Paste Special To Increase A Range Of Values All At Once
- 0 Comments
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.
That is all that there is to it. Follow the steps above to markup a huge dataset by a specific amount, in seconds.
* 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