Sign in or start a free trial to avail of this feature.
7. 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.
To explore more Kubicle data literacy subjects, please refer to our full library.
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 a row with shift + space bar, and then we'll press alt + I, and then R to insert a row.
This creates a row above the selected row. Let's try that again by moving to a new row. We'll select it with shift + space bar, and then we'll insert with alt+ I, and then R for row.
To insert a column, we'll simply select a column with control + space bar, 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 is 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 control + 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 a blank row with the shift + space bar, and then press control + minus to delete. I'll do that one more time by navigating to a blank row, selecting it, and pressing control + minus to delete.
Deleting columns is actually the exact same command. So let's select a column with control + space bar, and then press control + minus to delete. We'll do that one more time.
Control + minus 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 control + down arrow. Let's select the bottom row with shift + space bar, and then holding shift, use the up arrow to select the remaining rows.
Then press control + X to cut these cells, and move the cursor to the top left-hand corner of where you'd like to paste.
Then press control + 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 control + X, and then navigating up to the top row.
I'll then paste with control + V, and unfortunately this doesn't work. It actually overrides the first four rows, not exactly what we want. So let's undo with control + 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 control + V. To finish up this lesson, I'm going to summarize most of the commands we used 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 dataset. So I'll select with control + space bar, and then I'll press control + minus to delete. I'll press control + minus again, and one more time.
Let's now scroll up to the top, and I'll remove this blank row. Shift + space bar to select, and then control + minus, and control + minus one more time.
I'll now remove the blank row between 12 and 13.
Select it with shift + space bar, and control + minus 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 + space bar, and then shift + down arrow. Let's cut with control + X. Scroll down to the bottom with control + down arrow, and then right mouse button E, and press enter to insert.
Now we have a clean dataset, 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.