Tips for creating and updating sample data

- [Instructor] When you're creating sample data, or possibly updating sample data to make it look more current, there are a number of techniques you can use in Excel. In this worksheet, we see an HR kind of list here. And you'll see the social security numbers in column D. These were all randomly generated, and every so often I get a phone call, or an email from somebody that says, "Are these real?" well, I made them all up, and so I have to say, some of them could be real. Now, odds are a billion to one that yours isn't here. If it is, I'll gladly take it out. But, how do we generate numbers like this? And you can imagine other situations where you've got an ID scheme.

Maybe it's just six characters, eight characters, whatever. Here we have nine characters. How do we create a list like this quickly? Sample data. I'm going to zoom in a little bit here, and momentarily make column D wider. Not truly necessary. But to make this more visible, more clear. Let's use the function RANDBETWEEN. Just as you see this, by the way, in the list here, you could tab it in to place. RANDBETWEEN. So, the lowest number we want here, would be all ones, and there are nine of them. One, two, three, four, five, six, seven, eight, nine.

And the highest number, all nines. And nine of those too. I'll press Control Enter, so the active cell doesn't move. Completes the entry. Double-click the lower right hand corner. All of these have changed. Now, if we leave the numbers in this state, they're going to be changing all the time. This is something we could refer to as a live function. See it up in the formula bar, or right here. This changes every time we make worksheet changes. So, if I go over to cell C2 for a moment, and change that dash to a slash, look what happens when I press enter.

Keep your eye on column D. All the entries changed. If I make other changes, that's going to happen all the time. So, at some point, you will want to freeze these numbers. Just select column D, and a not so well known, but very efficient technique to turn these into values, in other words, throw away those formulas, use the right mouse button, and temporarily drag this into another location. For example, into column E. And then right back on top of itself. So, with the column having been selected, point to the right edge, hold down the right mouse button, and in one fluid motion, drag into column E, then back into column D.

Let go of the right mouse button, Copy Here as Values Only. So, these are no longer formulas. Going to readjust the column width, and move on. Now, the hire dates here, haven't been adjusted in a while. What's the most recent one? If you click column F, sometimes you will see valuable information in the status bar at the bottom of the screen. By the way, any time you highlight two or more cells with values, you're likely to see this kind of information. Maybe not all these indicators though. So, right click in the status bar, and amidst all these check marks, these six right here, can be checked or unchecked.

You've got the choice. I say, "Why not leave them all checked." Because, any time you highlight two or more cells, you're likely to get some valuable information, down in the status bar. Now, sometimes this doesn't work so smoothly, if you didn't format the entire column here. In other words, if this cell had not been formatted. Right now it's in date format, which seems a little strange maybe. But, if this were general format, as it often is, because who formats that as date? So, what happens now if you click column F? Can't use those numbers very easily.

So, let's click column F, and make the entire column be so called Short Date display. The format that we see currently. Now, we want to update these. The highest entry here, under Max, May 20th 2011. That's the latest date. And let's say it's early or mid 2017, let's add six years to all of these. Now, if it's important to maintain the same day of the week, we want to use a number here that's a multiple of seven. In other words, we want to be adding seven or 14 or 28.

But talking about six years or so. Maybe doing some quick math in your head, it's going to be around 2,100 days. Now, that doesn't have to be exact really. That certainly is a multiple of seven. Let's add 2100 to each of these dates. So, in an empty cell, let's put 2100. Copy it, Control C, or right-click copy, and then let's select all these dates from cell F2 downward. So, clicking on cell F2, hold down the Shift key, double click the bottom edge, and now what? How do we get that 2100 increment into these cells? Paste Special.

You can either right click and go to Paste Special, or press Control Alt V. Now remember, Control V means Paste. Control Alt V means Paste Special. So, what are we about to do here? Add. We'll be adding 2100 to each of these dates. Now, in the process, we're going to lose the format, so it will look weird at first. But, on the Home tab, Number Group, drop arrow, let's choose Short Date. And now we see dates that make sense. And what's the highest date in the entry now? It's February 17th of 2017.

So let's say our numbers look more up to date. And let's say we haven't adjusted those compensation totals very much recently. Let's bump them up, maybe by 5%, 10%. So, we can use this same cell here. But, write a formula that deals with those numbers. And we'll be using that same technique that we saw earlier, to update these values. So, one technique would be here, if we want to change these by 5%, take each of these, multiply them by 1.05. If we want to make sure the salaries are whole numbers, let's use the Round function here.

