Excel Date Filter Magic - Last Month, This Month, YTD & More + Awesome Interface

Рет қаралды 142,562

Күн бұрын

Excel Courses: www.teachexcel.com/premium-co...
Learn to make a beautiful and intuitive date filtering interface for your data in Excel that allows you to quickly view data for custom date periods, including rolling date periods, like This Month, Last Month, This Year, Last Year, or any other date interval that you can think of - I show you how to make the system that you need to view any custom date period that you can formulate.
This technique combines many features, including Tables, Data Validation Drop Down Lists, Custom Date Formulas, Lookups, FILTER() function for new versions of Excel, a crazy formula for older versions of Excel, and even rounded corners to make everything look nice.
The goal of this tutorial is to make it easier and nicer for you to analyze sets of data, particularly raw imported data - this works for stock data, financial transactions, bank records, sales data, or any kind of data that includes a date column.
I take you from processing the raw data, so it will filter better, all the way to making custom date formulas and polishing the front-end interface so that it looks nice and is easy-to-use.
Excel File: www.teachexcel.com/excel-tuto...
Dates in Excel: • How to Use Dates in Ex...
Drop-Down Lists (Data Validation): • Guide to Making Drop D...
VLOOKUP() Function in Excel: • VLOOKUP() Function in ...
Lookup to Return All Matches (big scary formula explained): • Vlookup to Return All ...
FILTER() Function: • FILTER Function in Exc...
Rounded Corners in the Worksheet: • Make Rounded Corner Ce...
Excel Courses:
- VBA/Macro Course: www.teachexcel.com/vba-course...
- Building Professional Forms: www.teachexcel.com/premium-co...
TeachExcel.com
#msexcel #howto #tutorial

Пікірлер: 74

@clairegao3988 2 жыл бұрын

Just watched this. Thank you for explaining things in such an easy and clear way. 🥰

@patrickschardt7724 3 жыл бұрын

FILTER is a wonderful function. Great tutorial. The period calculations are fantastic. I’m a current bill due date spreadsheet. Some of them are monthly, weekly, annually, etc. I’ll use yours a starting point. Thanks

@TeachExcel 3 жыл бұрын

Thanks Patrick, I'm really glad you liked the tutorial :)) FILTER is just so great, couldn't agree more! And definitely take my file and roll with it! I think that the setup should be flexible anough to change it around without much difficulty and the date formulas should also work well for many different combinations. I think when I get more time later this year I will make a small course on dates for Excel and how to get certain date periods, but, until then, feel free to ask for any help in getting your workbook up and running on the forum on TeachExcel.com

@marissawilliams3396 2 жыл бұрын

This was beautiful, thank you so much!!

@excellover7172 8 ай бұрын

Thank you very much! This is exactly what I was searching for.

@alexrosen8762 3 жыл бұрын

Excellent tutorial! Thank you :-)

@IvanCortinas_ES 3 жыл бұрын

Very original and creative case. Excellent tutorial. Thank you so much for sharing it.

@TeachExcel 3 жыл бұрын

Thank you Ivan, I'm happy that you think so, and I'm really glad that you liked it! :)

@moesadr3342 Жыл бұрын

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?

@YannisAndreadakis 3 ай бұрын

Excellent! Thank you!

@derrickwong3608 2 жыл бұрын

Great video. May I check how to extract my data if my data consists of a start date and end date?

@danmaruk 10 ай бұрын

Very informative and useful video, thank you. I am trying to do something similar with dates in the future (next 10 days, next 5 days) how would I apply that to the formulas?

@moesadr3342 Жыл бұрын

Great video, thank you. I wish you also could include the This and Last Quarter as well. Anyone could you please let me know what formula/s we should use if we also need to include Quarters as well? I would appreciate it.

@sbatsia 3 жыл бұрын

Absolutely awesome tutorial!!!! Thank you.

@TeachExcel 3 жыл бұрын

Thank you so much for your comment!!! I'm very glad you like it :))

@davidtopp1972 2 жыл бұрын

I watched your video on Date Filter Magic, and I think it is great. I would like to utilize it in my Budget & Check Register file. I am using Excel Version 16.54 for Mac. I would like to be able to track data on an ongoing basis, since I am constantly adding debits and credits to my check register, savings accounts, etc. Must I do anything special to make this work.

@ajayengineeringworks4735 2 жыл бұрын

That's amazing sir

