Create Easy Yearly Calendar in Excel and Sheets with a SINGLE Formula
Вставка
- Опубліковано 5 лип 2024
- Join 400,000+ professionals in our courses here 👉 link.xelplus.com/yt-d-all-cou...
In this easy-to-follow video, we guide you through the process of creating a dynamic, interactive yearly calendar using just one formula in both Excel and Google Sheets. This tool is perfect for anyone looking for a customizable calendar template.
⬇️ DOWNLOAD the workbook here: pages.xelplus.com/dynamic-cal...
🔑 Highlights:
▪️ Learn to make a yearly calendar that updates with a change in the year.
▪️ Discover how to use the SEQUENCE function for an entire year's calendar.
▪️ Understand the use of the DATE and WEEKDAY functions to identify start dates.
▪️ Improve readability with conditional formatting for better visual appeal.
▪️ Find out how to distinguish different months with color coding.
With this calendar you're able to input the year in a cell and the calendar will update automatically for you. To make the calendar even better I use Excel conditional formatting to color every other month in a different color (MOD function can be used here to identify every other month).
You can also use this Excel calendar in your dashboards. It's interactive and you can combine it with data entry for the year.
00:00 Yearly, Dynamic Calendar in Excel and Google Sheets
00:42 SEQUENCE Function for a Yearly Calendar in Excel and Sheets
02:22 DATE and WEEKDAY Function to Identify the Start date
06:11 Conditional Formatting to Improve Readability for Yearly Calendar
Video on MOD Function: • Excel MOD Function - E...
Playlist for Excel 365 new formulas and functions: • Excel for Office 365 &...
➡️ Join this channel to get access to perks: / @leilagharani
👕☕ Get the Official XelPlus MERCH: xelplus.creator-spring.com/
🎓 Not sure which of my Excel courses fits best for you? Take the quiz: www.xelplus.com/course-quiz/
🎥 RESOURCES I recommend: www.xelplus.com/resources/
🚩Let’s connect on social:
Instagram: / lgharani
LinkedIn: / xelplus
Note: This description contains affiliate links, which means at no additional cost to you, we will receive a small commission if you make a purchase using the links. This helps support the channel and allows us to continue to make videos like this. Thank you for your support!
#excel
Grab the file I used in the video from here 👉 pages.xelplus.com/dynamic-calendar-file
Your channel is the first and only place I go for Excel help. Everything is so clear and concise. Stuff that used to take me hours to figure out takes ten minutes now. You're a hero in my book!
Anyone ever told you.... "You r the best teacher on the globe?" Keep up the good work. Really appreciate all your efforts.
That's really kind! Thank you for watching and commenting.
Liela create a channel on VBA..🙏
I've learnt more about date management in excel in these 10 minutes than in 10 years of no-brain use of it. Thanks!
I have been watching many excel help videos, your teaching is the most clear and understandable! I really appreciated! From now on, I will keep on watching your videos! Love it!
Love it Leila. What a difference from creating calendars dynamically in the past. Thanks for the great video as always.
Thanks so much! 😊
Leila, you deserve all the money in the world for helping so many people!!
Awesome, didn't know that this is possible with only one function.
Thanks for sharing and for your amazing teaching skills!
Wow, I just discovered your videos and already watched 4 today - super helpful and extremely well done! Thank you!
I am going to start binge watching your Excel and PowerPoint videos! You are awesome! Thank you!
Nicely done, Leila! Easy to follow, logical, and articulatley presented. Thank you.
Glad you enjoyed it, Nathan!
What I really like about this & other video of yours is that the learning one gets. More than the main concepts there are minor things e.g. conditional formating nuances etc.. in this one. Very well done !!
Awesome, thank you!
It is just so simple to understand after playing it over 20 times slowly. Thank you.
Really useful, Thanks again Leila!! Amazing O365 improvements!
Oh my. Your beautiful brain is solving so many problems that I use to solve by excruciating sessions trying to learn special skills in Excel.
Amazing .. watch any number of times ... your learning skill is excellent ... a big thank you again
I have soooooo much to learn in life... and only fewer than about 30 years to learn it in! Every time I watch your videos and other such on You Tube, I realise that I know nothing!
Many thanks...
You're the best Leila! Thank you for teaching us in a great way 🙏
Your voice is very soothing and pleasant to the ears! You’re a very good teacher and trainer. Wow! More power! 👏👏👏
Hi Leila, do you have videos which may be helpful in monitoring stocks in excel? Thanks a lot in advance.
I always thought it was very complicated to make an excel calendar. But you showed in this video that there is a very simple and easy way to create the calendar. Thank you Leila.
You're very welcome, Jean!
Loved the music in the end - Relive uses exactly the same tune :) Joke aside, Leila taught me to use excel more efficiently in a few videos than what I learned since Office 2.0 in 1996 or something! Another excellent and simple video! Thank you
Leila your videos are truly great and are helping me a lot. You make everything super easy.... A big thank you
You are So inspirational Leila... God Bless You!!!
this is incredible . i never knew that excel can have a calendar with just a formula o.my.god thank you so much leila . you put a lot of effort on these tutorials proud of you . wow 👍👍
Glad you like it, Mark!
@@LeilaGharani i'm of couse leila
Whenever I watch your video, whenever I learn something new. Thanks a lot Leila.
Happy to hear that!
I am humbled by your knowledge and motivated to learn more. Thank you!
WOW! This is awesome! So many calendars I've made over the years that were a lot more manual. Thank you!
You are so welcome!
Fantastic tutorial. I learned several new things on this one. Thanks again Leila!
Glad it was helpful!
This is simply a great way to create a calendar which was frustrating till now. Your presentation is awesome. Thank you so much!
Most welcome 😊
I’ve never learned from someone who explains themselves so clearly. Thank you Leila!!
Glad it was helpful! Thank you.
I have some questions about excel and power bi what is carrier level of excel and power bi ..What is level required to got job on these two technology's iam already 1 year experience in excel but I idea what I can do for got job in Karachi Pakistan or others country please suggestions me leela or any one
Liela create a channel on VBA..🙏
Stumbled upon this video today. It didn't take much time after watching the video to subscribe. Thanks for this so interesting and informative video.
I always like your innovative way of thinking for using functions and formula
Thank you so much for this! You are seriously the best!
I’ve always loved Excel really , but all the tricks I learned from you add much flavours to it!!! Thank you
Glad it was helpful! Thank you.
Awesome Leila! This is really helpful. Your style and explanations are excellent!
Thank you so much, Charles!
Genius.
Such brilliantly constructed videos too.
You explain very clear and at pace anyone can keep up with, thank you from a Newbie.
Brilliant! I honestly had my doubts that you could do it with one formula....but I had never used the Sequence function before: thanks for introducing me to that function.
Plus the use of multiple conditional formatting rules to add dynamic formatting was a very sweet touch.
THANK YOU SO MUCH!!!! Excellent presentation...!!! You are a great teacher!!!
Thank you Leila. A great formula update to create a yearly calendar, with a nice CF for the different years.
You're very welcome, Steve!
Thanks Leila! My day is not complete if I don't learn something from you! :)
It’s really nice and time saving, Tank you Leila
Thanks for this channel. I recently started learning excel in.my old age. And I am sure I will soon become pro in excel by seeing your channel tips and sessions on excel. This is helping me a lot in excelling in my job. Thanks for becoming an idol for old age learning students who somewhere struggle with new generation on these new tricks..
AGAIN THANK YOU VERY MUCH FROM THE CORE OF MY HEART..YOUR SUGGESTIONS ARE HELPING ME IN EARNING MONEY..
I watched the entire video and the last few frames were good to hear since I am using Excel 2019 . I practiced the same in google sheet.. thanks
There were multiple banners explaining it's in 365 including online but glad it made sense eventually. I'm annoyed MS doesn't allow some functions like queries in online 365 but doesn't in 2019, but that's a different conversation
Wish I found your videos a lonnng time ago. I’ll be retiring soon but always want learn.
Your video are great for everyone-non power user and power user alike.
Stay well.
Leila, that's an amazing idea to apply dynamic arrays to generate calendar! Great great application of dynamic arrays!
Glad you think so!
Yeah, it’s amazing how much you can do in one cell now. With the addition of the LET function as well, you can do a lot more with a lot less.
Very true Andrew....
This is SO AWESOME!! Thank you for sharing these extremely useful tips!
You're so welcome!
Smart as usual, Leila
Thank you!
Brilliant Trick!
Many Thanks
Wish you be always Healthy & Happy:)
OMG, I'm an old guy and this was like drinking from a fire hydrant! It really helped when I updated my version of MS Office! This stuff is fantastic! Thanks so much!
These tutorials are amazing, thank you.
Thank you very much. I learn a lot from your tutorials.
thanks Leila, smart info from a beautiful teacher!!
Awesome, Awesome, Awesome. Your videos demonstrate platinum standards of simplicity !!
Thank you very much!
This is brilliant, Thanks...
They say "imitation is the highest form of flattery", so I used your ideas here to create a single month view where the user inputs the year but also selects the month (in a separate cell) using a simple form spinner set from 1 to 12. I also used conditional formatting to get it to place a border around / make bold today's date.
I'm very pleases with the result
WOW! This seems like what I am looking for, could you guide me if I can do weekly planning of resources of vehicles like this? So the user could click between weeks but still input information mainly in current week?
Why does anyone give it is thumb's down. I should stop by once a week. I needed this calendar. Thank you!
Thanks for stopping by now!
You change my life on a weekly basis!
Brilliant as always! Love your videos. Keep it up 😊
Thanks so much! 😊
Hello there, thanks for the tutorials you post on youtube for us who wants to learn and learned a lot from you. Great channel, as a subscriber i am looking forward for every new excel tutorial you make in the future. In the mean time , i will watch all the tutorials u made so far, always excited to learn something new.
Our pleasure! Hope you'll enjoy the videos!
This is so easy and useful. Thank you. You are the best excel tutor👍
Glad you think so!
love it! Once again, you have killed it Leila 👏 ♥
Thank you Rory :)
every time the videos teach me something! good work!
I never heard of the Sequence function until a week ago and now I see it everywhere. Its sooooo useful.
It really is :)
So handy and step by step explanation.
Thanks for sharing
I'm lucky to find you today on UA-cam, thanks for all your beautiful videos, you are amazing, wow! Hello to you from Australia
Wow, thank you!
Wow, that's a really well-explained video - thank you!
Glad you enjoyed it, Chris!
This is super easy and so helpful. Great video. Thanks a lot!
Great to hear!
Very cool! I liked the conditional formatting at the end. Nice touch.
Thank you! Cheers!
Great tutorial! Thanks for sharing this with us!
Nice use of SEQUENCE, love these dynamic array functions!
Many thanks Doug!
whoa! Didn't think it was that easy. Thanks for the clear explanation✨
You're welcome 😊
Excel is a tool very powerful and you are a very powerful teacher :-)
incredible! thank you for sharing ! Now I know two additional functions in Excel!
Glad it was helpful!
Thanks Leila. That's a great use of formulas to achieve the desired result..
Glad you like it Matt.
Wow, amazing. Thank you for the video.
Awesome. And your video effects are getting better and better.
Glad you think so, Felipe!
Absolutely brilliant. Thanks Leila you never disappoint
My pleasure 😊
So clearly for teaching. You got a new subscribed, congrats👏🏻👏🏻
Welcome aboard!
Great video it works, I have added Week No. by entering in cell I5 =WEEKNUM(F5,21) then paste in to column
Brilliant. Every day I learn a new things from u. You are best teacher. Keep it up. Best luck 🤞
Thank you so much 😀
Thanks! Really great information as usual - added this calendar hack to my bag of tricks.
Great! Hope it'll come in handy.
Excellent video. Will definitely make use of this in project
as usual you teaching me a lot, thank you so much.
My pleasure!
just love the way you explain. cheers
Thanks! Btw i dont click thumbs up button during the end of video,because i always first hit thumbs up button and i watch.i m confident that your videos never dissappoint.
Thank you for your trust Mohamed.
Thanks a lot. May you live longer. I have learned a new trick from you and I'm going to try it with my students coz I'm an IT Trainer at Saipali Institute of Technology and Management here in Uganda.
Excelent Teacher Ms Leila. I am a Teacher too and i would love to be as good as you. With your simple explanation i made a automated sheet that each monthly took +- 30min to configure. Now I only need less them 10 sec. Wonderfull!...
Wow, thank you Manuel!
Super! Thank you Leila.
Nice job. Thanks for the formula.
Thank you so much Leila, it is another super helpful video.
You're so welcome, Claire!
Eu achei bem inteligente esta maneira de gerar um calendário. Eu já fazia algo parecido, mas os dias da semana na parte superior do calendário se adaptavam aos dias... No seu calendário os dias sempre ficarão na ordem Dom, Seg, Ter, ..., Sex, Sab e as datas irão se adaptar a eles... Parabéns.... Bem pensado!
It was excellent! You are a brilliant teacher!
Thank you! 😃
Dear sis,
Thanks you so much for your shard this lesson, I always learn from you video. It help me alot.
Glad to hear that
Really appreciate your efforts
Thanks 👍
Fab video. Very useful. Thanks.
Hi Leila. Fun example.. very clever. I used =ISODD(MONTH(B5)) for the conditional formatting instead of MOD. Thanks for sharing. Thumbs up!!
Great alternative. Thanks for sharing, Wayne!
@@LeilaGharani How did you do this? My version of 365 does not have capability to create New rule.
Brilliant as always - thanks :)
Excellent
Great Teacher in UA-cam
Amazing. You are great. Thanks for sharing. 😊