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.
Useful keyboard shortcuts: inserting rows and columns
ALT + I, R: Insert a row above selected row
ALT+ I, C: Insert a column to the right of the selected column
F4: Repeats previous command
Adding data to a spreadsheet
- Always add rows and columns to datasets, not individual or groups of cells
- This will drastically reduce the potential for errors in your dataset
Useful keyboard shortcuts: deleting rows and columns
SHIFT + Spacebar: Select row
CTRL + Spacebar: Select column
CTRL + -: Delete selected row or column
Moving rows of cells
- Be careful of cut and paste because this can result in overwriting existing data
- Instead use Right-mouse button key + E, which will insert the cut cells above selected cells
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 Ctrl + 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 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 data sets 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 on our spreadsheet, so let's remove them. Let's first select a blank row with Shift + Space Bar and then we'll 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 + Space Bar, and then we'll press Ctrl + - to delete.
and 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 + Space Bar, 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 1. 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 overwrites the first four rows. Not exactly what we want. So let's undo with Ctrl + Z, and instead we'll use a different command. We'll 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 data set 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 + Space Bar and then I'll press Ctrl + - to delete. I will press Ctrl + - 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 Ctrl + - , and Ctrl + - one more time. I'll now remove the blank row between 12 and 13, select it with Shift + Space Bar, 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 + Space Bar 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 data set 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.