@srikanthsoorianarayanan5161 2 жыл бұрын

Thank you it really helped

@lolipits9047 2 жыл бұрын

Man thank you very much!!!

@ifanadiahmad7274 3 жыл бұрын

good video add very clear information passion for creating the best content

@user-oz8gh1ux5k 2 жыл бұрын

Thank you for this usuful video! I really need this to making report abour our budget! Thank you sooooo much

@TeachExcel 2 жыл бұрын

You are most welcome!!! If you need additional automation, you can always try out VBA/Macros - here is my course that takes you from Beginner to Expert in VBA: www.teachexcel.com/vba-course-update.php?src=yt_comment_reply_hj6JSSQIrdw

@HangLe-of4sm 3 жыл бұрын

Very helpful tutorial. Thank you so much!!

@TeachExcel 3 жыл бұрын

I'm glad you think so! You are very welcome Hang!

@Fxingenieria Жыл бұрын

Excellent !!!!

@wilfredtaikerweyah4625 2 жыл бұрын

Very Educative

@wayneedmondson1065 3 жыл бұрын

Hi.. interesting approach. I've done something similar, but storing the live data in a proper Excel Table format (either copy and paste in or via Power Query) and then adding slicers for all the columns that I want to filter. For things like this/last month/year, it may require helper columns to get the functionality desired. Just another approach. Thanks for your tips.. always learn something :)) Thumbs up!!

@TeachExcel 3 жыл бұрын

I'm glad you like it Wayne :) And thanks for this great comment! I LOVE helper columns combined with the strategy that you mention and it is one of my favorite ways to make this kind of setup as well.

@samsonlo7846 2 жыл бұрын

It is great. Help me a lot

@matnelly1 3 жыл бұрын

Thank you

@aasmasultana8678 2 жыл бұрын

Hi, I have a data where it has 7k customers' info for the complete year for paid and unpaid list. is there any formula or shortcut to know when was the last time the customer paid in which month and year?

@Saad.PS2009 3 жыл бұрын

Thank you so much

@TeachExcel 3 жыл бұрын

You are very welcome!

@anikanik8501 3 жыл бұрын

Tutorial very useful tutorial

@loludave9731 7 ай бұрын

Thanks for this Tutorial. But please, my table array is not updating to the Table name as i selected the array when applying the filter function. What could be wrong?

@vijaysahal4556 2 жыл бұрын

Very nice sir 👍👍👍👍

@SirGibTV Жыл бұрын

very informative. i ave older version of excel. and i dont have =filter. can you pls teach us how to do it? .

@futuresuperstar4life 2 жыл бұрын

I NEED HELP! PLZZ im trying to do something similar but more specific i guess. im a server and ive been working on an excel file to track my tips and sales percentages tip outs etc, it's very complex and im completely new to excel so it took me like two months. i'm trying to recreate an app that i was using but doesn't track enough details. in this app there are tabs on top for sorting the data yearly, monthly, weekly and biweekly and "all" and then at the bottom you can toggle between all of the data with arrows, between the 2 arrows there are 2 dates based off of the filter (the corresponding dates of the month, the week etc) but youre not limited to just "this month" and "last month", but you can continue to toggle between all months by clicking the arrow. i've tried with slicers and it kind of works but not as efficient or specific as i'd like it to be PLEASE TELL ME if you think that is at all possible on excel

@darrylmorgan 3 жыл бұрын

Great Tutorial On The Simply Awesome FILTER Function...Thank You Sir :) P.S Still Get A Headache Looking At The Scary Old School Formula!!

@TeachExcel 3 жыл бұрын

Thanks Darryl! And, man-o-man, I honestly didn't want to include it until I remembered that I did a whole tutorial on it in the past (I must have been crazy to do that hahaha).

@darrenburke9301 2 жыл бұрын

Hi, thank you for your teaching skills. Mine all works perfecty, but if i add Last 7 Days to dropdown and open and close, i get no sales. I checked the TableRaw and made sure their are dates in the last seven days, as i said all the other functions work EG.... this month, last month, this year, last year. I used =TODAY() - 7 , I get no report also tried =TODAY() - 14 and no results, missing something. Thank you guys

@vikaasb2016 3 жыл бұрын

Awesome 🙏

@TeachExcel 3 жыл бұрын

Glad you like it :))

@mirrrvelll5164 3 жыл бұрын

