When you bring data from another source into an Excel worksheet, the data often includes rows that you’ll want to delete. Here's the first of two methods.
When you bring data from another source into an Excel worksheet, the data often includes rows that you’ll want to delete. Often, you’ll want to delete blank rows. At other times, you’ll want to delete rows with irrelevant data. I frequently use two quick and easy methods that solve this problem nearly every time.
For both methods, you’ll rely on one column in your data, a column with characteristicsthat apply to the other columns. Let’s call this thecontrol column.
The first method works when you want to remove empty rows from your data. To begin, chose a column of data that has empty cells in the same rows where the other columns of data also have empty cells. This will be your control column.
Select the Entire List
The first step is to select the entire control column. If the column is long and your spreadsheet has other data in it, it’s often easier to select the bottom of your column first, and then extend your selection to select the top of your column. Here are some keyboard shortcuts that will help you to do this easily:
- Ctrl+End — Select the bottom-right cell in your spreadsheet.
- Ctrl+Home — Select the top-top left cell in your spreadsheet.
- Home — Select the left-most cell in your spreadsheet in the active row.
- End, Enter — Select the right-most cell in the used range in the current row of your spreadsheet.
- Ctrl+Arrow Key — Move the active cell in the direction of the arrow key to the next cell that contains data and that borders an empty cell
- Alt+PageDown — Move one page to the right.
- Alt+PageUp — Move one page to the left.
If you hold down the Shift key with any of these combinations, you’ll extend the selection.
Also, for getting around a multi-cell selection:
- Ctrl+Backspace — Display the active cell.
- Ctrl+Period — Move clockwise to the next corner of a multi-cell selection.
So, to go to the bottom-right cell of your data, press Ctrl+End. You might need to press Home to move all the way to the left, or press Alt+PageUp to page to the left until you get to the bottom cell of your control column.
Once you select the bottom cell of the column, hold down the Shift key and then move to the top cell with data in your control column. If the column of data is short, press PageUp; if the column is long, it probably would be easier to press Ctrl+Home (with the Shift key still down) and then click on the top cell of the control column with your mouse.
One way or the other, select the entire control column.
Delete the Blank Rows in Your Data
Delete the Blank Rows in Your DataWith the one column selected, first press the F5 function key to launch the Go To dialog.
In the Go To dialog, choose Special to launch the Go To Special dialog, shown here. In the Go To Special dialog, select Blanks and then choose OK. After you do so, Excel will select every blank cell in the selected range.
The final step is to delete every row in which a blank cell was selected. To do so, hold down your Ctrl key and the press the “-” (minus) key on your keyboard. Excel displays the Delete dialog. Select the Entire Row option, as shown, and then choose OK. After you do so, Excel deletes the empty rows.
Be sure to check your data at this point. If something has gone wrong you can press Ctrl+Z to undo the Delete command.
InHow to Delete Blank or Unneeded Rows in Your Worksheet, Method 2, I discuss the other method I use frequently to delete unneeded rows of data.
