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

1. Appending data to Excel worksheets

Hello and welcome to level three, session one. And we begin level three with appending data to existing spreadsheets. And in some senses this is fairly easy and builds on a lot of what you already know. So suppose I want to add an additional set of data to my existing spreadsheet. I just go right to the bottom and start typing. So 1227, another concerto and that sort of thing.

Now, a few considerations. Firstly, if you’re typing something which exists in alphabetical order, which this doesn’t at the moment you need it to perhaps be in the middle, then you would not insert it right at the end, but you would insert an extra blank row. Don’t overwrite someone’s existing data unless you’ve got a specific need for it. Secondly, if you’re adding additional columns instead of additional rows, for instance, I’ve got attributes maybe I’m going to add a new column for is hidden. Depending on the spreadsheet that you’ve inherited, you might find that inserting additional columns, particularly in the middle of data, may mess up formulas that are already there. It depends.

But basically the moral of the case is if you’ve got formulas depending on existing data, make sure after you’ve inserted the data that these formulas still work. Additionally, typing information right at the bottom can also break formulas. Suppose you got a formula that goes all the way down to row 865, and I start typing in 866. It’s possible that that formula will not include 866 because it only goes down to 865. So what I do suppose I wanted to insert, say, ten new rows. I personally would insert ten blank rolls before the end of the last row like that, and then copy my existing data up and I press delete on the bottom to clear it.

So this way, what used to be 865 is now actually 875. So your existing formulas would still work. Now we’ll be looking a bit more at copying and pasting in this particular session, and then with your formulas working, you can start typing. So these are little considerations to be taken into account when appending data to existing workbooks. Now I can’t tell you the number of times someone’s inherited a spreadsheet and has accidentally broken formulas by inserting additional columns or asserting additional rows simply because they didn’t know what they were breaking.

2. Setting data validation

Now of course you can add whatever data you want, but if you give this spreadsheet to somebody else, you don’t necessarily just want them to add just anything. For instance, let me add something in column A. Now, you know this isn’t really valid data. You know that, I know that, but there’s nothing to stop me for actually doing it. Well, there is if you want, and it’s available in the Data menu under Data Tools.

We have data validation. And as you can see, you can pick from a list of rules to limit the type of data that can be entered into a cell. So maybe if I select the next few cells there we go. Or you could insert highlight the entire column maybe from row five downwards and go into Data Validation. Now, you can see that currently the content of the cells that I’ve highlighted can be any value and that’s a default. But there are other options. You could say it’s got to be a whole number. So it’s got to be a whole number between say, number five and nine nine, for example.

So if I put this in and click OK, let’s see what happens if I now insert something that doesn’t match the data validation restrictions defined for this cell. This value does not match the data validation restrictions defined for this cell. So you can see it works. So I’ll just press cancel. Now, it wasn’t a particularly friendly error message. So let’s highlight these cells again and go back into data validation. So we’ve got two additional tabs, the Input message tab. So this allows you to say enter a whole number and I can say in the message greater between five and nano nine.

This is an example. So that way when you go into the cell, you can see a little tool tip and the user knows exactly what data you’re expecting from them. So let’s go into now the third tab, which is error alert, show an error after invalid data is entered. So this is currently checked. Now, when the user enters invalidata data, show this alert. So it can be a stop, a warning or just information. So let’s put a warning and say this isn’t right as the title and are you sure? In the error message. So now let’s put in the number 5. 5 and here we have a much more friendly message are you sure this isn’t right? Are you sure? And here you’ve got the option to continue. So I can continue putting invalid data or I can say no and try again. So if I put in yes, there we go, we say it’s 5. 5. I get a warning now.

So it’s no longer a stop, it’s now ayusho let’s contrast this with the other option. So we’ll go into data validation changes to information. So I’ll put in 6. 5 and Are you sure? And again it just gives me a warning with a few less critical options. It doesn’t say do you want to continue? It now says okay or cancel. So this is much friendlier, I think, than the default message. But even the default message can be changed. So if I go into the Our alert tab and change style to stop and put in 8. 5, clicking retry gets me back to the cell. I can’t actually insert the data, but it’s a much friendlier message. Now, suppose I set this up after I have entered initial data, or suppose I’ve just used the warning or information to add in erroneous data, potentially erroneous data.

So what I can now do is I can circle invalid data. So let’s highlight some cells and circle invalid data. And you can see that two sets of data are invalid, the five and a half and the six and a half. I was able to insert those in. So it could be that you want people to enter because they’re typing from a page, whatever data is there, but you do the checking process afterwards to see if there’s anything invalid.

So you can set up these data validation rules either before giving the spreadsheet over to somebody else, or afterwards when you have it come back to you. For checking purposes. Let’s clear those circles or go back into the data section and clear validation circles. Now let’s go over to a number column, column J. Now this has to be a date. So let’s go into data validation. And in the validation criteria, I can change this from any value to a date, and we can give it a start date or an end date. So maybe my start date is the 1 January 2011 and my end date is the 30 112 2029.

