Alter numeric data without formulas

- [Voiceover] There are times when you work with numeric data that you wanna make changes to the data, but not necessarily use formulas. And you can do that with the feature called Paste Special. You might be familiar with that feature for doing other kinds of things with numbers, but a special use of it involves incrementing, decrementing, even dividing and multiplying numbers by a certain amount without formulas. I've got some sample data on this worksheet and as I click column B for Hire Date here I'm looking at the bottom of the screen, in the status bar, and I see that the highest entry there is April 4th of 2014.

Now let's imagine it's mid 2016, I want my list to appear more up to date, many of the entries we're seeing already are pretty far in the past, but that's OK, because these are hire dates, but I want some of the entries in there to be more recent than the latest entry, which is April, 2014. I want to add a number to these. Now if I want to add a year exactly I'd put in 365 in some nearby cell, but I also wanna make sure these are weekdays, and right now every one of these happens to be a weekday, that's the only day we do hiring on, a weekday.

And so we wanna make sure it's a weekday. If we add 365 it'll move each of these weekdays up by a day. So we can add 364 if we simply wanted to change these by one year. Wanna change it by two years? We'll put in 728. And depending upon the year difference we'll use other numbers. Whatever number we use here if we want to keep the same day of the week we'll make sure this number is evenly divisible by seven. And I'm sure you know that is. We could put in 777 in another case or 910 or any number divisible by seven evenly if we want to make sure these stay the same day of the week.

So that's gonna be our incremental factor. And I'll show you two ways we could do this. What we're about to do is say in effect let's add this number to all those entries over in column B. So start with either right-click and copy or Control + C, either way, let's go select all the data from B2 downward. And a quick way to do this after selecting cell B2, hold down the Shift key, double-click the bottom edge. All those cells are highlighted, let's go to Paste Special.

We can get there off the Home tab and Paste, it's gonna be faster with Ctrl + Alt + V. A lot of you know Ctrl + V means paste, Ctrl + Alt + V means Paste Special. And we're about to Add. We're gonna be adding that number. Now when we do this because the format of the number we're adding is not a date format the answer we're gonna get will not be in date format. We'll have to make some adjustments. But looking ahead here, if we're about to add 728 days, that's essentially two years, minus a day or so, the answer we're likely to get for that very first entry, right now it's October 20th, 1995, it's likely to be something like October 18th of 1997, something like that.

But at first we won't see that. Let's click OK. And so our quick fix will be on the Home tab in the Number group here, drop arrow, we'll simply use Short Date. Actually it's the 17th of 1997, there we see it. Now another approach to this, I'm going to backtrack by pressing Control + Z, it's just a question of when we're applying a format. After putting in the 728 here why not just take this format, or the format of any of the dates, using the right mouse button drag this cell on top of the 728, right mouse button, and Copy Here as Formats Only.

Now if I were to make the column wider you might say, what is that all about? 1901. We don't care what that looks like, it really is the value 728, but we're applying the format ahead of time, so we won't have to apply it later. So it's here, Control + C again, I'll come over and highlight the data, holding down the Shift key, double-clicking the bottom edge, and now we go to Paste Special, Control + Alt + V, let's Add, and click OK.

Same numbers we saw before, but now we don't have to do the formatting. Now we did have to format this ahead of time, but that probably would have been simpler than formatting these. Nevertheless, we've changed all of these values. And as I click column B now what's the latest entry? It's April 1st of 2016. And you could imagine doing that not only for sample data, but in other kinds of situations as well. We changed all these by incrementing them. Now off to the right we've got different kind of data. Now it turns out that in the calculation of these someone has made a mistake and it turns out that all these numbers are too high or they're too low.

