Last Updated on April 25, 2024

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.

About The Author

Patty is a lead applications trainer for ONLC Training Centers and has worked for the company since 1988. She is technically proficient in all programs and all levels of Microsoft Office, Excel BI Tools, and is certified as a Microsoft Office Specialist (MOS). Patty has developed custom courseware, worked as a deskside support specialist and has been involved as a project manager for enterprise-wide Microsoft Office corporate migrations. She is also a trainer and consultant for Microsoft Project and Project Management Concepts. Prior to joining ONLC, Patty worked as a software support manager, developer and instructor.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>