Create a Dynamic Filter in Excel Using Conditional Formatting

Create a Dynamic Filter in Excel Using Conditional Formatting

Buy the Cheat Sheet and Workbook: https://gumroad.com/l/ytfiltercheatsheet

For more Excel tips and tricks be sure to visit me at http://www.bradedgar.com.

In this video I'll show you exactly how you can setup a data validation list in Excel. To do this you will go to your data validation list under the data tab in the quick access ribbon, select data validation and then refer to a range that lists your part numbers. Once added, you will then create and add the filter options by using CTRL + SHIFT + L.

The next step will be to apply Excel conditional formatting to the field/column you want to filter by and you will make sure that the conditional formatting refers to the data validation list selection cell so that items that are equal to the value selected are formatted in a white color. We made sure it was the same color so that it was not noticeable which items were highlighted in our data set for the analysis. Once this is done, you will filter by the field that you are dealing with (in our case the part number) by the new color applied using the conditional formatting.

Once you've done this, whatever part number you select will be filtered. As soon as you change the part number in your drop down, you'll notice that the filter is not update. You need to reapply your filter. To do this, we will write a macro as follows and create a module:

Sub ApplyFilter()

ActiveSheet.AutoFilter.ApplyFilter

End Sub

We will the use the event change under the spreadsheet that the drop down and data set exist on and call the ApplyFilter macro. This will make our filter reapply every time an update is made to the drop down list value for the data validation. All of our items will then filter based on the selection.

Other resources for this article:

https://youtu.be/pCAXRLYr59E

http://trumpexcel.com/2015/01/dynamic-excel-filter/

http://chandoo.org/wp/2009/02/12/make-a-dynamic-chart-using-data-filters/

https://youtu.be/0xOHevuILxI

http://www.contextures.com/xlautofilter01.html

https://blog.udemy.com/excel-vba-autofilter/

http://www.excelguru.ca/forums/showthread.php?2052-Excel-2010-Dynamic-Autofilter-on-Change

http://www.techrepublic.com/blog/microsoft-office/how-to-sum-values-in-an-excel-filtered-list/

http://www.bradedgar.com/premium-excel-slicer-tutorial-filter-sort-topbottom-5/

http://www.bradedgar.com/excel-tables-using-excel-tables-summarize-filter-autofill-formulas-like-boss/

http://www.excel-easy.com/examples/advanced-filter.html

-~-~~-~~~-~~-~-
Checkout my newest video: "How to Use the Vlookup Formula in you Excel Dashboard"
https://www.youtube.com/watch?v=MCBmPnaKf_g
-~-~~-~~~-~~-~-