Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
When working with certain kinds of numerical data, you may run into a situation where you need to change all the values in a range by the same amount. Maybe the data here in columns B through F, this data right here, was calculated incorrectly. And it turns out that all these numbers are off by 5. We would like to bump them all up by 5, increment them. One way to do this and not too efficient would be to create formulas here in kind of a shadow way and then copy those results up here. But there is a much faster way to do this.
If the incremental factor is the same, because in this case it's 5. Let's put 5 into an empty cell here and then copy it. Right-click, Copy, Ctrl+C, whatever and then select this data and use Paste Special. Now you can press Ctrl+Alt+V. That's a fast way to get there and what do we want here? Add, either click it and then click OK or simply double-click it. Keep an eye on a couple of numbers. They are all going to be bigger by 5. We can get rid of the 5 now. We don't need it.
Now a down side for some people will be there is no formula here at all. That's a plus for a lot of us. We don't want to see formulas unless we have to. We have incremented all these values by 5. They are all bigger by 5. We don't have any paper trail of what they have been but it surely didn't take it us very long. Of course, this could have been a much larger range as well. Now, how about a situation where maybe you are speculating about these entries here? You have got another copy somewhere so you would like to see these numbers projected for next year, maybe 10% bigger or any percent you want of course.
So what would we do with these mathematically? We would want to increase these by 10% or another way to say it, let's multiply them by 1.1. So just click an empty cell somewhere, put in 1.1, right-click and copy this or Ctrl+C, highlight the date in question. A standard way might be right-click Paste Special. Do it that way to. What are we going to do here? We are going to Multiply, right there.
Keep an eye on a couple of those. You know the 30s are going to become 33s. You can see those. Those of course will adjust too, there we go, and of course the real totals aren't emerging unless we go to the Home tab and perhaps use the Comma button to expose that, but we have incremented them all by 10%. This one had been a 30, this one had been a 30, and so on. This one had been a 40. So it's a good quick way to change totals without needing formulas and you could imagine another use of this perhaps, in a list like this.
We simply want to change all of these salaries by 500. Put 500 into a cell, right-click and copy it, select all this data here. One quick way would be hold down the Shift key, double-click the bottom edge. Now what? Right-click, Paste Special, and Add. There we go. We bump them all up by 500. Get rid of this. Again, no formulas, no paper trail. It's done, fast and easy, without formulas.
Get unlimited access to all courses for just $25/month.Become a member
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.
Your file was successfully uploaded.