Creating and updating sample data

- [Instructor] If you need to update sample data, or create sample data there are a number of techniques that are going to make that whole process faster and easier. I've got some sample data in columns A through G. You might have seen some of these names, some of these salaries, higher dates in other movies, I need to update these periodically. As I click column D here in the status bar at the bottom of the screen my eye is drawn to Min and Max. Max on the right December 2nd 2015, presumably in this organization if we're acting as if it's real we're saying the last person that was hired was December of 2015. I want to bump these all into 2019 or 2020 possibly, so what do I need to do? I need to add numbers to these I certainly don't want to retype them. Now if it's somehow important that I keep the same day of the week I will add a number to these that's a multiple of seven. If I'm thinking of a whole year that would be 364. If it's not really important about day of the week well, we'd probably use 365. If I'm going to bump all these entries up by three years I'll use three times 365, and either I'll do the math on my head, or on paper maybe I'll just do it up here, and ideally we simply want to put in that number by itself not as a formula. We don't want to be copying the formula, so if we want to add three years to all these cells here by adding the number of days there, we simply type 1095 copy that, right click copy Ctrl+C either way, and then we're going to select all the data from D2 downward. Goes down about seven hundred rows or so, so let's click cell D2 and with the Shift key held down double click the bottom edge. All those cells are highlighted. Now we copied that number, now we're going to Paste Special and there is a keystroke shortcut Ctrl+Alt+V. Remember Ctrl+V is Paste Ctrl+Alt+V is Paste Special, and what is it we want to do with that number that we copied? We want to add, you can double click it, or if you have clicked it click OK. Now that throws a monkey wrench into things, but that's easily adjustable in one of two ways. We can go on the Number tab group on the Home tab, simply choose Short Date, let me undo that even faster, but a different look is Ctrl+Shift+3, and that applies the date format this way, but what do we see now on the status bar at the bottom of the screen. The latest enter here is December 1st of 2018. If that wasn't enough maybe we'll add another 365, so you could do it all over again. You can imagine adjusting this in different ways. There could be times when the dates are well into the future and you want to adjust them we could just as easily in those situations use the subtract function instead of add. These salaries as I click column F the average is 88,000. I haven't updated these in quite a while, I'm going to make all these bigger by 20, 25& and here we simply write a new formula off to the right. Equal this value times say 1.2, 1.25 either way we have an answer. We might not want decimals in the answer, so we could certainly round that to the nearest penny that would be comma two or the nearest dollar zero, and we'll simply copy this down the column. Now in this case all we need to do is to take these cells their formulas these are all formulas, and with the right mouse button we can point to any edge say the top hold down the right mouse button and simply drag these in the column F. When we let go of the right mouse button, we want to copy here as values only, and those numbers are strictly there as values now. If we click column F the average salaries now is 111,000, and we don't need the data in column H, nor do we need this here too. Sometimes you're creating brand new data. We want some sample sales data for these states for these months coming up. We're going to run some models here see how this might work out. We might eventually readjust these, and create bigger volume for California and Texas maybe then for the other states, but let's say for the moment we just want some random numbers. The function we're going to be using is called RANDBETWEEN, and as soon as you see it, or you might double click it into place. What's the lowest value any of these we want to be? Let's say it's 500, and the highest one maybe 1500. I won't press Enter here because all these cells are highlighted, I'll press Ctrl+Enter and we'll have this function in place for all of these cells. If you've never worked with the RAND functions by the way you don't necessarily know that the numbers are as we say live, and that means if you click somewhere else, and do some data entry or make a change, what happens to the data? It changes. You can also force this by pressing F9, if somehow you say I don't like the look of those numbers but let's say they're OK. if we want to freeze them, highlight them all with the right mouse button drag in any direction. Say down, up, right, left, whatever. Holding down the right mouse button we let go the right mouse button, and copy here as values only, so I've created some sample data here using RANDBETWEEN, and earlier you saw how we changed all the dates, we also updated the salaries using various techniques with Paste Special. Anytime you work with sample data, and you're trying to either update the data, or create new data the techniques you've seen here will get the job done pretty efficiently.