How to Remove Rows in Excel Based on Criteria
The flexibility of Microsoft Excel has made it the perfect tool for entering, analyzing, transforming, and storing data. However, the more data entered, the greater risk there is of duplicate rows. These duplicate cells can arise from human mistakes or Excel errors in copying data from one worksheet into a larger workbook. In other cases, you may need to remove rows to update your worksheet after getting new data.
If you work with the program, you know that the need to extract duplicates in Excel arises often. A Microsoft Excel training certification will teach you all of the detailed ins and outs, but in this article, we will discuss the basics of how to remove rows in Excel based on criteria.
How to Remove Rows in Excel Based on Criteria
There are a few different ways to remove rows in Excel. They include:
Remove Entire Rows
The first option is to remove an entire row or rows from your worksheet. If you need to remove entire rows, the first step is to find and highlight the row(s) you want to be removed.
You can highlight an entire row by simply clicking on the letter of the row(s). Alternatively, use ‘CTRL (Cmd)’ or ‘Shift’ and ‘Arrow Key’ to select the rows you want to remove.
To remove all the rows you have highlighted, hold down the ‘CTRL+-’ (minus on the main keyboard) hotkeys. After highlighting the rows, simply press Delete in the home tab. Both of these options will remove all highlighted rows from your worksheet.
Remove Rows Based on Cell Value
To remove rows based on cell value, highlight the range in your worksheet where you want to remove rows. Next, open the ‘Find and Replace’ window by simultaneously holding down ‘CTRL’ and ‘F’.
In the ‘Find and Replace’ window, enter the cell value (for this example, we use ‘Cost’) into the ‘Find’ box. Click the ‘Find All’ button, and the ‘Find and Replace’ tool will display all the rows containing Cost.
Select all the rows (or ‘CTRL+A’ for everything) and close the ‘Find and Replace’ window. The selected rows will remain highlighted. Next, right-click the selected cells and select Delete from the pop-up window. Excel will remove only the rows you have selected.
With this option, you can filter for multiple values in Excel worksheets. You can also use this option for removing text as well as numbers.
Remove Rows in Excel by Cell Color
If you have a worksheet with different background colors, you can use it to delete rows. To do this, open the ‘Data’ tab in Excel.
Select the range of data you want to filter from and select ‘Filter’ in the ‘Data’ tab. Next, navigate to ‘Filter by Color’ and pick the color of the row(s) you want to be removed. Click ‘OK,’ and all the highlighted rows will be displayed on top.
In your worksheet, select the highlighted rows you want to remove, right-click on them, and click the ‘Delete Row’ option from the menu. Excel will remove all the rows based on the color you selected.
Remove Rows at the Right of a Worksheet
If the row you want to remove is at the right of your worksheet, open the ‘Format as table’ window by pressing ‘CTRL+T,’ or find it in the ‘Home’ tab. When you open ‘Format as table,’ a ‘Create Table’ dialog box will pop up for you to highlight the necessary range.
When your table has been formatted, simply use ‘CTRL+/-’ to remove the rows you have highlighted. With this option, Excel will keep data to the right of the removed rows intact.
Remove Rows Using Excel VBA Macro
Excel Visual Basic for Applications (VBA) is a tool used to automate Excel routines. First, you need to get the Excel VBA codes from one of many resources online.
Next, open your Excel worksheet, use the keyboard shortcut ‘ALT + F11’ to open the VBA module. In the pop-up window, click ‘Insert’ > ‘Module’ > ‘New Module.’ Copy and paste the VBA code for removing rows in the New Module.
Next, press ‘F5’ to run the code. After the process has been completed, the input box will appear. In this box, you can enter the value of the rows on the worksheet you want to delete. Enter the criteria you want to delete and click ‘OK.’
In a Nutshell
Different options exist to remove rows from Excel, and each is best suited for specific criteria. If you are working on a large worksheet, one or more of these options will be useful.
