Inserting and Deleting Cells, Columns, and Rows

Inserting & Deleting Cells, Rows, and Columns

Even the most well-planned worksheets will require you to add a column here, remove a row there, or add a cell or two to make room for or move something you didn’t have in mind when the worksheet was created.

Luckily, these additions and deletions are easy to do, using buttons found right on the Home tab, or from a quick pop-up menu when you right-click the area that needs a little restructuring.

First, let’s look at adding cells, rows, and columns.

To add a cell, click on the cell that should end up below the cell you’re adding, and click the Insert button on the ribbon. Here, I want to put a cell between the last item in the Quantity on Hand column and the total number of items in inventory.

Excel automatically moves the cell with the formula in it down, inserting a new cell above it in cell F29.

Note that Excel is not actually adding a new cell to the worksheet, the total number of cells in the sheet has not increased – it’s simply shifting the cell you selected down so that an empty cell now precedes it. It’s kind of like moving the cell’s contents, but without having to drag it manually or deal with surrounding content that’s in the way.

Now, sometimes this doesn’t work exactly as you’d hoped. If I select the empty cell and the cell that contains the formula and click the Insert button, Excel guesses – perhaps not correctly – that I wanted to add cells to the left of the selection, so it’s essentially shoved my formula over to the right. Excel’s guesses are made based on your selected cell or cells and the surrounding content.

If that’s not what I wanted to do – and it wasn’t – I can select the empty cells and click the Delete button, right next to the Insert button, on the Home tab.

And the formula is back in cell F30, where the first insertion placed it.

To have more control over where the inserted cell or cells appear, you need to click the bottom half of the Insert button, displaying a dialog box where you can choose where the inserted cell or cells end up. Here, I’ll select the same two cells and then click the bottom half of the Insert button – and choose Insert Cells.

This gives me a dialog box where I can choose to Shift Cells Right or Shift Cells Down (we’ll look at inserting entire rows and columns in a minute). I’ll choose Shift Cells Down.

And two cells are inserted above the selected cells, depositing my formula in cell F32.

So now let’s look at rows. To add a row, select the row – the entire row, by click the row number – that should be moved down to make room for a new one. Then click the Insert button, again on the Home tab.

You can also right-click the row and choose Insert from the pop-up menu.

If you want to insert more than one row, select the number of rows you want to insert, and then click the Insert button, or again, you can right-click to get the pop-up menu. I can select, for example, these three rows from the top of the inventory list and click the Insert button and now I have 3 rows above the first item in the inventory list.

Deletion is equally simple – select the rows you want to get rid of, and click the Delete button – so I can get rid of the three rows I just created. Remember, to select non-contiguous rows, columns, or cells, hold down the CTRL key as you make the selections.

Bear in mind, Excel will delete the rows even if they contain data so be careful.

Moving on to columns, adding and deleting columns is equally simple.

Select the column that should be on the right of the new column, and click the Insert button. Here, I’ll add a column to become the Vendor column to the Inventory list – and it will become Column E, moving the Unit Price column – and all the columns after it – over one.

If I change my mind? Select that new column and click the Delete button on the Home tab. Note that the Delete button on the keyboard will only remove content from the selection, not remove the selection itself, so that’s not an equivalent method.

NOW, as we saw earlier, clicking the bottom half of the Insert button displays a dialog box. Let’s look at how that works.

As you may know, we can tell when a dialog box will appear if we see an ellipsis (three little dots) after the command in a menu – so if we don’t see that, the command will just happen, no questions asked.

But when we have a cell or cells – not a whole row or column – selected, the Insert Cells command has an ellipsis after it in the Insert button menu.

And with a cell or cells selected, the Delete button’s menu also includes a command with an ellipsis, Delete Cells.

Each command opens a dialog box, asking how the insertion or deletion should affect the selected cells. After you choose your preference, the action takes place, according to your choice.

And now let me wrap up with a tip. There’s one thing that the Insert button does that takes users by surprise. If you’ve made a selection and copied it to the clipboard – using the Copy button on the home tab or pressing Ctrl + C – using the Insert button (not its menu, the main button) will paste that content.

How do you avoid this? If you’re making room for copied content and that’s why you’re using the Insert button, insert the requisite space before you copy the content that will go in the newly opened-up cells, rows, or columns.

Then you can copy the content, then click in the new cells, column, or row that it should occupy, and issue the Paste command - which you can do via the Home tab’s Paste button, by right-clicking the new location and choosing Paste from the pop-up menu, or by pressing Ctrl + V.