Excel: Filtering Rows Already Used in Validation Lists

Excel Filtering Rows Based on Already Used Validation Lists

In this article, we will discuss how to filter rows in Microsoft Excel based on already used validation lists. This technique can be very useful when working with large datasets and you want to quickly view only the rows that contain specific values from a drop-down list.

What is a Validation List in Excel?

A validation list in Excel is a drop-down list that allows you to restrict the data that can be entered into a cell. This can be useful for ensuring data consistency and accuracy in your spreadsheets. To create a validation list, you can use the Data > Data Validation command in Excel.

Filtering Rows Based on Already Used Validation Lists

To filter rows based on already used validation lists, you can use the Filter feature in Excel. Here's how:

1. Select the column that contains the validation list.
2. Click the Data > Filter command to enable filtering.
3. Click the drop-down arrow in the column header to display the filter options.
4. Select the Text Filters or Number Filters option, depending on the type of data in the column.
5. Select the Does Not Equal option, and then select the value(s) that you want to exclude from the filter.
6. Click the OK button to apply the filter.

Example

Let's say you have a list of jobs in a table, and you want to filter the table to show only the jobs that have not been completed. The first column in the table contains a validation list with the following values:

• Job #12345
• Job #12346
• Job #12347
• Job #12348
• Another tab
• Data validation lists
• ...

To filter the table to show only the jobs that have not been completed, you can follow these steps:

1. Select the first column in the table.
2. Click the Data > Filter command to enable filtering.
3. Click the drop-down arrow in the column header to display the filter options.
4. Select the Text Filters option, and then select the Does Not Equal option.
5. Select the value Completed from the drop-down list, and then click the OK button to apply the filter.

The table will now be filtered to show only the jobs that have not been completed.

Filtering rows based on already used validation lists in Excel can be a powerful technique for working with large datasets. By using the Filter feature in Excel, you can quickly view only the rows that contain specific values from a drop-down list. This can help you to analyze and manage your data more efficiently.

References

• Apply filtering to hide Excel data
• Use a drop-down list to filter data