In Excel, how to apply filter automatically to a table based on the selection in a drop-down list?

Question

For example, create a drop-down list in a specific cell outside the table with selections 1%, 2%, 3% etc, when click and select one of them, say 2%, it will automatically filter the table and only show rows with cell value greater than 2%.

Answer 1

Hi,

Assuming the table is in range A2:F100. Column E has the % entries. In cell H2, you select the % from the drop down. IN cell J2, enter this formula

=filter(A2:F100,E2:E100>H2)

Hope this helps.

Answer 2

Hi Ji2021!

Please try these steps: * Select the cell where you want to create the drop-down list. * Go to the "Data" tab and select "Data Validation". * In the "Data Validation" window, select "List" under the "Allow" option. * In the "Source" field, enter the list of values you want to include in the drop-down list, separated by commas (e.g. 1%, 2%, 3%). * Click "OK" to create the drop-down list in the selected cell. * In the table, add a filter to the column you want to filter based on the selected value. * Click the drop-down arrow in the filter for that column and select "Filter by Condition". * In the "Filter by Condition" window, select "Greater than" and enter the formula "=VALUE(LEFT($A$1,FIND("%",$A$1)-1))/100" (assuming the drop-down list is in cell A1). * Click "OK" to apply the filter.

Now, when you select a value from the drop-down list, the table will automatically filter and show only the rows where the value in the selected column is greater than the selected value.

Kind Regards, Shakiru

Answer 3

Hi,

Assuming the table is in range A2:F100. Column E has the % entries. In cell H2, you select the % from the drop down. IN cell J2, enter this formula

=filter(A2:F100,E2:E100>H2)

Hope this helps.

Hi, Ashish,

Your solution works but I have a big table and want to see the filtered result in the same sheet. Is it possible to filter the table directly?

Thanks

Jim

Answer 4

Hi Ji2021! Please try these steps: * Select the cell where you want to create the drop-down list. * Go to the "Data" tab and select "Data Validation". * In the "Data Validation" window, select "List" under the "Allow" option. * In the "Source" field, enter the list of values you want to include in the drop-down list, separated by commas (e.g. 1%, 2%, 3%). * Click "OK" to create the drop-down list in the selected cell. * In the table, add a filter to the column you want to filter based on the selected value. * Click the drop-down arrow in the filter for that column and select "Filter by Condition". * In the "Filter by Condition" window, select "Greater than" and enter the formula "=VALUE(LEFT($A$1,FIND("%",$A$1)-1))/100" (assuming the drop-down list is in cell A1). * Click "OK" to apply the filter. Now, when you select a value from the drop-down list, the table will automatically filter and show only the rows where the value in the selected column is greater than the selected value. Kind Regards, Shakiru

Hi, Shakiru,

I could not find the "Filter by Condition" selection. I am using office 365 and tried "number filters-->custom filter", then select "Greater than" and enter the formula "=VALUE(LEFT($A$1,FIND("%",$A$1)-1))/100" (assuming the drop-down list is in cell A1) but it did not work. No change on table when I make a selection in the drop down list. And it made the data in the table disappear. could not figure out how that happened.

Thanks'

Jim

Answer 5

Hi,

You will have to use VBA for that. Someone else will help you with the VBA code.