One of the most common tasks that many Excel users do on a daily basis is to delete multiple rows of data.
While deleting one single row is quite straightforward, deleting multiple rows at once can be time-consuming and tedious.
But the good news is that you don’t have to.
There are numerous ways in Excel to delete multiple rows quickly at once.
In this tutorial, I will show you how to delete multiple rows at once in Excel.
Delete Multiple Adjacent Rows in Excel
At times, it’s necessary to remove several rows that are next to one another.
The table below contains a company’s customer list. The customer’s active or inactive status is indicated in column A.
In the above dataset, I want to delete all the rows where the status is inactive in column A.
All of the inactive customers are in adjacent rows in this case. To remove all of these adjacent rows at once in Excel, we have three methods.
Method 1 – Delete Multiple Adjacent Rows Using the Excel shortcut
The keyboard shortcut is the simplest way to delete multiple rows in Excel.
To do so, we can follow the steps outlined below.
- Choose the row headers of inactive customers. Select the first row, then hold the shift key on your keyboard and then select the last row. This is going to select all the rows between the first and the last selected rows
- Use the Control + Minus (-) keyboard shortcut. Hold the control key and then press the minus key.
The selected rows will be deleted from Excel.
In the above example, I selected the entire rows that I wanted to delete. You can also use the keyboard shortcut to delete rows by selecting the cells in these rows.
Let’s say that I have selected the cells instead of row headers as in the below image.
Now if I use the shortcut Control + Minus (-), it will show me the Delete dialog box. Now, to delete the rows, select the “Entire row” option and click the “OK” button.
If we have selected cells instead of row headers, we can adjust the keyboard shortcut as follows.
Control + Minus (-) + R + Enter
If you have data in the cells in those rows (outside of our dataset), deleting the entire row would also delete the data. Therefore, we have to select the shift cells up option using the below shortcut – Control + Minus (-) + L + Enter
Also read: How to Delete Hidden Rows or Columns in Excel?
Method 2 – Delete Multiple Adjacent Rows Using the Context Menu
To delete multiple nearby rows using the context menu, follow the steps below.
- Select row headers of inactive customers.
- Right-click to open the context menu.
- Click on the ‘Delete’ option.
This will delete the selected rows.
Also read: How to Delete Filtered Rows in Excel (with and without VBA)
Method 3 – Delete Multiple Adjacent Rows Using the Home Tab
Another way to quickly delete rows is by using the delete option in the Home tab in the ribbon.
Below are the steps to do this:
- Select row headers of inactive customers (which are the rows that we want to delete).
- Go to the Home tab.
- Expand the “Delete” drop-down list from the “Cells” group.
- Click on the ‘Delete Sheet Rows’ option.
This will delete the selected rows instantly.
We can directly do the above steps of method 3 after selecting the rows using the below keyboard shortcut – ALT + H + D + R
Also read: Delete Cell in Excel (Shortcut)
Delete Multiple Non-Adjacent Rows in Excel
Occasionally we need to delete many rows that are always next to each other (we call them the non-adjacent rows).
A customer list for a company is shown in the table below. Column A displays the customer’s active or inactive status.
Now I want to delete all the rows of inactive customers.
In this case, there are 4 inactive customers in non-adjacent rows.
There are three ways to quickly delete several non-adjacent rows in Excel.
Method 1 – Delete Multiple Non-Adjacent Rows Using the Excel shortcut
Using the keyboard shortcut is the most convenient way to delete multiple rows in Excel.
To do that, we can follow the steps listed below.
- Choose the row headers of inactive customers. We need to hold down the “Ctrl” key when selecting the header rows.
- Use the Control + Minus (-) keyboard shortcut. We have to press both keys together.
The selected rows in Excel will be deleted.
One drawback of this method is that it is manual. So while you can think of doing it when you have a small dataset, it would be time-consuming and prone to error when you have a large dataset.
In the above example, we selected the entire row, but you can do the same thing even if you select one or multiple cells in the row that you want to delete.
Assume we chose cells instead of row headers, as shown in the image below.
In such a situation, when we press the Control + Minus (-) keys, the Delete dialog box will be opened.
To delete the rows, we have to select the “Entire row” option and press “OK”.
If we have selected cells instead of row headers, we can change the keyboard shortcut as follows.
Control + Minus (-) + R + Enter
Pro Tip: There is another way to choose only inactive customers from the list. First, set filters to only show inactive customers. Then, select the first row. Then, while holding down the Shift key, choose the last row. Excel will select all filtered rows using this method.
Also read: How to Select Non-adjacent Cells in Excel?
Method 2 – Delete Multiple Non-Adjacent Rows Using the Context Menu
If we wish to delete multiple non-adjacent rows using the context menu, we can do as follows:
- Select row headers of inactive customers. We have to hold the “Control” key while selecting the header rows.
- Right-click to open the context menu.
- Click on the ‘Delete’ option.
This will delete the selected rows.
Also read: Can’t Insert a Row in Excel – How to Fix!
Method 3 – Delete Multiple Non-Adjacent Rows Using the Home Tab
To delete several non-adjacent rows using the Home tab, we have to follow the steps listed below.
- Select row headers of inactive customers. We have to hold the “Control” key while selecting the header rows.
- Go to the Home tab.
- Expand the “Delete” drop-down list from the “Cells” group.
- Click on the ‘Delete Sheet Rows’ option.
This will delete the selected rows.
We can directly do the above steps of method 3 after selecting the rows using the below keyboard shortcut - ALT + H + D + R
Also read: How to Swap Columns in Excel?
Delete Multiple Visible Rows in Excel
Sometimes we need to delete multiple rows which are visible.
The below table contains a customer list of a company. Column A shows the status of the customer as active or inactive.
Let’s say we have manually hidden row number 9 as follows.
Now I need to delete all the visible rows of inactive customers.
We have three methods to delete these multiple adjacent rows in Excel at once.
Method 1 – Delete Multiple Adjacent Rows Using the Excel shortcut
The easiest way to delete multiple visible rows in Excel is to use the keyboard shortcut. We can follow the below steps to do that.
- First, we select the first row and then select the last row with the Shift key. Then, Excel will select all the rows between the first and last rows.
- Press the “ALT + Semicolon (;)” keys together to select only the visible cells. For some PCs, the Right side ALT key is not working for the shortcut. In that case, we have to use the Left side ALT key.
- Use the Control + Minus (-) keyboard shortcut to delete the selected rows. We have to press both keys together.
Using the above steps, Excel will only delete the selected visible rows. The hidden row will not be deleted.
If we forget to select only the visible cells, Excel will delete all the rows from row number 8 to 11, including the hidden row.
Let’s say that we have selected the cells instead of row headers as in the below image.
In such a situation, when we press the Control + Minus (-) keys, the delete dialog box will be opened. To delete the entire visible rows, we have to select the “Entire row” option and then click on ‘OK’.
We can also modify the keyboard shortcut as follows if we have selected the visible cells instead of visible row headers.
Control + Minus (-) + R + Enter
Method 2 – Delete Multiple Visible Rows Using the Context Menu
If we are using the context menu to delete multiple visible rows, we have to follow the below steps.
- First, we select the first row and then select the last row with the Shift key. Then, Excel will select all the rows between the first and last rows.
- To select only the visible cells, press the “ALT + Semicolon (;)” keys together. The right-side ALT key does not function as a shortcut on some computers. We have to then use the Left Side ALT key in that situation.
- Right-click to open the context menu.
- Click on the Delete option.
Excel will delete the selected visible rows.
Method 3 – Delete Multiple Visible Rows Using the Home Tab
If we are using the Home tab to delete multiple visible rows, we have to follow the below steps.
- First, we select the first row and then select the last row with the Shift key. Then, Excel will select all the rows between the first and last rows.
- To select only the visible cells, simultaneously press the “ALT + Semicolon (;)” keys. The right-side ALT key for the shortcut does not function on some PCs. We need to then press the Left Side ALT key.
- Go to the Home tab.
- Expand the “Delete” drop-down list from the “Cells” group.
- Click on the ‘Delete Sheet Rows’ option.
Excel will delete the selected rows.
We can directly do the above steps of method 3 after selecting the visible rows using the below keyboard shortcut – ALT + H + D + R
How to Undo the Deleting of Rows?
Accidentally deleting rows in Excel can be frustrating, especially if you need to recover the data quickly.
Fortunately, Excel has a built-in feature that allows you to undo the deletion of multiple rows.
To undo the deletion, simply press the “Ctrl + Z” keyboard shortcut or click on the Undo button located in the Quick Access Toolbar.
This will restore the deleted rows to their original location and format.
You have now learned how to delete multiple adjacent rows, non-adjacent rows, and visible rows using the Excel shortcut, the context menu, and the Home tab.
Other Excel articles you may also like:
