Excel Filter By Month: Filter feature or FILTER function? Amazing Dynamic Formula Solution. EMT 1796

Learn how to filter a data set by month using the Filter feature and the FILTER function. See how to create dynamic Data Valication also..
Topics:
1. (00:00) Introduction
2. (00:17) Filter Feature for a data set with just one year of data
3. (01:20) Filter Feature for a data set with multiple years in in data set
4. (02:00) Create Dynamic Data Valication Dropdown List for Year and Month See the functions: SORT, TEXT, UNIQUE
5. (05:16) Create Filtering Formula using the functions: FILTER and TEXT
6. (06:07) Add new records to table and test if solution is dynamic
7. (07:07) Summary,
8. (07:26) Closing, Video Links

[fernando]

I learn a lot from your classes, thank you for sharing your knowledge

[simoiyahector-morales]

You are a literal Excel Guru, always something new to learn. Thanks for sharing this knowledge FREELY with us. God bless you.

[sheripresloid]

I am absolutely loving all these videos. Been working in Excel for 30 years and have expertise, but some of the little things are amazing. Love the # in the source section. Love the arrow to capture the table name. Love the unique usage in the date, etc.

I use Alt (or F10) and A T for filters. I am a huge keyboard person, so it is nice the newer Excel version still allows me to use my old timey keystrokes. :)

[monicamelara]

Thank you so much for taking the time to do this. I truly appreciate you!

[sumardjo]

thanks for all
because you give me sample file to practice
thanks for all
thanks

[darrylmorgan]

Boom!Super Cool Formula, Loving The Awesome Filter Function...Thank You Mike :)

[johnborg]

Thanks Mike. Data Validation removing dublicates in the List. Never noticed that. Just tried it now. That's Great!!!

[bagnon]

Instead of Ctrl+CC, I use Win+v and it works for all programs, not just Excel.

[chrism]

Thanks Mike! FILTER is just so awesome.

[JonathanExcels]

Some other program on my pc high jacked my ctrl+shift+L earlier this year and I haven’t been able to use it. I work around it with menu acceleration keys but muscle memory really wants that shortcut to work.

Very clear video. Excellent!

[nadermounir]

excellent tutorial Mike 👌 I try to never miss any of your videos 📹

[excel_along_the_way]

Thank you Mike. When Amazon open in South Africa hopefully your book can be purchased here.

[robinmock]

I needed this to complete a n assignment! Thank you

[spyrakos]

Great video. I would like to see a Power Query approach, too!

[nobby-green]

This a great thanks! one question, when the data is displayed, i get numbers in the column rather that dates shown. any ideas?

[richardhay]

I like this video !! I'm a big fan and regular user of FILTER. I often filter by date parameters. Helper columns seem not to be politically correct but I find them extremely tempting esp with anything that might become a FILTER criterion. After all spreadsheet real estate is not Scarce with 2^14 columns avaiable!!! So a sheet I was updating a few moments ago has separate columns for MONTH, YEAR & WEEK by using TEXT() for month and year and WEEKNUMBER for week. This gives me max flexibility to build reports quickly with FILTER. Putting month and year in the same cell is a good look but some of my sheets eventually grow to five or even ten years of transaction data making such combo dropdowns rather unweildy. Often I filter by complex multiple criteria (such as jan 2021 and jan 2019 in one FILTER report) so I need max flexiblity in referencing. I use linked reference cells but type in criteria for each report and reference such cells with FILTER.

I haven't used filter buttons for a long time. If I want to Filter in plce I normally use Table Slicers.

[realtoast]

Great! But what's the difference between Ctrl-Shift-L and Ctrl-T when establishing a table?

[moesadr]

Great video, thank you. How about creating a dynamic report showing the totals in this format: This Month, Last Month and this quarter, last quarter and this year, last year at all times without any extra steps. something like a dashboard which always only automatically calculates and shows above mentioned values?

[j.rjunior]

I was using the same formula you used, =SORT(UNIQUE (TEXT("Column", "yyy-mm"))).

But I had a lot of blank cells, and those blank cells would return as 1900-01.
The blank cells will be filled with more future data that I would get.

So what I did was I took a random column(AV), and I typed. =IF(A8="", "", TEXT(A8, "yyy-mm")) .
And I draged that formula down to the amount of columns I was using.
Column A is my date column btw.

Than I used the =SORT(UNIQUE (TEXT(AV8:AV600, "yyy-mm"))).
But it wouldn't sort the month and day properly, soo I ended up switching the formula around to, =UNIQUE (SORT(TEXT(AV8:AV600, "yyy-mm"))). And it worked perfectly. 👌

Thank you soo much, Mr. Excel. This was exactly what I was looking for. Thank you.

[sty]

In ms 365 for data validation, even if the list of cells referenced by the list function is not a duplicate value, it removes the duplicates and creates a list with unique values. I don't know when it was updated, but I found out about these features a while ago.