Sign in or start a free trial to avail of this feature.
6. Inserting Rows and Columns
The safest way to insert, move and delete Excel data is using rows and columns - which is what I'll be demonstrating in this lesson.
Inserting Rows and Columns (00:03)
To insert a row, use the keyboard shortcut Alt, I, R. This adds a new row above the currently selected row. To insert a column, press Alt, I, C. This adds a column to the left of the currently selected one. If you want to insert multiple rows or columns, you can use the shortcut F4, which repeats the last action you took.
Deleting Rows and Columns (02:05)
To delete a row or column, you first select the full row or column. You then use Ctrl and the minus key (Ctrl + -). This works for both rows and columns.
Moving Rows and Columns (02:40)
When you want to move rows or columns, one option is to cut the rows from their existing locations, and paste where you want to put them. However, this approach overwrites any existing content in the destination cells. As a result, you should generally avoid it.
Instead, you should use the Insert Cut Cells command instead. This inserts the data without overwriting existing data. The keyboard shortcut for this is Right Mouse Button Key, E, Enter. The right mouse button key is found on the bottom row of most keyboards. If your keyboard doesn’t have this key, pressing Shift + F10 achieves the same result.
In the previous lesson we learned how to select cells using the keyboard. In this lesson, we'll learn how to add new rows and columns to an Excel sheet. Inserting and editing rows and columns are some of the most common commands you'll perform in Excel. So as you can probably guess, we'll be using a number of keyboard shortcuts to perform these tasks. To insert a row in Excel, let's first select row with Shift + Spacebar and then we'll press Alt + I and then R, to insert a row. This creates a row above the selected row. Lets try that again by moving to a new row, we'll select it with Shift + Spacebar and then we'll insert with Alt + I and the R for row.
To insert a column we'll simply select a column with Ctrl + Spacebar and then we'll press Alt + I, C to insert a column to the right of the selected column. Let's insert another column by repeating that command. So it's Alt + I, C if you need to insert many rows and columns there's a very helpful command called F4 which simply repeats the previous action you've made. So given that the previous action I've made has been Alt + I, C and I press F4, it generates a new column. If I continue to press F4, it repeats the Alt + I, C command and generates new columns.
I'll just undo these new columns quickly by pressing Ctrl + Z.
The F4 button will repeat any command in Excel but it's particularly useful when inserting multiple rows and columns. You might be wondering why I'm focusing so much on inserting rows and columns and not inserting cells. Well it turns out that inserting rows and columns is the safest way of adding new data to a spreadsheet. Adding individual cells or even groups of cells can be very dangerous and create errors in your datasets or models. So to be on the safe side, always add rows or columns to spreadsheets, not cells. We now have quite a few blank rows in our spreadsheet so let's remove them. Let's first select blank row with Shift + Spacebar and then we press Ctrl + - to delete. I'll do that one more time by navigating to a blank row, selecting it and then pressing Ctrl + - to delete.
Deleting columns is actually the exact same command. So let's select a column with Ctrl + Spacebar, and then we press Ctrl + - to delete. Do that one more time.
Ctrl + - to delete the column. Now that we know how to insert and delete rows and columns, let's figure out how to move them. Let's say I want to move the bottom four rows down three additional rows. Let's navigate down to them using Ctrl + Down Arrow. Let's select the bottom row with Shift + Spacebar, and then holding Shift, use the Up Arrow to select the remaining rows.
Then press Ctrl + X to cut these cells, and move the cursor to the top left-hand corner of where you'd like to paste.
Then press Ctrl + V to paste, and the cells are moved down three rows.
Let's now try something a little more challenging. Let's move the bottom four rows up to the very top, above row one. We'll start by cutting this content with Ctrl + X and then navigating up to the top row.
I'll then paste with Ctrl + V, and unfortunately this doesn't work. It actually overrides the first four rows. Not exactly what we want. So let's undo with Ctrl + Z, Let's undo with Ctrl + Z, we use right mouse button E, press Enter, and this inserts the four rows above the first row, which is exactly what we want.
When I say right mouse button, I don't actually press the right side of the mouse. I press this key on the keyboard, normally located on the bottom row, which serves the exact same function as a right mouse button click. If your keyboard doesn't have this key, you can also use Shift + F10 to perform a right mouse button click. Hopefully the last example showed you how moving rows of data can be tricky, and how easy it is to lose data when completing this task. To be on the safe side, be sure to use the right mouse button, and E when moving cells, not pasting with Ctrl + V. To finish up this lesson, I'm going to summarize most of the commands we learned today, by removing all the blank rows and columns in this dataset, and rearranging the rows in the correct order. Let's start by removing the columns to the left of the data set. So I'll select with Ctrl + Spacebar, and then I'll press Ctrl + - to delete. I'll press Ctrl + - again, and one more time.
Let's now scroll up to the top, and I'll remove this blank row. Shift + Spacebar to select, and then Ctrl + -, and Ctrl + - one more time.
I'll now remove the blank row between 12 and 13. Select it with Shift + Spacebar, and Ctrl + - to delete.
And to finish up, let's move the bottom four rows back to their original position. Let's scroll up to the top, let's select them, Shift + Spacebar, and then Shift + Down Arrow.
Let's cut with Ctrl + X, scroll down to the bottom with Ctrl + Down Arrow and then right mouse button E, and press Enter to insert.
Now we have a clean data set devoid of blank rows and columns, with all of the rows in the correct order.
As an exercise, try to clean up the dataset in the "Before" file below this video, as I have done here. Also, try to stick with the keyboard shortcuts as much as possible, however tempting it may be to use the mouse.