Extract Data Based on a Drop Down List - Deselect Option

[exceljunior]

Hi there,

I found this spreadsheet containing a drop-down list that extracts data based on the selection in Cell H4 and H6

However, say you selected both filters, is it possible to 'deselect' one over the other and have the data shown based on this. (Example: Have Alabama selected, but I want cell H6 deselected to just show all 'Alabama' results) Ex. You want to filter the table to just 'branch', without removing the 'products' drop down menu cell.

Additionally, is there a way to deselect both 'Branch' and 'Products' drop down at the same time to show all the data.

Excel attached. Many thanks

[#2]

@exceljunior I've adjusted your formula and dropdown lists a little and it does what you ask for, though I feel there must be a better way to do it. However, an early morning over breakfast didn't give me the right inspiration just now.

Select the "*" to deselect an option.

[#3 · Accepted solution]

@exceljunior Another option is to use the IF function with ISBLANK to perform a different calculation if the criteria cells are blank. For example:

=FILTER(Sales, IF(ISBLANK(H4), ROW(Sales), Sales[Branch]=H4)*IF(ISBLANK(H6), ROW(Sales), Sales[Products]=H6), "Nothing Found")

The ROW function in the above formula simply returns the row number for each row in the table, if the criteria cell is blank. The FILTER function interprets all numeric values as TRUE (other than 0, which is interpreted as FALSE), so all records will be returned if both criteria cells are blank.

[#4]

Thanks for your suggestion Riny

[#5]

Thanks DJ!