Microsoft Excel 77-727 – 2.1 Insert Data in Cells and Ranges

5. 2.1.5 Insert and Delete Cells

Have a cell or range of cells selected within the body of the Worksheet, as opposed to having an entire row or column selected. Then when you right click and select Insert or Delete, you’ll see this menu. Which option you select will affect the Worksheet. So you need to be careful. If you just have one cell selected like this, then when you choose to shift cell up or down, you can see that that’s what happens. If you select entire rows or Konyuns, then this is the effect. If you have more than one cell selected, then the effect is magnified. So here I’ve selected this group of four cells over two columns and rows. Now, when I select to insert entire row, two rows are inserted. It’s the same with the Colonels. Two columns are inserted. This works the same for the Delete option, but obviously in reverse. So if I select the same group of cells and select the Lease option, you can see that two rows that delete it. If I select just one cell and delete, I can elect to have the other cells moved up or left and so on.

6. Exercises Domain 2.1

Okay, so we have the monthly revenue open and we want to select B three and use autofill to get January down to December. Very simple. Widen the kind so that you can see everything. You can double click on the intersection or drag it. Now we put the title in January and sell E Two. Make it bold. And next we’re going to autofill that across the spreadsheet. You can see in a couple of instances here, we’re going to have to widen a couple of volumes. No, actually no, it works fine. We’ll just widen a little bit. Anyway, now we’re going to Q Three and autofill to Q 14. So we autofill this formula at E 15. We’re going to do the same thing for we’re going to autofill all the crosses spreadsheet. Now we’re going to copy the information from E 15 to P 15 into the clipboard.

So we’re going to use a copy command. So we have to select E 15 to P 15 and copy. Now we’re going to use Paste special. We only select Values and we’re going to transpose. So Paste special values only and transpose press OK and you can see the effect. Next we’re going to put in a title salary One. So the title is monthly revenue by Airport 2017. We type that in and next we’re going to merge and center that title over the range from E One to Q One.

So you have to select E One to Q One and then press the Merge and center button. Change the font if you wish. So, size 16 and bold. Next we’re going to use Autofill to copy the spark line. The first spark line curators in cell or tree. And we’re going to autofill that to range all four to all 14. Just making this type of bold. But now we’re going to go to that spark line graph and we can auto fill the spark lines. And now we’re going to create a name range. So we select from D two to Q 15 and call it monthly revenue.

Remember that you can’t have a space in the name, so I’ve put in an underscore between monthly and revenue. Next we’re going to use the name manager. So when you go into formulas, a name manager gives you all the ranges you have. We select the one we want and we’re going to edit it. And you can see here that you can change the range of cells which are applied to that particular name. Let’s select a new range of cells. So we click and drag and then we say okay, and that range has now been changed. Now let’s select Cancer V One and paste it into A one, but only using the formatting. So we just want to know we could have used the former painter here, by the way, also so you can see all these paste options.

Now I’ve just made mistakes, so I go back and I’ll do it again. So I copy and I paste only the formatting. And you can see it looks exactly the same. Now let’s do find and replace. So we go to find or replace. So here we are with the dialog box put in Padua. So we want to find Padua and replace it with pad stock, and it tells you how many occurrences it finds. So when you say replace all, there you go, there’s only one replacement. Next, we’re going to use flash fill. So on a four, we have already generated a code. We’re going to go to Data and slash Fill, and it automatically generates those cold codes for the rest of the cells. We take out the one and a two as instructors.

Now we’re going to cut the spark lines and select them and cut. And we’re going to insert those cut cells at D two. So you go to D two, we’ve cut. Now we go to D two. We have to go to the home button and insert. And we know insert cut, sadly appear very nicely. Next, we’re going to copy the data from e one to e 14, and we’re going to paste it into a different worksheet. Just so you know, you don’t have to do the same worksheet. You can go to a different worksheet. Or we go to the revenue summary to a one and paste.