Left parenthesis, and after the 1.05 comma zero, to round these to the nearest whole number. Now, before changing all these, let's click column H. What's the total at the bottom? 44 million, 214 thousand. So, let's copy this down the column, by double clicking, and those are the new numbers. And you can certainly see the changes. And here too, with right mouse button, I'm going to drag this data into column H. Holding down the top edge, drag it into column H, let go of the right mouse button, Copy Here as Values Only.

And that new total of the numbers there is 46 million, 425 thousand. We don't need the data in column K any more. Delete those, and we've got our pure numbers of here. So, we bumped up all the salaries, the compensation amounts here. I've got another set of data, off here to the right. I want some sample data here, of a range of dates and a range of times in the day. And let's say based on what we're seeing over in column S, and T and U to the right here, we've got some settings already in place here.

This indicates the range that we want here. Make this, by zooming back just a little bit, we'll see those. So, let's say all the dates we want to put in here. Now, you could imagine doing these for a single day, for sample data. But let's say we want our dates to range between these two. So, in cell P2, we use that RANDBETWEEN function again. Equal RANDBETWEEN. And the starting date that we want here, is January 1st of 2015. Now as we copy this down the column, we always want to be referring cell T1, so we'll press the Function key F4, to make that absolute address, comma, and the maximum range comes out of cell U1.

That too should be an absolute reference. Press the function key F4. Press Control Enter. That's one of those dates within the range. Double click to copy this down the column. Now, let's say here too, as in our prior examples, if we don't want these numbers to change any more, we use the right mouse button, drag it right, left, up, down. Makes no difference. On top of the cells. Let go of the right mouse button, Copy Here as Values Only. Now, with time of sale, it's going to be a little bit tricker.

We need to use, pretty much the same capability. Once again, RANDBETWEEN. But, if we use the starting date here, 6:00 am, in other words, all the sales we use, occur between 6:00 am and 8:00 pm here too. Here too, we're putting in the absolute reference, after we put them in here, F4. Enter. We get an answer. But, if we drag from that lower right hand corner, a few cells downward, we get the same answer over and over and over again.

And so, you wouldn't necessarily know what we have to do here, but we have to multiply these numbers to turn them into whole numbers. Now, you may or may not know, that when you work with times, even though that says 6:00 am and 8:00 pm, those are pure values. If you use the comma button here, that's one approach. That shows you what the real values are. The RANDBETWEEN function is designed to work with whole numbers. So, let's undo the formatting for the moment. We need to make a change in our formula here, to multiply these numbers, by the number of minutes in a day.

And these are the kinds of things that you don't always encounter, or recognize ahead of time. So, we need to multiply this by, how many minutes are there in a day? Well, there are 24 hours, and 60 minutes each. Now, you could've done the math ahead of time, and that's 1440. We could multiply by 1440, or do it this way. So, over on this side I'll do the 1440. Now that puts us on the right path, but still doesn't calculate the way we want things to go here.

We then have to divide that by 1440. Not exactly obvious. And we get an answer there. That's looking good. Let's drag down a few cells. Are these within the range? Yes they are. Let's double click and copy this down the column. And just to make a quick check here on this data, to make sure that it falls within our range, let's write a function here, to see what the maximum value is there. And that looks funny at first too. We can take one of these formats, use the right mouse button, drag any edge into here, Copy Here as Formats Only.

That's within the range. That's the latest possible entry. Let's drag this down and change it to MIN. Same idea. The earliest possible entry, that we have on our list right now is 6:00 am. Now, this data's still active. And, by the way, when you are working with random data, if you want to change all these, because somehow to you, they don't look random, just press the Function key F9. That does a worksheet recalculation. So, I'm about to press F9. All of these cells in column Q will change.

And we keep an eye on these too, these are going to change. Active cell can be anywhere. I'm going to press F9. So, our earliest entry is 6:01 am, latest entry is 7:54. We see all the numbers in column Q. Check it again. Check it again. I'm simply pressing F9. We could be doing this with the entries in column P too, if they were RANDBETWEEN, but no more are they. They were changed into their pure values. So, we see a number of different techniques here, using random functions, the RANDBETWEEN function, and different techniques for updating sample data.