Nice video man, this is truly useful. Gonna save it in my excel folder =)

@mohamedezequiel9458 3 жыл бұрын

pro tip : watch movies at Flixzone. Been using it for watching all kinds of movies these days.

@mustafazion1737 3 жыл бұрын

@Mohamed Ezequiel Yea, been using flixzone} for years myself :)

@j.rjunior5584 Жыл бұрын

Can you doo this if you want too see the overall "general data", and still be able to filter out what particular year you would like too see.

@lisaniv3228 Жыл бұрын

Hi new sub here. Just have one question, is there a way to lovk a column after each day has passed? Thanks in advance!

@AshGeo 2 жыл бұрын

I thought I was the only one hiding stuff to make it look better. Btw, what happens if you print the entire sheet? Does the hidden columns and data in white font get printed?

@darcybolter5977 Жыл бұрын

I giggled when you titled your sheets R2 D2 :)

@makubexho Жыл бұрын

What’s so funny about it?

@robdf9024 10 ай бұрын

Hi, thank you for the interesting video. When I enter the formula with the "FILTER", the D2 gives me a #NAME? sort of error. It is definitely because I am using Excel 2016. The other thing is, when I use the IFERROR formula on the last part of your video, the formula does not populate the cells even if re-copy and re-paste all the formulas on the extent of the worksheet. But thank you.

@karenlopez1931 Жыл бұрын

hello, I did it but only the first row is showing, i can see the formulas below but it is not showing the data. what am i doing wrong?

@KatsudonArt Жыл бұрын

hello, thank you for this tutorial. I would like to do something similar but the drop down would be "total year" then the name of the months. I can't wrap in my head what formula should I do. By the way, happy birthday to this video :D

@RonniePandaog Жыл бұрын

Do you have any idea on how to get This week and Last week data?

@dristspencer669 2 жыл бұрын

How would you zero in on a year you want to find specifically, such as having various years in an Excel Sheet with a lot of 2002, 2005, 2017, and 2021. You want to find the date 2021 specifically and highlight it all in a different color.

@tishshah7417 3 ай бұрын

how can i dynamically analyse off of this information?

@moesadr3342 Жыл бұрын

Any chance you could let me know how to add Quarters as well? This Quarter, last Quarter please?

@peachieangie Жыл бұрын

This was a good tutorial but you lost me around the 17:55 mark because I'm using Google Sheets to budget (Excel's great but I need Sheets so that I can edit where ever I am, not just my personal computer). It's not as easy as Excel to create or name a table, I don't know where I went wrong because it keeps giving me error messages. Do you have explanation videos using Google Sheets? Thanks!

@nunyabusiness4516 Жыл бұрын

having an issue, my cell displays N/A even though I did it exactly as you have so easily explained...hmmm?? =VLOOKUP(E3,TablePeriods,2,FALSE). Can you provide some sort of explanation so I can fix this? This will make my life so much easier...

@andreagaddi3595 3 жыл бұрын

Why not using advanced filter with old version ?

@chaudharywaqasahmed4561 2 ай бұрын

I made IF Error INDEX formula, but no data is showing at the place of formula.

@freevoicebd Жыл бұрын

I have copied formula in all cells. But from second row result blank. What wrong here? I have followed your excel file also. My Excel verison 2010

@sheilasmith5389 3 жыл бұрын

Can you demonstrate or explain formulas using fiscal months and years, instead of calendar years?

@TeachExcel 3 жыл бұрын

I probably won't have to ability to fit such a tutorial in for a while but you could ask for some help for this on the forum on TeachExcel.com - I would also play around with the functions that I show you in this tutorial though because they can be used to make a wide range of date periods without tooo much difficulty.

@arnulfocapuli7819 2 жыл бұрын

Good jub hehehe, i like when zooming, please create simple pos program,

@viharraut1181 2 жыл бұрын

The =filter option is not showing up anyone has idea how to get

@revilotnek1610 6 ай бұрын

How to do this in Google Sheets?

@JAYSONFERANIL Ай бұрын

how about specific date?

@emmanuellesabino1496 3 жыл бұрын

sad that my excel version still dont have the filter formula :'(

@TeachExcel 3 жыл бұрын

I wish they would give an update for older versions and just allow it to be used in all of them back to 2007 - would be amazing! If you would like any help implementing the older formula in my tutorial, you can feel free to ask in the forum on TeachExcel.com and you can upload a sample file there as well.