Microsoft Excel MO-200 – Level 3, Section 1 – Insert data in cells and ranges Part 2

5. Cutting and moving cell data

Now, cutting is very similar to copying, except it deletes the previous information. Now, just having a look at our keyboard, if C is copy and V is paste, cut is X. So you can imagine the information is literally moved on the previous cell. So x it goes away. So notice we’ve got now in a row, undo cut, copy paste. Very useful to remember those four and then bold right next to it. So if I go into my catalog and I cut rather than copy and paste, you’ll see that the text is removed from B seven and put into e two. Now, there is an alternate way of doing this and that’s by moving the mouse.

So it’s at the top part of the cell. So you can see it’s now four headed mouse and just dragging it. So that is also the equivalent of copying and pasting. So that’s fine for text. What about formulas? Well, let’s go back to our practice activity from the previous level, which has all of these formulas. Now, if I was to copy this cell down two, then it would obey all of the relative mixed and absolute rules.

So it would be B dollar three going down to still B dollar three and dollar a four going to dollar a six. If I copy it over here to D six, then you can see the changes in the formulas and that’s what copying is talking about. Now let’s see what happens if instead of copying, I cut this cell and you can see that it still retains the original formula, it still references the original precedence. And that’s the difference between cut and paste.

The formula is literally a carbon copy of what you had before. So, whereas copy updates the formula based on its new location, cut doesn’t. And you’ll notice there’s another difference between cut and copy. If I copy, I can paste information several times. However, if I cut, what I’m doing is I’m moving the original. And so there is no paste function. I can’t paste that several times because all of that is just the one original.

And also with cut, you can’t do the nifty things that we were doing with Pay special in the previous video. It’s just a straightforward moving. You’re not making copies. If you’re cutting, you’re cutting the one cell. Or of course, you could highlight multiple cells and cut them as well, but it’s the original. Now, what happens if you decide you don’t want to do the cutting? You notice at the moment there is this dashed green line that goes around. Well, simple, if I don’t want to do the pasting, I can just press escape and it forgets the cutting and paste is no longer available. So that’s the difference between cutting and copying.

6. Using AutoFill tool, and Expanding data across columns

Now this practice activity that we had in the last level, you can see that the numbers going across and going down are all sequential. And what you may have thought I did was type in 2345 and so forth. But as you did something a bit less time consuming, I started off with the number two. Now let’s have a look at this cell and I’m going to really blow it. Top. You can see at the bottom right hand corner there is a small square and if I hover over it, you can see that the cursor changes to a plus. This allows me to auto fill. And if I drag across, you can see that it is auto filling with the number two.

So it’s not going to 3456 and so forth. So that’s not how I did it necessarily. But let’s try again. Maybe if I put in the number two and then I put the number three next to it and then drag across, will it then go, you’ve put two, now I’ve got three and no. However, what happens if I highlight the two numbers two and three and then drag across? The computer now goes, now I get it, you want 2345 and so forth.

And similarly, if I was going down, then the computer would also work out what I was meaning. Now the question is, what if I didn’t have 2345, but I had two four? Is the computer going to think I want to add two to each of these or 2468? Or does you want to double it? Two 4816. So let’s just move it across and see what happens. Well, you can see that the default 2468, but there’s a little icon at the end with some filling in additional cells and a plus. Let’s have a click on that. And this is a computer saying, okay, I think I know what you want, but just in case. And you can see that there are options. So I can fill series, I can copy the cells. So just been doing the filling. Copying the cells will just mean 2424. I can just add the formatting or I can fill without any formatting.

But none of these is anything that I actually want. I want 2468. So the computers sort of got it, but not quite. So we’ll see how we can get it exactly in a little bit later. Now, what about dates? Well, dates could be the 1 January 2020. What happens if I fill it downwards? Well, you can see that it’s filling in the actual dates of 1 January, 2nd January, 3 January and so forth. Now, what if instead of putting the 1 January, I put in Jan 20, so I’m not putting in a particular date.

And you can see this hasn’t worked. So I’ll put in the 1 January 2020, and then I’ll put in the 1 March 2020 and I’ll highlight these two and see what happens when I drag this down, you can see that the computer has gone ah, I can see a pattern. It goes from the first after month to first to the next month and so forth. But what if it wasn’t quite what you wanted? Maybe the difference between the 1 January and the 1 February is 31 days. Maybe you wanted it to be every 31 days. Well, thankfully we have another way of telling the computer what to do more than just this little list that we’ve got here.

What we can do instead is use a drop down list in the editing section. And here it is, the fill. So I can fill again down right, up left. But it’s a series that I really want to talk about. Here you can tell the computer this is exactly what I want. I want you to fill the rows in columns and I want it to grow. So I don’t want it to be linear, I don’t want it to be add two, add two, add two, I want it to grow. In other words, I want it to in this case double. So if I say that, then you can see that nothing’s happened.

Why would that be? Because it’s a series in which is a bit odd. I want it to fill in columns, so I’ve got to select rows. So if at first you don’t succeed, just try again and now you will see it goes 24624, 816. So if I fill in quite a lot, then I will want it to double each time and it goes from two all the way to 512. It’s similar with dates. So let’s just have the 1 January and select spare cells.

And I can say that I want this to step every day or I want this to step every seven days. Or in our previous example it was stepping every 31 days. So now got 1 January, 1 February, but then the 3 March and the 3 April. And because it knows it’s a date, because that’s the type that I’ve put in, I don’t have to restrict it to days, I go restricted to months, years or weekdays. So weekdays would skip whatever is a weekend in your locale. So you can see it’s jumped the fourth and the 5 January. And the final option is flash fill. All that does is it largely just fills in what was immediately above, so you can see automatically filling in values. So here it just fills in the number four. I hardly ever use it.

So my advice to you, if you find that putting in a couple of values and then dragging across doesn’t work, then I would go to home editing fill series and tell it exactly what you want just by the series in rows and columns. It seems it’s the other way around to what you expect. So I’m filling in all of these columns and therefore I need series in rows and that is how to quickly add values that are almost formalin without using formulas.

7. Practice Activity Number 6 – The Solution

So how did you do in this practice activity? Let’s see what you had to do. First of all, you had to open up this particular workbook, add data validation to cells C Four to C ten. So you’ll only accept text that’s between three and ten characters long. So highlight C three through to C ten, go into data validation which is in data add in data validation and change it so that in the settings it will accept a text length of between three and ten characters long.

So you don’t need to test it. But if you did, then you would find that you would have problems entering anything that was outside of that, but anything inside of that would be fine. Highlight sells D four to D ten and find and replace So I’m going to use Control and H though you could go to the home tab, find and select and Replace. So find and replace all. License words with permit. So license with permit. I don’t want to match the case. So let’s replace all and you’ll see you’ve got here the driver’s license is replaced with the driver’s permit. Copy cell D seven and using paste special paste values only into cells D eleven. So here I paste paste special and I just want the value only.

And you’ll see that there is no formatting, it’s all in black. Move the contents of cell d ten into d twelve. So two ways you could do that, you could hover over the top bit and drag it down, or you can use Cut Control and X over here as well, and Paste Control and V and then use the auto fill tool to continuing the numbering of 1234 downwards.

So I can highlight those and just drag down. Or I could start at four, highlight those and go into the fill and click down. But that doesn’t work. So you have to go into series and make sure it’s column in series because we’re going down and we are linearizing, we are adding one each time. So I hope you enjoyed that particular practice activity. Hope getting more confident with what you’re learning now. See you in the next video.