From the course: Excel 2016: Advanced Formulas and Functions

Updating values without formulas

From the course: Excel 2016: Advanced Formulas and Functions

Start my 1-month free trial

Updating values without formulas

“

- Sometimes you need to change the values of cells, and although you could write formulas, it might be faster to use another technique that allows you to change values without formulas. Someone has sent you this list, and maybe these represent some kind of an index number. You're familiar with the source data, you recognize a problem. Whoever made the calculations forgot to add five, and all these entries are short by five. You'd like to make them all bigger by five. The numbers are sufficiently small, we can probably do this in our head and just retype the entries. But, of course, it could be a much larger list, and that would take a lot of time. So, anytime we need to increment numbers or decrement them, or possibly use multiplication or division, we can, by use of Paste Special, change values without formulas. If the incremental value here is five, click in a nearby cell, type five. Copy that, control C or right click and Copy, either way. Select the data that you want to change, and go to Paste Special. One quick way to get to Paste Special, control alt V. Remember control V is Paste, control alt V Paste Special. And what do we want to do here with that five, we want to add. So keep your eye on the upper left number, it's 50, it'll become a 55, the number to the right will become a 47 and so on. We will see new results here, no formulas. And there they are. We don't need the five anymore, get rid of it. Not a formula in sight here, we've changed all these. A different situation over in columns H through M. Maybe it's at the beginning of a year, and these are the sales figures for the last six months of the previous year. And what we'd like to say is, we want your performance to be ten percent bigger next year. So we want all these numbers to be bigger by ten percent. That 60 will be a 66, the 50 will be a 55, the 40, a 44. Those are easy enough to do in our heads, but the others take a little bit of work. And once again, we could have a huge set of numbers, but, even here, it would take us some time if we write formulas elsewhere and copy, paste and so on. So, in a nearby cell, let's put in that incremental factor. This time it's going to be a multiplier. In other words, if this grows by ten percent, we'll be multiplying it by 1.1, that'll give us the 66. So, 1.1, copy it, control C, select the data that we want to change. Once again go to Paste Special, control alt V is the fast way, and this time we'll choose multiply, double click it to multiply. And we've got our new answers. That had been a 60, now it's a 66. This had been a 50, now it's a 55, and so on. We don't need this anymore, delete. In both cases here, we have no paper trail, so to speak. In other words, no recognition anywhere on the screen of what the results had been, we've simply altered these. Now if you do other things and want to come back and change these and it's too late to do an undo, you could consider doing the reverse, that's usually not the issue, but possibly we could select this data after having 1.1 in the cell, then coming back here and we could divide these entries by 1.1. But that's less likely to be needed. So, any time we need to increment by adding or subtracting, multiplying or dividing, we can use this technique, putting that factor first in a nearby cell, copying it, then selecting the data, then getting to Paste Special with control alt V, and then apply the appropriate operand, addition, subtraction, multiplication or division. A quick way to change results without formulas.

Contents