How to Delete and Add Rows and Columns in Microsoft Excel 2016
During this Microsoft Excel 2016 training tutorial video, you will learn how to insert and remove rows and columns.
Video Transcript
In this section and the next couple of sections we’re going to look at formatting worksheets. You should by now have a pretty good idea about how to enter data into worksheet, how to edit it, and also how to format it from the point of view of the type of data. What we’re going to start looking at now is how to do things like add and remove rows and columns and how to do some of that coloring in, how to change the background colors of cells and how to use specific fonts, as well as things like alignment of text and numbers.
So in this first section I’m going to concentrate first on inserting and removing rows and columns. But before I do that just one thing. The reason for spending time on formatting worksheets is not only to make them look good which is often a very, very important thing in itself. I’m a great believer in the fact that presentation is everything. But also by judicious formatting the message that you’re trying to put over with the information in your worksheets can be a lot stronger. So making sure that the points that you’re trying to make easily stand out is very important and formatting is in turn a very important aspect of that.
So the first job I’m going to do is to tidy up this worksheet. I saved it at the end of the previous section, Business Expenses 02.xlsx. I left it in quite a mess as you can see and there’s quite a lot to do to make this worksheet usable again and certainly presentable.
So before I do that I just want to do one other thing, mention one other thing because this is going to be quite important from now on. If I click on a cell, let’s click on that one. It’s got 42,072 in it. It’s actually of Accounting Data type at the moment so it’s $42,072. If you look over to the left of the Formula Bar there’s another box here. It’s actually called the Name Box and it says K8. And K8 is of course the Cell Reference for that cell. It’s column K, row 8. Now when it comes to deleting and inserting rows and columns let me just quickly delete a column here. I’m going to delete column D. So I’m going to select it, I’m going to right click and I’m going to say Delete. I’m going to delete column D. Look what happens to that cell, the one that was K8 just now is not surprisingly J8. Now you will see as we start inserting and deleting rows and columns that the cell references change accordingly. And as we’ll see in three or four sections from now once we start including these cells into formulae and functions and drawing graphs and so on it’s quite easy to think well what happens to my formula here. If I’ve got a formula that’s going to add some cells together won’t those formula get upset by inserting and deleting rows and columns? Won’t all my references change? Won’t Excel get confused? Well the answer to that is no it won’t because although the references to your cells change every time you insert a row, insert a column and perform many other operations as well, Excel always keeps track of any changes which are consequences of changes that you make. So if you delete a row or a column or something you don’t have to worry about that. Excel will look after the consequences of it for you.
So let me start by doing some inserts. So I’m going to insert a new column. If I select column E and right click and click the Insert I will get a new column inserted. Now it’s actually inserted to the left of the column that I had selected. So the column that I had selected actually moves to the right. And I’ve got one column inserted because I had one column selected. If I selected two columns, let me select F and G. So just to do that if you select F by clicking on the header, drag across with the mouse with the mouse button still down and then release you’ve got two columns selected. If I now right click and do Insert I get two columns inserted. Not they’re empty but I’ve got two columns inserted.
Now inserting rows pretty much works the same way. One very important point to note though, I’ll just mention this now but just something to be aware of. I’ve selected row 8 and I’m going to do an Insert. This time I’m going to do the insert using the Insert button in the Cells Group on the Home Tab. I’m going to click on Insert Sheet Rows and I get a newly inserted row above the row that I had selected and that newly inserted row is itself selected. So that’s fine. If I had selected two rows I would have got two rows above, etcetera.
But now let me select row 12. Now look carefully at row 12. You see that it’s got some formatting itself, a bit of that sort of mustardy colored background shading and it’s got some shading above it and below it. Let’s select row 12 and let’s do an insert again. Now once again when I do this I’m going to get a row above the one that I had selected and that new row will itself be selected, but also the newly inserted row has some formatting. It inherits some formatting from the row above the one that was selected when I did the insert. Now this may all seem rather baffling to begin with but once you’ve been doing this for a little while you’ll get used to the fact that when you do do inserts, particularly inserts of whole rows and whole columns, there are situations where you can inherit some of the formatting surrounding the selected row or column. So it’s something to be aware of. I’m not going to linger on it now. You’ll soon get the hang of dealing with it when it occurs, if it happens in a way that you don’t want. But you don’t get, necessarily, a completely empty row or column when you do an insert. You may get some formatting. You won’t get any content as in numbers and letters and so on but you might get some formatting. It’s just something to be aware of.
Want More? Get Started With a Free Excel 2016 Course! Click Here
Let’s now look at doing deletions. Let’s delete that row 12 that I entered. So all I need to do is select the row. If I look at the Contextual Menu I’ve got a Delete option. On the Delete button in the Cells Group on the Ribbon I’ve got Delete Sheet Row. So let me use Delete Sheet Rows on this occasion. That row is deleted.
Next I’m going to delete several columns. Now first of all I can select a whole range but supposing I want to delete two columns and they are not next to each other. So they’re not adjacent columns. Say I wanted to delete columns K and N. If I select column K by clicking on its header, hold the Control key down and select column N I’ve got two non-adjacent columns selected. I can do that of course with any number of non-adjacent rows or columns. Having done that if I go back to the Delete button in the Cells Group on the Ribbon the Delete Sheet Rows option has become a Delete Sheet Columns option. So these options are context sensitive. So let’s do Delete Sheet Columns. Those two are now deleted.
But I can of course delete a whole range of columns quite easily. So to delete columns B to L if I click on the header of column B, keep the mouse button down, drag across to column L, so I’ve got that whole selection made, right click, click on Delete, and all of those columns are now deleted. And I’m back to having something that’s a little bit closer to my expenses.
Now you can see some other things that I need to delete but before I do I want to talk to you about Clear because something Clear can be a better option than Delete.
Let’s look at these cells here from B20 through to E20. They have formatting and they have content. So for instance, the cell B20 has got the number 73.5 in it which because the cell is formatted in Accounting Format its got a dollar on the left hand side and 73.50 on the right and it’s got that sort of mustardy color background and that’s part of the Fill Color of the cell. Now I could of course just select row 20 and delete it because I don’t actually need that but sometimes you may want to note delete everything in a cell or a row or a column. You may just want to delete the formatting or perhaps delete just the content and that’s where Clear comes in.
Now with row 20 selected if you look at the Home Tab and the Editing Group there is a Clear button with a dropdown. And on the dropdown you have a number of options. You have Clear All which basically clears everything, Clear Formats, Clear Contents, Clear Comments, we haven’t talked about Comments yet but you can put comments in a cell, and you can also put Hyperlinks in a cell and you can clear those with the Clear Hyperlinks option.
Let’s first of all try Clear Formats. Now if I click on Clear Formats I’ve removed both the Fill Color and the Accounting Format, so the number formatting, and all I’ve got left are the actual numbers in those cells. Let me Undo that change and instead of Clear Formats let’s do Clear Contents. Now if I do Clear Contents the contents themselves, the numbers have gone but the number formatting, in this case Accounting Format, and the Color Formatting, the fill formatting, etcetera, that is all still there. So if I clicked in cell B20 and typed the number 13.4 and click, it’s $13.40, still Accounting Format. So when I did the clear I lost the actual numbers in there but I kept all of the formatting.
So sometimes Clear is a good option. Let me on this occasion though select row 20 and say Clear All and to all intents and purposes that does the same as a delete. It’s not quite as simple as that because the delete would have moved everything underneath up one whereas the clear just clears the content without actually deleting the row or column.
Okay I’ve got two more columns to delete. That’s D and E. So now I’m back to having just the information that I need. There’s quite a lot of work to do on the formatting and there’s quite a lot of work to do on adding some additional information. But for the moment let’s just do a final bit of tidying up.
Let me take the cells from B10 to B16 and what I’m going to do there is to say Clear Formatting. So I’m back to just the numbers. And then what I need to do with those is to apply a consistent number format. In this case what I really want is that Date Format and I’m going to put that Long Date Format back again. And over here, D10 to D16 I want to just clear the formatting. Really I only want to clear the coloring, if you like. Now I could do that by changing the Style. I’ve not talked about Style so far. I’ll be coming back to that a little bit later on. For the moment let’s just do exactly this equivalent of what we’ve just done on column B. We’ll say Clear Formats and then what I’m going to do is apply, I’ll think I’ll put Currency Format on there now.
Want More? Get Started With a Free Excel 2016 Course! Click Here
Okay I have a few too many rows above here. Let me get rid of most of those. And then I have one more duplicate row to get rid of there. Select that, delete that, and I’m going to leave this expenses, Business Expenses 03 sheet like this for the moment and carry on working on it in the next section. I’ll see you then.
Simon Calder
Chris “Simon” Calder was working as a Project Manager in IT for one of Los Angeles’ most prestigious cultural institutions, LACMA.He taught himself to use Microsoft Project from a giant textbook and hated every moment of it. Online learning was in its infancy then, but he spotted an opportunity and made an online MS Project course - the rest, as they say, is history!
