Excel - Create an Event Calendar in Excel | Microsoft Excel Tutorial - Episode 717
Hellow guys, Welcome to my website, and you are watching Excel - Create an Event Calendar in Excel | Microsoft Excel Tutorial - Episode 717. and this vIdeo is uploaded by MrExcel.com at 2009-02-18T06:07:45-08:00. We are pramote this video only for entertainment and educational perpose only. So, I hop you like our website.
Info About This Video
Name |
Excel - Create an Event Calendar in Excel | Microsoft Excel Tutorial - Episode 717 |
Video Uploader |
Video From MrExcel.com |
Upload Date |
This Video Uploaded At 18-02-2009 14:07:45 |
Video Discription |
Microsoft Excel Tutorial: Create an Event Calendar in Excel.
Welcome back to the MrExcel netcast! In today's episode, we will be tackling a great question from Matthew, who manages a club in Washington, D.C. Matthew needs a way to easily see which days the club is reserved for events. So, we will be building an event calendar that highlights the reserved dates.
To start off, we will insert a new worksheet and enter a Sunday date. Then, we will use a formula to add one to that date as we go across and copy it across seven cells. In the next row, we will take the date above and add seven, and copy that down for however far we want the calendar to go. This can be for a few weeks or even the entire quarter.
Next, we will do some formatting to make the calendar look more like a traditional one. We will change the font to a smaller size and use a custom number format to show only the month and day. We will also align the dates to the upper right corner and add borders to make it look more like a calendar. Lastly, we will make the cells taller to give it a more square shape.
Now, the challenge is to highlight the reserved dates on the calendar. Normally, Conditional Formatting does not allow us to refer to cells on another worksheet. However, we can overcome this by creating a named range for the dates on the Events worksheet. Then, we can use this named range in a conditional format on the Calendar worksheet to highlight the reserved dates in a different color.
To test if this works, we can add a new date on the Events worksheet and see if it automatically gets highlighted on the Calendar worksheet. And sure enough, it does! As time goes by and more events are added, the calendar will automatically update to show the next 13 weeks.
Thank you for tuning in to this episode of the MrExcel netcast. I hope this solution helps Matthew and anyone else looking to create an event calendar in Excel. Don't forget to subscribe to our channel for more helpful Excel tips and tricks. See you next time!
Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/
You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-on-youtube/
Matthew from Washington wants to take an event database and create a visual calendar that shows availability.
While this sounds simple, it requires several steps. The end result is fairly cool. Episode 717 shows you how.
Table of Contents:
(00:00) Introduction
(00:20) Building a Calendar
(01:17) Formatting the Calendar
(02:12) Highlighting Booked Dates
(03:47) Testing the Calendar
(04:32) Clicking Like really helps the algorithm
#excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial
This video answers these common search terms:
Automatic updating of the calendar as time goes by
Copying and pasting formatting in Excel
Creating a named range for referencing cells on another worksheet
Excel tutorial for adding formulas to automatically update the calendar
How to highlight reserved dates in a calendar using conditional formatting
Next netcast from MrExcel
Step-by-step guide for creating a calendar with reserved dates
Testing the calendar by adding a new event
Thanking the viewer for watching the video
Using conditional formatting to highlight cells with values
YouTube video tutorial on creating a calendar in Excel
Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads/1152190/ |
Category |
Education |
Tags |
Excel | business | accounting | spreadsheets | tutorial | technology | MrExcel | Calendar | conditional formatting | Named Ranges | Formula | Bill Jelen | Learn Excel | Excel Tutorial | Power Excel | Microsoft Excel | Excel Tips | Data Set | Excel Help | Learn Excel with MrExcel | Excel Tips and Tricks | MrExcel Podcast | mrexcel channel | mrexcel website | mrexcel forum | excel guru | mrexcel tips and triks |
More Videos