{"id":3952,"date":"2023-02-03T20:23:20","date_gmt":"2023-02-03T20:23:20","guid":{"rendered":"https:\/\/www.bizinfograph.com\/resource\/?p=3952"},"modified":"2024-03-13T15:30:33","modified_gmt":"2024-03-13T15:30:33","slug":"how-to-delete-rows-based-on-cell-value-in-excel","status":"publish","type":"post","link":"https:\/\/www.bizinfograph.com\/resource\/how-to-delete-rows-based-on-cell-value-in-excel\/","title":{"rendered":"How to Delete Rows Based on Cell Value in Excel?"},"content":{"rendered":"<p>Delete Rows Based on Cell Value in Excel to maintain clean, relevant, and accurate datasets for your analyses. This efficient method helps streamline data management tasks, allowing you to focus on meaningful information that drives decision-making. By mastering this technique, you can automate the process of filtering out unnecessary or erroneous data, enhancing your productivity and ensuring your Excel projects remain organized and impactful. Embrace this functionality to optimize your data processing workflows and elevate the clarity and precision of your reports.<\/p>\n<p>I&#8217;ll demonstrate various methods for deleting rows in Excel based on a column value or a condition in this tutorial.<\/p>\n<p><strong>This Tutorial Covers:<\/strong><\/p>\n<ol>\n<li><a href=\"#using-a-value-or-condition,-filter-the-rows,-and-then-delete-them\"><strong>Using a value or condition, filter the rows, and then delete them<\/strong><\/a>\n<ul>\n<li>Delete Rows that contain a specific text<\/li>\n<li>Delete Rows Based on a Numeric Condition<\/li>\n<\/ul>\n<\/li>\n<li><a href=\"#after-sorting-the-dataset,-delete-the-rows\"><strong>After sorting the dataset, delete the rows<\/strong><\/a><\/li>\n<li><a href=\"#find-and-select-the-cells-based-on-cell-value,-then-delete-the-rows\"><strong>Find and select the cells based on cell value, then delete the rows<\/strong><\/a><\/li>\n<li><a href=\"#delete-every-row-that-has-a-blank-cell\"><strong>Delete every row that has a blank cell<\/strong><\/a><\/li>\n<li><a href=\"#delete-cell-based-on-conditional-formatting\"><strong>Delete cell based on conditional formatting<\/strong><\/a><\/li>\n<li><a href=\"#based-on-cell-value,-filter-and-delete-rows-(using-vba)\"><strong>Based on cell value, filter and delete rows (using VBA)<\/strong><\/a><\/li>\n<\/ol>\n<h2><strong id=\"using-a-value-or-condition,-filter-the-rows,-and-then-delete-them\">1. Using a value or condition, filter the rows, and then delete them<\/strong><\/h2>\n<p>Filtering these is one of the quickest ways to remove rows that have a particular value or satisfy a certain condition. You can remove all of these rows once you have the filtered data (while the remaining rows remain intact).<\/p>\n<p>With Excel&#8217;s filter, you can choose to filter based on a variety of factors (such as text, numbers, dates, and colors)<\/p>\n<p>Let&#8217;s look at two instances of filtering and deleting rows.<\/p>\n<ul>\n<li>\n<h3><strong>Delete Rows that contain a specific text<\/strong><\/h3>\n<\/li>\n<\/ul>\n<p>Let&#8217;s say you wish to remove every row in the data set below where the buyer is American Eagle (in Column C).<\/p>\n<p><img fetchpriority=\"high\" decoding=\"async\" class=\"aligncenter wp-image-3953 size-full\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Delete-rows-based-on-cell-value-1.png\" alt=\"Delete Rows Based on Cell Value in Excel\" width=\"390\" height=\"405\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Delete-rows-based-on-cell-value-1.png 390w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Delete-rows-based-on-cell-value-1-289x300.png 289w\" sizes=\"(max-width: 390px) 100vw, 390px\" \/><\/p>\n<p>Although you can choose to manually delete these rows in this small dataset, your datasets are likely to be very large and this won&#8217;t always be an option.<\/p>\n<p>Then you can delete all of these rows by filtering all the entries where American Eagle is the buyer (while keeping the other rows intact).<\/p>\n<h3><strong>The steps to delete rows based on the value (all records for American Eagle) are as follows<\/strong><\/h3>\n<p><strong>Step 1: <\/strong>If you wish to remove the rows from the data set, choose any cell.<\/p>\n<p><img decoding=\"async\" class=\"aligncenter wp-image-3954 size-full\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Delete-rows-based-on-cell-value-2.png\" alt=\"Delete Rows Based on Cell Value in Excel\" width=\"484\" height=\"164\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Delete-rows-based-on-cell-value-2.png 484w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Delete-rows-based-on-cell-value-2-300x102.png 300w\" sizes=\"(max-width: 484px) 100vw, 484px\" \/><\/p>\n<p><strong>Step 2:<\/strong> On the \u201cData\u201d tab, click. Choose the &#8220;Filter&#8221;\u00a0icon under the &#8220;Sort &amp; Filter&#8221; group. All of the dataset&#8217;s header cells will have filters applied as a result.<\/p>\n<p><img decoding=\"async\" class=\"aligncenter size-full wp-image-3955\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Delete-rows-based-on-cell-value-3.png\" alt=\"Delete rows based on cell value\" width=\"644\" height=\"100\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Delete-rows-based-on-cell-value-3.png 644w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Delete-rows-based-on-cell-value-3-300x47.png 300w\" sizes=\"(max-width: 644px) 100vw, 644px\" \/><\/p>\n<p><strong>Step 3:<\/strong> The Buyer header cell&#8217;s Filter icon should be clicked (this is a small downward-pointing triangle icon at the top-right of the cell)<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-3956 size-full\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Delete-rows-based-on-cell-value-4.png\" alt=\"Delete Rows Based on Cell Value in Excel\" width=\"403\" height=\"410\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Delete-rows-based-on-cell-value-4.png 403w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Delete-rows-based-on-cell-value-4-295x300.png 295w\" sizes=\"(max-width: 403px) 100vw, 403px\" \/><\/p>\n<p><strong>Step 4:<\/strong> Select the \u201cAmerican Eagle\u201d choice only and deselect everything else (a quick way to do this is by clicking on the Select All option and then clicking on the American Eagle option). By applying this filter to the dataset, you will only see records for buyers of \u201cAmerican Eagle\u201d. Select all the filtered records.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-3957\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Delete-rows-based-on-cell-value-5.png\" alt=\"Delete rows based on cell value\" width=\"388\" height=\"427\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Delete-rows-based-on-cell-value-5.png 388w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Delete-rows-based-on-cell-value-5-273x300.png 273w\" sizes=\"(max-width: 388px) 100vw, 388px\" \/><\/p>\n<p><strong>Step 5:<\/strong> Select &#8220;Delete Row&#8221; by selecting Right-click on any of the chosen cells.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-3958\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Delete-rows-based-on-cell-value-6.png\" alt=\"Delete rows based on cell value\" width=\"405\" height=\"247\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Delete-rows-based-on-cell-value-6.png 405w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Delete-rows-based-on-cell-value-6-300x183.png 300w\" sizes=\"(max-width: 405px) 100vw, 405px\" \/><\/p>\n<p><strong>Step 6:<\/strong> Click OK in the dialog box that appears. You won&#8217;t find any records in the dataset at this time.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-3959\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Delete-rows-based-on-cell-value-7.png\" alt=\"Delete rows based on cell value\" width=\"550\" height=\"152\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Delete-rows-based-on-cell-value-7.png 550w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Delete-rows-based-on-cell-value-7-300x83.png 300w\" sizes=\"(max-width: 550px) 100vw, 550px\" \/><\/p>\n<p><strong>Step 7:<\/strong> Select the \u201cFilter\u201d icon from the \u201cData\u201d tab. Apart from the deleted records, all records will be visible after the filter is removed.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-3960\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Delete-rows-based-on-cell-value-8.png\" alt=\"Delete rows based on cell value\" width=\"418\" height=\"314\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Delete-rows-based-on-cell-value-8.png 418w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Delete-rows-based-on-cell-value-8-300x225.png 300w\" sizes=\"(max-width: 418px) 100vw, 418px\" \/><\/p>\n<p>In the preceding phases, the data is first filtered based on a cell value (or other criteria like after\/before a date or greater\/less than a number). You simply remove the records after you have them.<\/p>\n<p>Some helpful shortcuts to be aware of to quicken the process:<\/p>\n<ul>\n<li>To apply or remove the filter, press Control + Shift + L.<\/li>\n<li>To erase the chosen cells or rows, press Control + &#8211; (while holding down the Control key, press the minus key).<\/li>\n<\/ul>\n<p>Keep in mind that everything you may have in other cells inside these rows will be lost if you delete a row. Making a copy of the data in another worksheet and deleting the rows there is one technique to get around this. Once finished, replace the original data with a copy of it.<\/p>\n<p>Or<\/p>\n<p>The techniques demonstrated later in this article can be used (using the Sort method or the Find All Method).<\/p>\n<ul>\n<li>\n<h3><strong>Delete Rows Based on a Numeric Condition<\/strong><\/h3>\n<\/li>\n<\/ul>\n<p>You can use a number condition (or a date condition) in the same way that I used the filter method to remove all the rows that had the text &#8220;American Eagle&#8221;.<\/p>\n<p>Consider the following dataset as an example. I want to remove all the rows where the sale value is less than 30,000 from it.<\/p>\n<h3><strong>The steps to delete rows based on the numeric condition are as follows<\/strong><\/h3>\n<p><strong>Step 1:<\/strong> Pick any data cell from the list.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-3961 size-full\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Delete-rows-based-on-cell-value-9.png\" alt=\"Delete Rows Based on Cell Value in Excel\" width=\"483\" height=\"162\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Delete-rows-based-on-cell-value-9.png 483w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Delete-rows-based-on-cell-value-9-300x101.png 300w\" sizes=\"(max-width: 483px) 100vw, 483px\" \/><\/p>\n<p><strong>Step 2:<\/strong> On the \u201cData\u201d tab, click. Choose the &#8220;Filter&#8221;\u00a0icon under the &#8220;Sort &amp; Filter&#8221; group. All of the dataset&#8217;s header cells will have filters applied as a result.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-3962\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Delete-rows-based-on-cell-value-10.png\" alt=\"Delete rows based on cell value\" width=\"640\" height=\"97\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Delete-rows-based-on-cell-value-10.png 640w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Delete-rows-based-on-cell-value-10-300x45.png 300w\" sizes=\"(max-width: 640px) 100vw, 640px\" \/><\/p>\n<p><strong>Step 3:<\/strong> In the Sales header cell, select the Filter icon (this is a small downward-pointing triangle icon at the top-right of the cell).<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-3963 size-full\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Delete-rows-based-on-cell-value-11.png\" alt=\"Delete Rows Based on Cell Value in Excel\" width=\"324\" height=\"333\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Delete-rows-based-on-cell-value-11.png 324w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Delete-rows-based-on-cell-value-11-292x300.png 292w\" sizes=\"(max-width: 324px) 100vw, 324px\" \/><\/p>\n<p><strong>Step 4:<\/strong> Place your cursor on the &#8220;Number Filters&#8221; menu item. This will display all of Excel&#8217;s number-related filter choices. Select the &#8220;Less than&#8221; option by clicking.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-3964\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Delete-rows-based-on-cell-value-12.png\" alt=\"Delete rows based on cell value\" width=\"468\" height=\"366\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Delete-rows-based-on-cell-value-12.png 468w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Delete-rows-based-on-cell-value-12-300x235.png 300w\" sizes=\"(max-width: 468px) 100vw, 468px\" \/><\/p>\n<p><strong>Step 5:<\/strong> Put &#8220;30000&#8221; in the field of the &#8216;Custom Autofilter&#8217; dialog box that appears. Select OK. With this filter, only records with sales values less 30,000 will be displayed.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-3965\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Delete-rows-based-on-cell-value-13.png\" alt=\"Delete rows based on cell value\" width=\"501\" height=\"205\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Delete-rows-based-on-cell-value-13.png 501w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Delete-rows-based-on-cell-value-13-300x123.png 300w\" sizes=\"(max-width: 501px) 100vw, 501px\" \/><\/p>\n<p><strong>Step 6:<\/strong> Choose every filtered record. \u201cDelete Row\u201d can be selected by doing a right-click on any cell.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-3966\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Delete-rows-based-on-cell-value-14.png\" alt=\"Delete rows based on cell value\" width=\"462\" height=\"220\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Delete-rows-based-on-cell-value-14.png 462w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Delete-rows-based-on-cell-value-14-300x143.png 300w\" sizes=\"(max-width: 462px) 100vw, 462px\" \/><\/p>\n<p><strong>Step 7:<\/strong> Click OK in the dialog box that appears. You won&#8217;t find any records in the dataset at this time.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-3967\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Delete-rows-based-on-cell-value-15.png\" alt=\"Delete rows based on cell value\" width=\"428\" height=\"143\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Delete-rows-based-on-cell-value-15.png 428w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Delete-rows-based-on-cell-value-15-300x100.png 300w\" sizes=\"(max-width: 428px) 100vw, 428px\" \/><\/p>\n<p><strong>Step 8:<\/strong> Select the \u201cFilter\u201d icon from the \u201cData\u201d tab. With the exception of the deleted records, all records will be visible after the filter is removed.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-3968\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Delete-rows-based-on-cell-value-16.png\" alt=\"Delete rows based on cell value\" width=\"420\" height=\"266\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Delete-rows-based-on-cell-value-16.png 420w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Delete-rows-based-on-cell-value-16-300x190.png 300w\" sizes=\"(max-width: 420px) 100vw, 420px\" \/><\/p>\n<p>In Excel, you may apply a variety of number filters, including less than\/greater than, equal\/does not equal, between, top 10, above, or below average, and more.<\/p>\n<p><strong>Note:<\/strong> Additional filters may be used. You might eliminate all the rows where the sales value is higher than 30000 but lower than 30000, for instance. You must apply two filter conditions in this situation. Having two filter criteria is possible using the &#8220;Custom Autofilter&#8221; dialog box (AND as well as OR).<\/p>\n<p>You may also filter the records depending on the date, just like you can with the number filters. For instance, by following the identical procedures as above, you can delete every record from the first quarter. Excel automatically displays pertinent filters when you are using Date filters (as shown below).<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-3969\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Delete-rows-based-on-cell-value-17.png\" alt=\"Delete rows based on cell value\" width=\"359\" height=\"452\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Delete-rows-based-on-cell-value-17.png 359w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Delete-rows-based-on-cell-value-17-238x300.png 238w\" sizes=\"(max-width: 359px) 100vw, 359px\" \/><\/p>\n<p>Filtering, while a terrific approach to swiftly remove rows based on a value or a condition, has one drawback: it eliminates the entire row. For instance, in the scenario below, all the data that is to the right of the filtered dataset would be deleted.<\/p>\n<p>What if I only want to remove records from the dataset while retaining all of the other data?<\/p>\n<p>Filtering does not allow you to accomplish that, but sorting does.<\/p>\n<h2><strong id=\"after-sorting-the-dataset,-delete-the-rows\">2. After sorting the dataset, delete the rows<\/strong><\/h2>\n<p>In most situations, you&#8217;re better off utilizing the filter method described above than sorting, which is another option to delete rows depending on value.<\/p>\n<p>Only when you only want to eliminate the cells with values and not the complete rows are you advised to use this sorting method.<\/p>\n<p>Imagine you want to remove every record where the country is Germany from the dataset as it is displayed below.<\/p>\n<h3><strong>How to accomplish this using sorting is detailed below<\/strong><\/h3>\n<p><strong>Step 1:<\/strong> Pick any data cell from the list.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-3970 size-full\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Delete-rows-based-on-cell-value-18.png\" alt=\"Delete Rows Based on Cell Value in Excel\" width=\"399\" height=\"353\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Delete-rows-based-on-cell-value-18.png 399w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Delete-rows-based-on-cell-value-18-300x265.png 300w\" sizes=\"(max-width: 399px) 100vw, 399px\" \/><\/p>\n<p><strong>Step 2:<\/strong> On the Data tab, click. Simply select the &#8220;Sort&#8221;\u00a0icon from the &#8220;Sort &amp; Filter&#8221;\u00a0group.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-3971\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Delete-rows-based-on-cell-value-19.png\" alt=\"Delete rows based on cell value\" width=\"574\" height=\"93\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Delete-rows-based-on-cell-value-19.png 574w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Delete-rows-based-on-cell-value-19-300x49.png 300w\" sizes=\"(max-width: 574px) 100vw, 574px\" \/><\/p>\n<p><strong>Step 3:<\/strong> Select &#8220;Country&#8221; in the sort by column of the Sort dialog box that appears. Check to verify that Cell Values is chosen in the Sort on field. Choose A to Z (or Z to A, it doesn&#8217;t really matter) under the Order option.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-3972\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Delete-rows-based-on-cell-value-20.png\" alt=\"Delete rows based on cell value\" width=\"453\" height=\"208\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Delete-rows-based-on-cell-value-20.png 453w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Delete-rows-based-on-cell-value-20-300x138.png 300w\" sizes=\"(max-width: 453px) 100vw, 453px\" \/><\/p>\n<p><strong>Step 4:<\/strong> Select OK. You will then receive the sorted data set as displayed below (sorted by column B).<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-3973\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Delete-rows-based-on-cell-value-21.png\" alt=\"Delete rows based on cell value\" width=\"407\" height=\"412\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Delete-rows-based-on-cell-value-21.png 407w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Delete-rows-based-on-cell-value-21-296x300.png 296w\" sizes=\"(max-width: 407px) 100vw, 407px\" \/><\/p>\n<p><strong>Step 5<\/strong>: All Germany-related records should be chosen (all the cells in the rows, not just the country column). Right-click the item you&#8217;ve chosen and then select Delete.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-3974 size-full\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Delete-rows-based-on-cell-value-22.png\" alt=\"Delete Rows Based on Cell Value in Excel\" width=\"344\" height=\"381\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Delete-rows-based-on-cell-value-22.png 344w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Delete-rows-based-on-cell-value-22-271x300.png 271w\" sizes=\"(max-width: 344px) 100vw, 344px\" \/><\/p>\n<p><strong>Step 6:<\/strong> The &#8220;Delete&#8221; dialog box will then appear. Ascertain that the &#8216;Shift cells up&#8217; option is chosen. Then click OK.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-3975\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Delete-rows-based-on-cell-value-23.png\" alt=\"Delete rows based on cell value\" width=\"273\" height=\"169\" \/><\/p>\n<p>The aforementioned actions would eliminate all records with the Germany country, but they do not eliminate the entire row. Therefore, any data that is to the right or left of your dataset will be unaffected.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-3976\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Delete-rows-based-on-cell-value-24.png\" alt=\"Delete rows based on cell value\" width=\"386\" height=\"333\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Delete-rows-based-on-cell-value-24.png 386w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Delete-rows-based-on-cell-value-24-300x259.png 300w\" sizes=\"(max-width: 386px) 100vw, 386px\" \/><\/p>\n<p>Although I sorted the data based on the cell value in the example above, you can also use the same methods to sort the data based on numbers, dates, the color of the cell or the color of the font, etc.<\/p>\n<p>You need a means to sort the data back into the original order in the event that you wish to maintain the original data set order but eliminate records based on criteria. To accomplish this, sort the data first, then add a column with serial numbers. After you&#8217;ve finished removing the rows and records, just sort the data using the newly added column.<\/p>\n<h2><strong id=\"find-and-select-the-cells-based-on-cell-value,-then-delete-the-rows\">3. Find and select the cells based on cell value, then delete the rows<\/strong><\/h2>\n<p>When you want to locate and pick cells with a certain value, Excel&#8217;s Find and Replace capability can be quite helpful.<\/p>\n<p>You can quickly erase the rows after selecting these cells.<\/p>\n<p>Let&#8217;s say you have the dataset depicted below and wish to remove every row where Germany is listed as the country.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-3977 size-full\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Delete-rows-based-on-cell-value-25.png\" alt=\"Delete Rows Based on Cell Value in Excel\" width=\"378\" height=\"382\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Delete-rows-based-on-cell-value-25.png 378w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Delete-rows-based-on-cell-value-25-297x300.png 297w\" sizes=\"(max-width: 378px) 100vw, 378px\" \/><\/p>\n<p><strong>The procedures are as follows:<\/strong><\/p>\n<p><strong>Step 1:<\/strong> Choose the whole set of data.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-3978\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Delete-rows-based-on-cell-value-26.png\" alt=\"Delete rows based on cell value\" width=\"406\" height=\"343\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Delete-rows-based-on-cell-value-26.png 406w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Delete-rows-based-on-cell-value-26-300x253.png 300w\" sizes=\"(max-width: 406px) 100vw, 406px\" \/><\/p>\n<p><strong>Step 2:<\/strong> On the \u201cHome\u201d tab, click. Select the &#8220;Find &amp; Select&#8221; option under the \u201cEditing\u201d group, and then click \u201cFind\u201d (you may alternatively use the keyboard shortcut Control + F).<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-3979\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Delete-rows-based-on-cell-value-27.png\" alt=\"Delete rows based on cell value\" width=\"545\" height=\"210\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Delete-rows-based-on-cell-value-27.png 545w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Delete-rows-based-on-cell-value-27-300x116.png 300w\" sizes=\"(max-width: 545px) 100vw, 545px\" \/><\/p>\n<p><strong>Step 3:<\/strong> In the &#8220;Find what:&#8221; field of the Find and Replace dialog box, type &#8220;Germany&#8221;. Select \u201cFind All\u201d. This will display all occurrences of the text \u201cGermany\u201d that Excel was able to locate for you right away. \u201cControl + A\u201d on the keyboard will select every cell that Excel has located. Additionally, you will be able to view every single selected cell in the dataset.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-3980\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Delete-rows-based-on-cell-value-28.png\" alt=\"Delete rows based on cell value\" width=\"447\" height=\"276\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Delete-rows-based-on-cell-value-28.png 447w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Delete-rows-based-on-cell-value-28-300x185.png 300w\" sizes=\"(max-width: 447px) 100vw, 447px\" \/><\/p>\n<p><strong>Step 4:<\/strong> Select &#8220;Delete&#8221; from the context menu when you right-click on any chosen cell. The \u201cDelete\u201d dialog box will then be displayed.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-3981\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Delete-rows-based-on-cell-value-29.png\" alt=\"Delete rows based on cell value\" width=\"329\" height=\"322\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Delete-rows-based-on-cell-value-29.png 329w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Delete-rows-based-on-cell-value-29-300x294.png 300w\" sizes=\"(max-width: 329px) 100vw, 329px\" \/><\/p>\n<p><strong>Step 5:<\/strong> Choose &#8220;Entire row&#8221; from the menu. Select OK.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-3982\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Delete-rows-based-on-cell-value-30.png\" alt=\"Delete rows based on cell value\" width=\"264\" height=\"162\" \/><\/p>\n<p>The aforementioned actions would remove Germany from every cell.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-3983 size-full\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Delete-rows-based-on-cell-value-31.png\" alt=\"Delete Rows Based on Cell Value in Excel\" width=\"428\" height=\"387\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Delete-rows-based-on-cell-value-31.png 428w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Delete-rows-based-on-cell-value-31-300x271.png 300w\" sizes=\"(max-width: 428px) 100vw, 428px\" \/><\/p>\n<p><strong>Note:<\/strong> You can use wildcard characters to find data in Excel because Find and Replace can handle them. Use &#8216;*West&#8217; as the text to find in the Find and Replace dialog box, for instance, if you wish to remove all rows where the region is either Mid-West or South-West. This will show you every cell where the word &#8220;West&#8221; appears at the conclusion of the text.<\/p>\n<h2><strong id=\"delete-every-row-that-has-a-blank-cell\">4. Delete every row that has a blank cell<\/strong><\/h2>\n<p>If you want to remove all the rows with blank cells, you may accomplish so quickly and effectively in Excel using a built-in feature.<\/p>\n<p>You can rapidly choose all the empty cells by using the Go-To Special Cells option. Additionally, it&#8217;s really easy to delete these cells once you&#8217;ve picked all of the empty ones.<\/p>\n<p>Consider the following dataset: I want to remove all the entries for which I don&#8217;t have the profit value.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-3984\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Delete-rows-based-on-cell-value-32.png\" alt=\"Delete rows based on cell value\" width=\"395\" height=\"383\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Delete-rows-based-on-cell-value-32.png 395w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Delete-rows-based-on-cell-value-32-300x291.png 300w\" sizes=\"(max-width: 395px) 100vw, 395px\" \/><\/p>\n<p><strong>The procedures are as follows:<\/strong><\/p>\n<p><strong>Step 1:<\/strong> Choose the whole set of data.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-3985\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Delete-rows-based-on-cell-value-33.png\" alt=\"Delete rows based on cell value\" width=\"405\" height=\"330\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Delete-rows-based-on-cell-value-33.png 405w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Delete-rows-based-on-cell-value-33-300x244.png 300w\" sizes=\"(max-width: 405px) 100vw, 405px\" \/><\/p>\n<p><strong>Step 2:<\/strong> F5 must be pressed on your keyboard. The &#8220;Go To&#8221; dialog box will then appear (you may also get it from Home -&gt; Editing -&gt; Find and Select -&gt; Go To). Choose the Special button in the &#8220;Go To&#8221; dialog box. The &#8220;Go To Special&#8221; dialog box will then be displayed.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-3986\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Delete-rows-based-on-cell-value-34.png\" alt=\"Delete rows based on cell value\" width=\"256\" height=\"241\" \/><\/p>\n<p><strong>Step 3:<\/strong> Choose &#8220;Blanks&#8221; from the Go To Special dialog box. Select OK.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-3987\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Delete-rows-based-on-cell-value-35.png\" alt=\"Delete rows based on cell value\" width=\"267\" height=\"278\" \/><\/p>\n<p><strong>Step 4:<\/strong> The aforementioned actions would choose every cell in the dataset that is empty. Once the blank cells are selected, use the right-click menu to pick &#8220;Delete&#8221; from any cell.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-3988\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Delete-rows-based-on-cell-value-36.png\" alt=\"Delete rows based on cell value\" width=\"516\" height=\"345\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Delete-rows-based-on-cell-value-36.png 516w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Delete-rows-based-on-cell-value-36-300x201.png 300w\" sizes=\"(max-width: 516px) 100vw, 516px\" \/><\/p>\n<p><strong>Step 5:<\/strong> Select &#8220;Entire row&#8221; from the drop-down menu in the Delete dialog box, then click OK<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-3989\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Delete-rows-based-on-cell-value-37.png\" alt=\"Delete rows based on cell value\" width=\"262\" height=\"160\" \/><\/p>\n<p>The aforementioned actions would remove blank cells.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-3990\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Delete-rows-based-on-cell-value-38.png\" alt=\"Delete rows based on cell value\" width=\"421\" height=\"357\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Delete-rows-based-on-cell-value-38.png 421w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Delete-rows-based-on-cell-value-38-300x254.png 300w\" sizes=\"(max-width: 421px) 100vw, 421px\" \/><\/p>\n<h2><strong id=\"delete-cell-based-on-conditional-formatting\">5. Delete cell based on conditional formatting<\/strong><\/h2>\n<p><strong>The steps listed below should be followed if you wish to delete cells in Excel depending on conditional formatting.<\/strong><\/p>\n<p><strong>Step 1:<\/strong> Choose the whole set of data.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-3991\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Delete-rows-based-on-cell-value-39.png\" alt=\"Delete rows based on cell value\" width=\"450\" height=\"378\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Delete-rows-based-on-cell-value-39.png 450w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Delete-rows-based-on-cell-value-39-300x252.png 300w\" sizes=\"(max-width: 450px) 100vw, 450px\" \/><\/p>\n<p><strong>Step 2:<\/strong> Go to \u201cHome\u201d tab, then click \u201cConditional Formatting\u201d option under \u201cStyles\u201d group. Then click \u201cHighlight Cells Rules\u201d, then select \u201cText that Contains\u2026\u201d.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-3992\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Delete-rows-based-on-cell-value-40.png\" alt=\"Delete rows based on cell value\" width=\"603\" height=\"255\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Delete-rows-based-on-cell-value-40.png 603w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Delete-rows-based-on-cell-value-40-300x127.png 300w\" sizes=\"(max-width: 603px) 100vw, 603px\" \/><\/p>\n<p><strong>Step 3:<\/strong> After that, \u201cText That Contains\u201d dialog box will open. In \u201cFormat cells that contain the text\u201d box, type \u201cAmerican Eagle\u201d as we want to delete this buyer\u2019s info. Then click OK.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-3993\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Delete-rows-based-on-cell-value-41.png\" alt=\"Delete rows based on cell value\" width=\"412\" height=\"129\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Delete-rows-based-on-cell-value-41.png 412w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Delete-rows-based-on-cell-value-41-300x94.png 300w\" sizes=\"(max-width: 412px) 100vw, 412px\" \/><\/p>\n<p><strong>Step 4:<\/strong> On the Data tab, click. Simply select the &#8220;Sort&#8221;\u00a0icon from the &#8220;Sort &amp; Filter&#8221;\u00a0group.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-3994\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Delete-rows-based-on-cell-value-42.png\" alt=\"Delete rows based on cell value\" width=\"576\" height=\"94\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Delete-rows-based-on-cell-value-42.png 576w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Delete-rows-based-on-cell-value-42-300x49.png 300w\" sizes=\"(max-width: 576px) 100vw, 576px\" \/><\/p>\n<p><strong>Step 5:<\/strong> Select &#8220;Buyer&#8221; in the sort by column of the Sort dialog box that appears. Check to verify that \u201cCell Color\u201d is chosen in the \u201cSort on\u201d field. Choose color under the \u201cOrder\u201d option.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-3995\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Delete-rows-based-on-cell-value-43.png\" alt=\"Delete rows based on cell value\" width=\"468\" height=\"217\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Delete-rows-based-on-cell-value-43.png 468w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Delete-rows-based-on-cell-value-43-300x139.png 300w\" sizes=\"(max-width: 468px) 100vw, 468px\" \/><\/p>\n<p><strong>Step 6:<\/strong> Select OK. You will then receive the sorted data set as displayed below (sorted by column C).<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-3996\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Delete-rows-based-on-cell-value-44.png\" alt=\"Delete rows based on cell value\" width=\"429\" height=\"442\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Delete-rows-based-on-cell-value-44.png 429w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Delete-rows-based-on-cell-value-44-291x300.png 291w\" sizes=\"(max-width: 429px) 100vw, 429px\" \/><\/p>\n<p><strong>Step 7<\/strong>: All American Eagle-related records should be chosen (all the cells in the rows, not just the Buyer column). Right-click the item you&#8217;ve chosen and then select Delete.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-3997\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Delete-rows-based-on-cell-value-45.png\" alt=\"Delete rows based on cell value\" width=\"429\" height=\"315\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Delete-rows-based-on-cell-value-45.png 429w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Delete-rows-based-on-cell-value-45-300x220.png 300w\" sizes=\"(max-width: 429px) 100vw, 429px\" \/><\/p>\n<p><strong>Step 8:<\/strong> The &#8220;Delete&#8221; dialog box will then appear. Ascertain that the &#8216;Shift cells up&#8217; option is chosen. Then click OK.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-3998\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Delete-rows-based-on-cell-value-46.png\" alt=\"Delete rows based on cell value\" width=\"277\" height=\"174\" \/><\/p>\n<p>The aforementioned actions would eliminate all records with the American Eagle buyer, but they do not eliminate the entire row. Therefore, any data that is to the right or left of your dataset will be unaffected.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-3999\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Delete-rows-based-on-cell-value-47.png\" alt=\"Delete rows based on cell value\" width=\"414\" height=\"337\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Delete-rows-based-on-cell-value-47.png 414w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Delete-rows-based-on-cell-value-47-300x244.png 300w\" sizes=\"(max-width: 414px) 100vw, 414px\" \/><\/p>\n<h2><strong id=\"based-on-cell-value,-filter-and-delete-rows-(using-vba)\">6. Based on cell value, filter and delete rows (using VBA)<\/strong><\/h2>\n<p>The final technique I&#8217;ll demonstrate to you uses a small amount of VBA.<\/p>\n<p>If you frequently need to delete rows based on a particular value in a column, you can use this technique. The VBA code can be added once to your Personal Macro workbook. In this manner, you can utilize it across all of your Excel workbooks.<\/p>\n<p>The Filter technique described above is how this code operates (except the fact that this does all the steps in the backend and save you some clicks).<\/p>\n<p>Let&#8217;s say you have the dataset depicted below and wish to remove every row where Germany is listed as the country.<img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-4000\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Delete-rows-based-on-cell-value-48.png\" alt=\"Delete rows based on cell value\" width=\"383\" height=\"402\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Delete-rows-based-on-cell-value-48.png 383w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Delete-rows-based-on-cell-value-48-286x300.png 286w\" sizes=\"(max-width: 383px) 100vw, 383px\" \/><\/p>\n<p><strong>The VBA code needed for this is provided below:<\/strong><\/p>\n<p>Sub DeleteRowsWithSpecificText()<\/p>\n<p>ActiveCell.AutoFilter Field:=2, Criteria1:=&#8221;Germany&#8221;<\/p>\n<p>ActiveSheet.AutoFilter.Range.Offset(1, 0).Rows.SpecialCells(xlCellTypeVisible).Delete<\/p>\n<p>End Sub<\/p>\n<p>The code above first filters the rows using the supplied criteria (in this case, &#8220;Germany&#8221;), then selects all the filtered rows and deletes them.<\/p>\n<p>To ensure that my header row is not erased, you&#8217;ll notice that Offset was utilized in the code above.<\/p>\n<p>If your data is in an Excel Table, the code above won&#8217;t work. Excel views an Excel Table as a list object, which explains why. Thus, you must slightly adjust the code if you wish to delete rows from a Table (covered later in this tutorial).<\/p>\n<p>It will present you with a prompt like seen below just before deleting the rows. This is advantageous to me because it enables me to confirm the filtered row before deleting it.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-4001\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Delete-rows-based-on-cell-value-49.png\" alt=\"Delete rows based on cell value\" width=\"243\" height=\"124\" \/><\/p>\n<p>Keep in mind that you cannot revert the deletion of rows made using VBA. Use this only if you are certain that it will accomplish your goals. Additionally, keeping a backup copy of the data is a good idea in case something goes wrong.<\/p>\n<p><strong>If your data is in an Excel table, you can delete rows that have a particular value by using the code below:<\/strong><\/p>\n<p><strong>\u00a0<\/strong>Sub DeleteRowsinTables()<\/p>\n<p>Dim Tbl As ListObject<\/p>\n<p>Set Tbl = ActiveSheet.ListObjects(1)<\/p>\n<p>ActiveCell.AutoFilter Field:=2, Criteria1:=&#8221;Germany&#8221;<\/p>\n<p>Tbl.DataBodyRange.SpecialCells(xlCellTypeVisible).Delete<\/p>\n<p>End Sub<\/p>\n<p>I had to modify the code since VBA views Excel Table as a List object (and not a range).<\/p>\n<p><strong>Where Should the VBA Code Go?<\/strong><\/p>\n<p>This code needs to be added to a module in the VB Editor backend.<\/p>\n<p><strong>The steps that will demonstrate how to do this are as follows:<\/strong><\/p>\n<p><strong>Step 1:<\/strong> The VBA Editor window can be opened by pressing the keyboard shortcut ALT + F11. A &#8220;Project Explorer&#8221; pane is located in the left side of this VBA Editor window (which lists all the workbooks and worksheets objects). To use this code, double-click any object in the workbook, this will open the Module code window on the right and add the Module object to the Workbook. Copy the aforementioned code, then paste it in the module window (which will show up on the right).<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-4002\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Delete-rows-based-on-cell-value-50.png\" alt=\"Delete rows based on cell value\" width=\"613\" height=\"407\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Delete-rows-based-on-cell-value-50.png 613w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Delete-rows-based-on-cell-value-50-300x199.png 300w\" sizes=\"(max-width: 613px) 100vw, 613px\" \/><\/p>\n<p><strong>Step 2:<\/strong> Once the code is in the VB Editor, you can execute it using any of the following techniques (be sure to choose any cell in the dataset you wish to execute the code on first):<\/p>\n<ol>\n<li>Press the F5 key while selecting any line of the code.<\/li>\n<li>In the VB Editor&#8217;s toolbar, select the Run button.<\/li>\n<li>By clicking on the macro&#8217;s assigned button or form within the worksheet, you can activate it.<\/li>\n<li>Add it to the Quick Access Toolbar to instantly execute the code.<\/li>\n<\/ol>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-4003\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Delete-rows-based-on-cell-value-51.png\" alt=\"Delete rows based on cell value\" width=\"562\" height=\"239\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Delete-rows-based-on-cell-value-51.png 562w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Delete-rows-based-on-cell-value-51-300x128.png 300w\" sizes=\"(max-width: 562px) 100vw, 562px\" \/><\/p>\n<p><strong>Step 3:<\/strong> After that, it will present you with a prompt like seen below just before deleting the rows. Click OK.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-4004\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Delete-rows-based-on-cell-value-52.png\" alt=\"Delete rows based on cell value\" width=\"246\" height=\"128\" \/><\/p>\n<p><strong>Step 4:<\/strong> Then, on the \u201cData\u201d tab, click. Choose the &#8220;Filter&#8221;\u00a0icon under the &#8220;Sort &amp; Filter&#8221; group.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-4005\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Delete-rows-based-on-cell-value-53.png\" alt=\"Delete rows based on cell value\" width=\"642\" height=\"100\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Delete-rows-based-on-cell-value-53.png 642w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Delete-rows-based-on-cell-value-53-300x47.png 300w\" sizes=\"(max-width: 642px) 100vw, 642px\" \/><\/p>\n<p>After that, the result looks like this below.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-4006\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Delete-rows-based-on-cell-value-54.png\" alt=\"Delete rows based on cell value\" width=\"426\" height=\"383\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Delete-rows-based-on-cell-value-54.png 426w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/Delete-rows-based-on-cell-value-54-300x270.png 300w\" sizes=\"(max-width: 426px) 100vw, 426px\" \/><\/p>\n<p><strong>Note:<\/strong> You must save the workbook in the macro-enabled format because it contains VBA macro code (xlsm).<\/p>\n<h2><strong>Application of Delete Rows Based on Cell Value in Excel<\/strong><\/h2>\n<div class=\"w-full text-token-text-primary\" data-testid=\"conversation-turn-294\">\n<div class=\"px-4 py-2 justify-center text-base md:gap-6 m-auto\">\n<div class=\"flex flex-1 text-base mx-auto gap-3 md:px-5 lg:px-1 xl:px-5 md:max-w-3xl lg:max-w-[40rem] xl:max-w-[48rem] group\">\n<div class=\"relative flex w-full flex-col agent-turn\">\n<div class=\"flex-col gap-1 md:gap-3\">\n<div class=\"flex flex-grow flex-col max-w-full\">\n<div class=\"min-h-[20px] text-message flex flex-col items-start gap-3 whitespace-pre-wrap break-words [.text-message+&amp;]:mt-5 overflow-x-auto\" data-message-author-role=\"assistant\" data-message-id=\"a260de69-04ef-4227-8a32-b08d6ee00dfa\">\n<div class=\"markdown prose w-full break-words dark:prose-invert light\">\n<ul>\n<li><strong>Data Cleansing<\/strong>: Remove rows containing errors or irrelevant values, such as &#8220;#N\/A&#8221; or &#8220;Error&#8221;, to clean your dataset and improve analysis accuracy.<\/li>\n<li><strong>Filtering Out Inactive Records<\/strong>: Automatically delete rows that contain specific identifiers for inactive accounts, clients, or products, keeping your databases current.<\/li>\n<li><strong>Removing Duplicate Entries<\/strong>: Delete rows that contain duplicate data based on specific cell values to ensure data uniqueness and integrity.<\/li>\n<li><strong>Excluding Low-Performance Data<\/strong>: Automatically remove rows with performance indicators below a certain threshold, focusing analysis on higher-performing segments or individuals.<\/li>\n<li><strong>Managing Inventory Levels<\/strong>: Delete rows containing products with zero inventory to streamline inventory management and reporting.<\/li>\n<li><strong>Data Segregation<\/strong>: Remove rows that pertain to a specific department, geographic location, or category that is irrelevant to the current analysis or report.<\/li>\n<\/ul>\n<\/div>\n<\/div>\n<\/div>\n<div class=\"mt-1 flex justify-start gap-3 empty:hidden\"><\/div>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n<p>For ready-to-use Dashboard Templates:<\/p>\n<ol>\n<li><a href=\"https:\/\/www.bizinfograph.com\/dashboard-templates#Finance\" target=\"_blank\" rel=\"noopener\">Financial Dashboards<\/a><\/li>\n<li><a href=\"https:\/\/www.bizinfograph.com\/dashboard-templates#Sales\" target=\"_blank\" rel=\"noopener\">Sales Dashboards<\/a><\/li>\n<li><a href=\"https:\/\/www.bizinfograph.com\/dashboard-templates#Human-Resource\" target=\"_blank\" rel=\"noopener\">HR Dashboards<\/a><\/li>\n<li><a href=\"https:\/\/www.executiveknowledge.org\/dashboard-templates\/35\" target=\"_blank\" rel=\"noopener\">Data Visualization Charts<\/a><\/li>\n<\/ol>\n","protected":false},"excerpt":{"rendered":"<p>Delete Rows Based on Cell Value in Excel to maintain clean, relevant, and accurate datasets for your analyses. This efficient method helps streamline data management tasks, allowing you to focus on meaningful information that drives decision-making. By mastering this technique, you can automate the process of filtering out unnecessary or erroneous data, enhancing your productivity &hellip;<\/p>\n<p class=\"read-more\"> <a class=\"\" href=\"https:\/\/www.bizinfograph.com\/resource\/how-to-delete-rows-based-on-cell-value-in-excel\/\"> <span class=\"screen-reader-text\">How to Delete Rows Based on Cell Value in Excel?<\/span> Read More &raquo;<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"site-sidebar-layout":"default","site-content-layout":"default","ast-global-header-display":"","ast-main-header-display":"","ast-hfb-above-header-display":"","ast-hfb-below-header-display":"","ast-hfb-mobile-header-display":"","site-post-title":"","ast-breadcrumbs-content":"","ast-featured-img":"","footer-sml-layout":"","theme-transparent-header-meta":"default","adv-header-id-meta":"","stick-header-meta":"","header-above-stick-meta":"","header-main-stick-meta":"","header-below-stick-meta":"","footnotes":""},"categories":[493],"tags":[495,494],"class_list":["post-3952","post","type-post","status-publish","format-standard","hentry","category-delete-rows-based-on-cell-value","tag-delete-rows-based-on-cell-value","tag-delete-rows-based-on-cell-value-in-excel"],"_links":{"self":[{"href":"https:\/\/www.bizinfograph.com\/resource\/wp-json\/wp\/v2\/posts\/3952","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.bizinfograph.com\/resource\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.bizinfograph.com\/resource\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.bizinfograph.com\/resource\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.bizinfograph.com\/resource\/wp-json\/wp\/v2\/comments?post=3952"}],"version-history":[{"count":5,"href":"https:\/\/www.bizinfograph.com\/resource\/wp-json\/wp\/v2\/posts\/3952\/revisions"}],"predecessor-version":[{"id":8836,"href":"https:\/\/www.bizinfograph.com\/resource\/wp-json\/wp\/v2\/posts\/3952\/revisions\/8836"}],"wp:attachment":[{"href":"https:\/\/www.bizinfograph.com\/resource\/wp-json\/wp\/v2\/media?parent=3952"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.bizinfograph.com\/resource\/wp-json\/wp\/v2\/categories?post=3952"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.bizinfograph.com\/resource\/wp-json\/wp\/v2\/tags?post=3952"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}