We might often need to delete cells based on the content of the cells when dealing with a large data set. So how to delete rows in active cell or specific text in excel? There are a few ways we can go about carrying our business. We will guide you with step-by-step instructions so that you can do this task easily.
When dealing with massive data sets, you may find yourself having to swiftly remove rows depending on the values contained inside them (or based on a condition).
Consider the following instances, for instance:
- Your company has sales representative data, and you want to remove all of the entries for a specified area or product.
- You wish to eliminate all the records where the selling value is less than one hundred dollars.
- You want to eliminate all the rows in which there is a blank cell in them.
- In Excel, there are a variety of approaches to dealing with this data set.
The approach you pick will differ depending on how your data is organized and what cell value or condition you wish to use to remove these rows.
Delete Rows Based on Text
While you may be able to remove these rows manually in this tiny dataset, your datasets are likely to be large, making manual deletion of rows an impractical alternative in most cases. So instead, we will use the filter option to delete the rows based on a specific location in our data set.
Step 1: Open a new Excel sheet and enter your data set. Next, you have to go to the Data tab.
Step 2: Press the filter button and click on the Downward arrow button; a drop-down menu will pop down, and then you can select which rows to delete by text. In our case, we are deleting the rows based on the North Location.
Step 3: Untick all of the text except the ones you wish to delete and press OK, and this will make only the ones you want to delete.
Step 4: Select the data set. Right-click on the data set, and click Delete Row. A pop-up will pop up and say, Delete Entire Sheet Row? Press OK, and those data will be deleted.
Step 4: Go to the Downward Arrow on the place you wished to remove them based on and select all, and the remaining data will be shown.
Note: It is vital to note that when you remove a row, everything that may have been in other cells in these rows will be deleted. Creating a duplicate of the data in another worksheet and then deleting the copied data’s rows is one way to get around this restriction. Once you’ve finished, transfer the data back into the original location.
Delete Rows Based On Number Data
In the same way that I used the filter technique to remove all of the rows with the word Mid-West, you can also use a number condition to delete all of the rows containing the text Mid-West (or a date condition).
In any case, let’s assume that we have the following dataset and that we want to eliminate all of the entries where the selling value is less than 500.
Step 1: Open the excel and enter the data set to go to the Data tab from the ribbon, click on the data set, and filter.
Step 2: Click on the Column from where you want the data deleted. You need to go to the Numbers Filters and select less, and a window will pop up.
Step 3: Enter the desired numbers that you wish to filter out. In our case, it’s 500, and press enter. You will be shown the numbers below 500. Next, choose the data set and then right-click on the data set and select Delete Row from the context menu. A pop-up will appear on the screen, asking whether you want to delete the whole sheet row. If you click “OK,” those files will be removed.
Step 4: Now, click on the filter button to remove the filter and see the remaining data set.
In Excel, you may apply various numerical filters, such as less than/greater than, equal/does not equal, between, top 10, above or below average, and so on.
Note: Please remember that you may apply different filters. Consider the following scenario: you want to remove all rows where the sales value is more than 200 but less than 500 dollars. In this scenario, you will need to employ two filter criteria to achieve success. The Custom Auto filter dialog box provides for the use of two filter criteria at the same time (AND as well as OR). In the same way that you can filter data based on numbers, you can also filter records based on dates. To eliminate all of the data from the first quarter, you may do so by following the identical methods outlined above in this section. Excel automatically displays the filters relevant to your work when you’re working with data filters.
Conclusion
Now you have an idea of how to delete rows about active cell or specific text in excel. These functions will make your lives a lot easier as they will help you filter out unnecessary data very quickly. Filtering out unnecessary data is a crucial skill that you should pick up if you are dealing with large spreadsheets daily.