Maybe they're all too high by the same amount, maybe they're too large by five. So we could decrement these by five, that be the same kind of thing we did before, but this time subtracting. And the decremental factor we'll put it out here, maybe it's a five, we'll just copy that. Remember, we could right-click and Copy, it's just as easy as Control + C maybe, highlight all the data in question, once again, paste, Control + Alt + V, and we'll simply add five to all of these. Click OK, keep an eye on that 40 in the upper left hand corner, it's gonna become a 45, and so on.

All the others have changed as well. Let me undo that and let's approach it from a different angle. Get rid of the five. Suppose it's at the beginning of 2017, the final numbers are in, here are the 2016 sales. You're the sales manager and you want all of your staff here and some of them are responsible for different states, you want all these to grow by 10%. Now possibly what we'll do here is copy this list, because maybe we wanna keep this, but at the same time when we're copying it we don't have to set up formulas, so all I'm going to do here is highlight this data and then hold down the Ctrl key, drag an edge, drag it off to the right right there, let go of the mouse first.

OK, so for the moment we've got duplicates. But let's make all these numbers here get bigger by 10%. Now remember, these are just numbers right now, no formulas, let's make them all bigger by 10%. If you're doing the math I think you'd know you need to multiply these by 110%, or simply 1.1. And the 40 will become an even 44, the 50 over here will be a 55, there's a 30 in there somewhere, there it is down there, that's gonna be a 33, but the others of course we really couldn't do in our heads very easily.

So let's change all these. Find a nearby cell, put in that value, 1.1, right-click and Copy, select these cells, activate Paste Special, Control + Alt + V, and this time it's gonna be multiplication, Multiply. Click OK. We got our new numbers there. In some cases we're seeing two decimals, sometimes one, we probably should format them more consistently, but remember, the 40 is now a 44, that's an even number, the 50 over here became 55, the 30 down here for September in California is now a 33, and so on.

So formatting the numbers for consistency's sake would make sense, click the comma button up here, probably the fastest way to do it. Realign the column widths, drag it across, double-click. These numbers are 10% bigger than the ones over here. Now of course we could have written formulas here to make these be 10% bigger, but we didn't need the formulas. We could've all along changed this heading to be 2017 and simply applied the 1.1 factor to the existing data, but we did wanna have both sets available just for comparison's sake.

So these are techniques for changing numbers without writing formulas. Another small example here, we've come into some extra money, we don't wanna work any kind of algorithm, we simply wanna give everybody 1000 dollars more. We've got about a quarter of a million. That'll more or less cover it here. We've got roughly 750 employees. Let's take that value 1000, copy it, Control + C, select the data here, click here, hold down the Shift key, double-click, Control + Alt + V, we'll Add, so that first number's gonna go to 62,000, the next one 42,000, and so on.

They've all jumped. And once again, perhaps a comma, decrease the decimals. By the way, one little tip of using that status bar that's related to this, typically cells with text are General format and you make no changes there. This says General because cell A1 is General, but it doesn't mean the others are. Now most of the time you don't bother changing the format of those. I'm gonna change them all back to General just to make sure. Now if I click column D I can read those numbers in the status bar, but there are no commas and it makes it a little bit trickier to read.

Same thing with column E, although there not as much. But as I click column B the status bar is practically worthless. Those numbers are not readable or workable at all. So in formatting column B instead of General format, I'm gonna do this for the entire column, I'm gonna apply Short Date. Now this does nothing really to cell B1, keep your eye on it, didn't really change it, but now when I click column B what do we see in the status bar? That's valuable information. I know that the oldest date in there is April 24th of 1995, the most recent date is April 1st of 2016, there's even an average date that makes sense in there.

And the same thing will happen with Salary now. I've got commas, once I format this, not formatted yet, but I'll use the comma format, decrease the decimals, it's not doing anything substantially different with cell D1, but it does have the same format as the others. So when I click on column D then it means that I can see the data and read it easily. So these are related techniques to the idea that we can make changes to numerical information by using, not formulas, but a copy, paste technique where we can add, subtract, multiply, or divide a common factor to a whole set of numbers at the same time.