Join Dennis Taylor for an in-depth discussion in this video Inserting and deleting rows and columns, part of Excel 2016 Essential Training.
- There many different situations when you're working with Excel where you need to change the layout of a worksheet. Maybe you want to put it in a new column or a new row or in some cases, delete a column or a row. You can do that relatively easy and quickly. You can also at different times simply add cells, push data down. You can delete cells. We can do this across rows or across columns. In this particular worksheet called Insert-Delete. It's in our file called 05 - Layout. Maybe we've decided that we need to keep track of people by their status.
In other words, maybe some of these people are part time, and we don't see that in the list here. And we want a new column to the left of Column D. Now if you didn't know how to insert a new column, you might begin by clicking the column, but then what? On the Home tab, there's a choice called Insert, and that's certainly helpful. We could Insert Sheet Sheet Columns. We could click that. And nothing wrong with that, and I'm certainly not saying that's not the best way to do it. But I think it's handier much of the time to use the right mouse button.
Let me use ctrl + z here. I'm going to right-click on Column D. And there's Insert. Now, it doesn't say columns. So if you're relatively new with Excel, Insert sometimes doesn't quite say what it means. In this case, it doesn't. But it does mean new column to the left. We can get there that way. Now after thinking about it for a bit, maybe making some other changes, you've decided, "I just can't get to that yet. "I don't need that empty column." Maybe you've done other actions in the meantime, so you can't do an undo. How do we get rid of this? We can use the same feature.
Other words, we don't have to go elsewhere. I'm going to right-click Column D and Delete. Now here's another thought. We want to add a new record here. We could add it at the bottom, although maybe this person is in Account Management. We want to put it in the list near that group. Maybe we want to add it above Row 90 here. We could do that if we wanted to, or maybe we want to add it in place. Maybe the person's name is Abbott, and we want to add it above Row 2, something like that. It's not always the best way to do it. If I right-click Row 2, I can choose Insert.
It puts in a new row, and I could fill in the data. Notice how it copied the color down. I might want to get rid of that. But as I scroll to the right, would that have been a good idea? Looks like I've split other data now. It's up to you to know what's on your worksheet. And although this is not a horrible result here, I think you can sense what might happen if we do this with certain other rows. I'm going to press ctrl + z to undo that last action. What if I were not thinking clearly, and I went to insert a new row above Row 6? Right-click, Insert.
I could fill in my data here. Nothing wrong with that part of it. But scrolling to the right, I've introduced a gaping hole in this table. Maybe I'm going to use that later for lookup purposes. And then, the list over here obviously is not what we want it to be. So there will be times where instead of inserting a row, you might want to insert cells. I'm going to press ctrl + z again. So if we wanted to add new data here, we would highlight just this data. Right-click, Insert. Notice now the option has ... behind it meaning there's more to come.
We're going to see a dialog box. Shift cells down would be the option we want here. Now this is not going to touch data to the right. Keep an eye on Column L. As I click OK, it will shift all these cells from Row 6 downward. We're not losing anything here, but now we can fill in the data. The data to the right has not been disturbed at all. So we could start adding our new data here. Now once again, I'm reconsidering and don't have the time to do this, so I'll press ctrl + z. And similarly you could imagine the same situation.
You're looking at this data here. Maybe you want to add Pennsylvania to the list, and you want to put above IL, so you could highlight these cells here. Right-click and Insert, Shift these cells down. Put in your Pennsylvania data. We have not disrupted anything to the left. Now another possibility here, and you want to be a little bit careful with this of course. Let's say that we say for example, Danielle has left the list here. Let's get rid of Danielle's record. If we delete the row, you can imagine what's going to be happening.
Now you don't always see that data to the right. Again, that's a reminder. Be sure you know what you're doing here, be looking ahead. If we're not seeing the data to the right, well that's an oversight on our parts. And if we delete Row 5 here, we will be deleting all of Row 5 including the data over here that's currently highlighted. So in this case, if we simply wanted to get rid of this information here, the information for Danielle Atkinson, highlight these cells, right-click, and Delete. Shift cells up. We want the cells below to be shifted up.
The information for Danielle Atkinson will disappear, and it's gone. Nothing to the right got disrupted. Always be alert to that concept. We can insert and delete columns or rows. We can insert and delete cells. And of course, it makes a difference at different times. The other concern here and one that if you've worked with Excel, you don't worry about too much anymore is that Excel for the most part automatically adjusts formulas as we insert and delete. You might or might not be familiar with the kind of formula that's in Column E here, but this calculates years of service, in this case, based on what's in D2.
And all the formulas below, of course, deal with Column D. If I want a new column, I suggested this earlier with the status column, if I right-click Column D and Insert, and we're eventually going to put in data here let's say, what happened to the formula in Column F? Instead of referring to D2. It now refers to E2. And nearly always when you insert rows and columns, you don't worry about your formulas. Excel automatically adjusts them, and that's comforting because that would take a lot of work to adjust otherwise in some cases.
I'm going to delete this. And sure enough the reference is now back to D2 as you would hope and want it to be. So lots of situations here where we insert and delete, and I strongly recommend using the right mouse button. Again, nothing wrong with those commands on the Home tab, but it's a lot easier when you use the right mouse button to insert and delete columns and rows or in some cases simply cells.
- Working with the Excel interface
- Entering data
- Creating formulas and functions
- Formatting rows, columns, cells, and data
- Working with alignment and text wrap
- Adjusting rows and columns
- Finding and replacing data
- Printing and sharing worksheets
- Creating charts and PivotTables
- Inserting and deleting sheets
- Using power functions such as IF and VLOOKUP
- Password-protecting worksheets and workbooks
- Sorting data
- Analyzing data with Goal Seek and Solver
- Creating and running macros