But these could be dates of birth, in which case you might be going back much earlier. You might be going to allow start date of the first, the first 1900 say. So now, if I put in a date, so let’s say the first of the first 2020, it allows it. What happens if I put in the first of the first 2020 and a space and then a time 12:00. As you can see, it allows that as well. So date really is date or date and time. Now, this uses the 24 hours clock. So 1 January 2020 at 23 59 is 1 minute to midnight. But you can’t put in the first of the first 2020 at 300. That doesn’t really work. The compute has interpreted it as being 30 hours after the 1 January, which makes the 2 January at 06:00 A. m. , but support ours to put in an invalid date. So the first of the first 2000 nope. Or the first of the first 2030. Neither of those are acceptable with our data validation. So let’s put in another data validation.

We’ll say that this has to be text between five and nine characters. So it will allow me to put in the word concerto but not the word high. So it stops people just putting in an X just to make sure that there is a value. So you may be telling people actually I want to put the word blanket. Now there is another type which is custom. Now custom is really when we get into formulas, so we’ll be getting into other things like conditional formatting and whenever we use formulas in more advanced things which will have a look at the expert course here, we can use them in this validation as well. So I can see we’ve got whole number, decimal, date time, text length and custom.

And then the other one that I haven’t mentioned is List. So we have an extension and currently we’re using just three values WMA, MP3 and there’s one more which is m four B and it could say I want to restrict this to just those values. So what I can do is I can highlight these cells, go to data validation and say I want you to restrict this to a list of these particular values. So I’m going to click on this little arrow up which reduces the size of the dialog box and I’m going to highlight values d five through to D 865 my existing data which is valid. So now if I go into this cell, you’ll see that there is a little drop down box right next to it and that contains all of the various values which are valid. But you’ll notice there’s a lot of repetitions. But just doing this allows me to have autocomplete. So when I press W, it’s going R, WMA, M, it’s going old, it could be the first one on the list, MP3. Now it would be better if there wasn’t all of these duplications and so ideally you would have somewhere else, maybe in a different spreadsheet your list.

So MP3, m, four B and WMA and we change this data validation so that the list source is from sheet free and now we have a non duplicatory list. Now we’ll be looking later at how to remove all of these duplications, but that’s for a future video. So this is data validation and it’s very useful. It allows you to have a look and say, okay, this figure, this has to be a whole number or it could be a decimal, could be a date which includes date time, it could be time, it’s got to be a string between this many characters and this number. And it’s got to accept, it’s got to be in compliance with a certain formula. You can have custom messages and even if you leave this at any value, having the ability to have a tool tip when you go into a particular cell is invaluable and then we can say I want you to stop. You can’t enter invalid data or give a warning or information with a message and finally afterwards you can circle invalid data. So very useful data validation, especially if you’re giving a spreadsheet for others to fill in. And I think it’s one of those features which is very useful but a bit underused. So here it is. Data validation.

3. Finding and replacing data

Now, we’ve had a look in the previous level at Find. So we went to Home Find and select and Find so we could look for the word Symphony. And you’d see that there are 70 repetitions, 70 cells where the word symphony is used. Now let’s change this from the English Symphony to the French Symphony with an I at the end. And you can see it’s found 42 of these, mostly the words symphonies. Now you might want to be that you might want to alter the existing data so that all things that really refer to symphony. So symphony, symphonies, symphonic would all be changed to the word symphonies or symphony. Similarly, you might have a spreadsheet with a product or during tab and let’s say that has the word scanner and scanner with one or two ends, and it might be a huge amount of data. And you don’t want to have to modify them individually. You want to unify the data so that the same identical data is spelled in the same way. And this is when you can use the Replace tab, which is included in Defined and Replace, but it’s also there in Home Find and select Replace. Just as an aside, you’ll notice that this is Control and H. Okay? So we’ve got control.

And f for Find. So if I bring up a keyboard, control and F for Find, Control and G for Go to. So why is replace, control and h? Because really it’s the next one along. They wanted it close by, they wanted it together. So however you get there, I personally that’s one of those ones that I do actually remember, Control and H. So we could replace the word symphonies with the word Symphony and we can find the first occurrence and we can replace it. And if we go back into it, you’ll see that it’s now saying Beethoven Symphony number one and five. And similarly, I can click on Replace All and that will replace all of the 41 occurrences which are there. Now let’s have a click on the Options tab and see what we’ve got there. Well, we have got the within, where are you searching Finding or where are you replacing? You can search within one individual sheet or throughout the workbook. Now, be careful altering this. It may be good to do it once. So maybe we got the word Symphony elsewhere or symphonies and you want to replace them wherever they occur throughout the workbook. So let’s click replace all. There we are. It has replaced one instance throughout the workbook. So I’m just going to close this down. Now, suppose I wanted to add an N to scanner and so I’m going to search for an E and replace it with a double ne. A bit sloppy, but I’m just showing you what might happen. I’ll get rid of the options.

So an E to a double ne and I click Replace All and it’s only going to replace one. Hang on, it’s replaced eleven and that’s because without the options there you wouldn’t know. Now click options. It’s visible that you’re replacing it throughout the workbook. You would think you’re just replacing it in this one existing spreadsheet, but it’s still remembering your previous setting of workbook. So that is a bit dangerous. So use it and then change it back to sheet if you so wish. So I’m just going to undo what I did. Now personally, if I do want to search for an entire workbook, what I do is I do the find first and I click on Find All so I can actually see what I’m replacing without accidentally replacing information that I didn’t want to replace. So let’s see what else is there. We can search by rows or by columns. In other words, do you search across row five and then row six and then row seven? Or do you search down column A, then column B, then column C?

The next one is look in formulas. So it could be I want to find a certain formula or I want us to find something in the values. In other words, I don’t care what the actual formula is, I want to see what the value is so we can have a look at that if we go to our practice activity that we had a look at at the end of the previous level. So we’ve got lots of formulas in here. So it could be I want to find the number six. So I’ll click on six and I’ll click Find All and you’ll see that it’s found all of the formulas with the number six in it, regardless of what the value is. If I change the lookin to values and click Find All. Now you can see it’s found all of the values which contain the number six. But suppose just going to say, no actually I just want to find all of those cells which contain six and nothing else.

Well, you can click on matching cell contents. So now if I find All, I am now restricted to just 1234. So it’s these four cells that contain the number six and only the number six and not another cell that contains 16. For instance, the final option that I want to have a look at is the match case. So this is where you say, I want to find the word symphony with a capital S, not a lowercase S. So if I select match case, then it will find all of those occurrences with a capital S. And if I get rid of that, if I change it to a lowercase S, it’s now finding a further 42 cells. There’s also formatting, but we’ll have a look at formatting later. So you could say I want to have a look for every cell that contains symphony that’s in red for instance. But we’ll be getting into the formatting later and this is more detailed look at the Find and Replace dialog box.

4. Copying and pasting data, including Paste Special

We did a fair bit of copying and pasting in the previous session of the previous level. So copying and pasting data, if it’s just text, then copy control and C and paste CTRL and V. And there you go, the text is replicated. Now, if it’s a formula, then copy and paste will adjust the formula depending on whether it is relative, mixed or absolute. So we had a look at all of these dollar signs in the previous level. So without these dollar signs, if I was just to copy this formula equals b three times a four, then well, you see what happens, the numbers are so big that Excel can’t actually calculate them, so they are all relative. So b three becomes b four, which then becomes b five, b six when you copy it downwards. So we had a look in the previous level at how important these dollar signs were for keeping formulas relative. So the dollar sign locks what’s, immediately afterwards. But that’s not the only thing about copy and paste. It’s not just about text and formulas and so forth. It’s all about formats. So let’s make this text yellow wave background with red foreground.

So if I copy it and paste it, you can see that the new cell inherits this yellow background, red foreground. But what if I wasn’t so much interested in the content, but in something else? For example, suppose I was interested in the formatting, the yellow background and the red format and the red foreground. Well, I’d still copy and then I would go to paste. But the drop down underneath paste. Now, there are a lot of icons and you can see this is a standard paste.

And when I hover over these icons, you can see what the result is going to be. This would copy just the formulas and not the formats. This will copy formulas and number formatting that would keep the source formatting that would copy without any borders. And there are lots of other so this one over here done in other paste options that would just copy the formatting and not the value. So very useful if, for instance, I was copying some text and I wanted all the other text that I’m wanting to paste, I want the text to remain, but I want the formatting to be added as per the original. That’s what this formatting can do. But to be honest, I don’t really like going through all of these icons, having to try and memorize what they are or work out. It’s not that one, it’s not that one.

No. Instead, what I do is I go through the paste special dialog box and this gives me this in plain English, if I want to paste formulas, if I want to paste the values, if I want to paste the formats and so forth. So let’s just see what we mean by pasting the values. If I was to copy all of this, which contains formulas and then go into my paste special and click on values. The formulas would disappear and they would be replaced just with the values. So I’m just going to undo that because I actually like these formulas or I can copy the validation. We just had a look at validation, so maybe you’ve got validation in one particular area and you want to copy it to somebody else somewhere else. Now, I also want to have a look at this operation add, subtract, multiply, and divide. What does that mean? Well, I’m going to now just do what I previously done. I’m going to replace this with the values. And now I’m going to put the number two in a different cell and copy it and highlight all of these values.

If I go into the paste special and click add, all of these values will be incremented by the number two that will be added. Similarly, if I go for subtract, multiply, or divide, they will all have this operation based on the number two. So that can be quite useful. So if you just want to double a particular column, you don’t have to use formulas. You just type the number two, copy it, and then use this paste special dialog box to just double all the numbers. Now, I was talking about the keyboard shortcuts, so let’s just have a look at the keyboard shortcuts. So copy is control and C. Now, paste is control and V, which sounds very odd. Why not? Is it paste? Well, firstly, I think they wanted something right next to copy, so they put the next letter along, just like as you saw, replace is H because it’s right next to find and go to. But if you want to try and memorize this, and I really recommend you do, imagine that the letter V is a container of glue, a glue gun, or maybe I’m filling that up with glue. So I’m copying and then I’m dipping into the paste. So controls. C. Copy. Control. V. Paste. And in the next video, we’ll have a look at the next item cut.