How to make automatic calendar in Excel

Creating a new calendar each month is a waste of time, but many people do as they think they need to rebuild it to get correct weekends and public holidays.

But in fact, with few formulas, 2 conditional formatting rules, 1 dropdown list and 5 lines of VBA code, you can create an automatic calendar.

Step 1: Add a dropdown list

Let's start by writing the employees' name in column A

Do you have the developer tab in your Ribbon? No, so

  1. Go to the menu File>Options
  2. Then Customize the Ribbon
  3. Check the developer option in the right pane

Now, select Developer>Insert>Combo Box

Then left click and drag your mouse to create a drop-down objet in your worksheet like in this picture.

Create a list of months

Now we need to create a list of month somewhere else in our worksheet (use the technique to fill series for that 😉)


In my case I have to create this list 32 columns away from my source data (maximum number of day per month + the column of the employees' name).

Now, connect the Combo Box with this list of month.

  1. Select your Combo Box object
  2. Right-click on it
  3. Select Format Control ...

The Format Control dialog will open.

  1. Select the Control tab (the last one)
  2. Select the range of month (in this example AH1:AH12
  3. In the Cell link, select A1 (explanation, just under)

What is the cell link?

The Cell link is the cell that will collect the value of the index of the element selected.

  • If you select May, the 5th element in the list, the value in A1 will be 5
  • And if you select September the value in A1 will be 9
  • ...

Why choose A1? It's tricky 😉 In fact, the drop-down object is above the cell A1. Like that, the end-user will not see the result of the cell linked.

The location of the Cell link is crucial for the rest of the explanation. It has in impact on the formula DATE and with the macro.

Step 2: Another Drop-down list for the year

On the same way,

  • Create a list of years in a column (it's just the value of the year, not a date)
  • Insert a new drop-down list
  • Link this new drop-down list to this range of year
  • Select A2 as a Cell Link

Step 3: Create the first date in function of the drop down

Here, we are going to re-used the contains of A1 (month selected) and A2 (year selected) and put them in the DATE function. If you are not familiar with the DATE function, have a look at this article.

So, let's create the first day in function of the value of the drop down object.

  • The year it's the value of the cell link A2 + 2016 (yes, the first year in the list is 2017, so the result in A2 will be 1 and not 2017)
  • The month is just the value of A1 (easy)
  • And the day is 1 (first day of the month)

In B6, the formula is


Step 4: Extend the days

This step is very easy.

We just have to add 1 to the previous cell to increase your series of days.

  • In C6, write the following formula
  • Copy the formula till the cell AF6


Step 5: Change the date format

Now we are going to focus on the date format.

We already have the year and the month in the upper left corner, so we don't really need this information in our cells. What we need, it's just to display the days of our dates.

To do this, we can change the format of the date in the row 6 to display only the day. We want to display the short name of the day and also the figure.

  • Select all your dates (from B6 to AF6)
  • Open the Format Cells dialog box (Ctrl+1 or Home>Cell format>More Number format)
  • Select the Custom category
  • Enter ddd dd in the Type field.

Step 6: Change the orientation of the text

Now let's change the orientation of our date:

  • Select B6:AF6
  • Activate the menu Home
  • Orientation
  • Rotate Text Up

Then, adjust the size of the columns B:AF to get this presentation.

  • Select columns B:AF
  • Right click a the column header
  • Set the column width to 2.5

Step 7: Add color and borders

Now, add some color and borders to your calendar. Pretty nice 😍😍😍

Step 8: Add a dynamic title

Now, we are going to create a dynamic title with the first and last day of the month.

The formula for the first day of the month is


The formula for the last day of the month is


And to customize the result, we must include these 2 formulas in the TEXT function. The parameter of the TEXT function is the format of the date you want.

="Period from the "&TEXT(DATE(A2+2016,A1,1),"dd mmmm yyyy") &" to the "&TEXT(DATE(A2+2016,A1+1,1)-1,"dd mmmm yyyy")

And the result is:

Step 9: Highlight the weekends

Now, here is the most interesting part of the workbook. We are going to highlight automatically the week-end in the calendar

  1. Select all your data with the first row of your calendar (range B6:AF13)
  2. Create a conditonal formatting rule (Home>Conditionnal Formating>New rules)
  3. Select Use a formula to determine which cells to format
  4. Write the formula =WEEKDAY(B$6,2)>5
  5. Change the background color (orange in this example)

The WEEKDAY function returns a value between 1 and 7 corresponding to the day of the week. So, we build a test with this function to know if the day is a week-end of not.

Very, very, VERY IMPORTANT ⚠⚠⚠ There is only one $ in the formula after the column (B) and before the row (6).

This is because, the condition must always "read" the value of the day. Read this article about the reference mixed or this one about the reference absolute to know how to use the $ in a formula.

Step 10: Highlight the public holidays

For the public holidays, you need to create in a new worksheet, a list of the public holiday of your country

Then, repeat the same 5 previous steps but change the formula

  1. Select all your data (cells B6:AF13)
  2. Create a conditional formatting rule (Home>Conditionnal Formating>New rules)
  3. Select Use a formula to determine which cells to format
  4. Write the formula =COUNTIF(Holidays!$B$2:$B$4,B$6)>0
  5. Change the background color (red)

Here, we use the COUNTIF function. If one of the day is in the list of public holiday, the formula returns 1. So, we test if the formula returns a value greater than 0.

Step 11: Hide the last columns with a macro

To finish, we must create a macro.

The calendar is build with 31 days. So if we selected a month that hasn't 31 day, the calendar will not be correct. So we are going to create a macro that will hide the days not include in the selected month.

  1. Press Alt + F11 to open the Visual Basic Editor
  2. From the Insert menu, choose Module
  3. Copy Paste the following code in your module
Sub Hide_Day()
Dim Num_Col As Long
   'This instruction cleans the content of the cells in your calendar
   For Num_Col = 30 To 32
   ' Test if the month of the cells (row 6) is the same of the month selected (cell A1 or cells(1,1))
     If Month(Cells(6, Num_Col)) >= Cells(1, 1) Then
        Columns(Num_Col).Hidden = True
        Columns(Num_Col).Hidden = False
     End If
End Sub

Step 12: Link the macro to the drop down objets

To finish, we link the 2 drop down objects to this code.

  1. Select the Combo Box
  2. Right-click on it.
  3. Choose Assign Macro
  4. Select the name of the macro (Hide_Day) and click OK

Step 13: Your calendar is ready

And THAT'S IT 😎😍👍

Now, each time you select a month in your drop-down list, your calendar will automatically change the color of the weekend, holidays (providing your list is up to date, of course) and also hide columns as necessary.

Related posts

Permanent link to this article:


Skip to comment form

    • Deepika on 13/08/2019 at 07:58
    • Reply

    Hi, Could you also please share the code for to retain the data that has been input into cells when months are changed?

    It would be really helpful !!

    1. No because this is a full project with a lot of lines of code. Also, each situation is unique and I can't write a "standard" code.

    • Eteruza Medas on 29/07/2019 at 17:27
    • Reply

    Hi Frederic, I was unable to have the macro hide and unhide the columns (I rechecked and couldn't find the problem). I am also unable to retain the details in the individual months (whenever I return to a previous month, the macro cleans the contents). How can that be changed?

    • Adriano Rodrigues on 01/07/2019 at 17:03
    • Reply


    • Varun on 26/06/2019 at 00:29
    • Reply

    Hi there, awesome video!! I would to like to keep the cells populated in each month and not have it clear the contents after selecting a new month / year. Is that possible and if so, please can you describe? Thanks in advance

      • Kate on 08/07/2019 at 05:14
      • Reply

      I also have the same problem with this one. When I type in any cell through the month but the old content is still there. Please help. Thanks in advance.

    • khomol on 13/06/2019 at 21:14
    • Reply

    Hi. Brilliant post, very clear and easy to follow, I have even customised it to my needs. But I need help to make a google spreadsheet version, any suggestions how to do that?

    1. Thanks but Excel Online is better than Google sheet and free too.
      All the steps are possible with Excel Online except the dropdown.

    • lauren on 12/06/2019 at 10:18
    • Reply


    Is there any way to keep the data just in that current month? I am using it to track employee sickness days and when you change the month it brings the data from lets say April into May.
    Thank you.


    1. Hi Lauren, Yes it's possible but it's a development (macro and formulas) and it takes time.
      Because, not only you have to manage to save each day of sickness and the way to display them in function of the month selected but also, you must manage an interface to update or delete previous data.

        • Jay on 29/07/2019 at 16:15

        I also need the same function! Please make a tutorial to save the information under each month drop down!

      • Dante on 26/06/2019 at 03:33
      • Reply

      The calendar is brilliant. But I wish to have this function as well so I can track sick leave/ vacation leave. Basically retain the previous month's data.

    • WINSTON T CUISON on 08/06/2019 at 09:56
    • Reply

    Is there anyway i can save the previous data when i switch to another month? For some reason when i come back to the previous month i lost all the data. Is there anyway i can fix this?

    1. Yes it's possible but it's a real project with development. One or 2 days for me

    • Gerrit on 31/05/2019 at 13:45
    • Reply

    Can i download this calendar please?

    1. Hi, it was possible to download it with Paypal long time ago but it was too difficult to manage the automatic download with their Platform. So now, it's not possible to download the file. Sorry

      • Lorraine on 07/06/2019 at 14:55
      • Reply

      When I enter text in one month, it transfers to the next month - how do I stop this from happening?

      1. I don't understand when you say "When I enter text in one month". You don't have to enter text but just select the month in the dropdown list

    • sandeep kothari on 21/04/2019 at 08:31
    • Reply

    Great post!

    • Micula Aurel on 31/01/2019 at 09:56
    • Reply

    I did the same as in exemple, except the macro couse in the first cels were you have link the mounth and the year i have to put the company name and adress wich is mandatory, so i had to move the linked the mounth and year to AG 1 and AG 2, and here is my problem, i try to change the macro but didnt work , may you please help me or tell me what to change in macro to work ?
    Thank you in advance
    Regard the rest of the stepts worked perfectly.

    • DRIN on 28/01/2019 at 14:42
    • Reply

    Hi Fred,

    How can I add name in the title bar using Com Box? I have a list of people for each month

    I want to show like this:

    ELDRIN - from January 1, 2019 to January 31, 2019

    Thank you,

    • DRIN on 23/01/2019 at 10:00
    • Reply

    Hi Mr. Frederic,

    Could you please explain the code you've shared on Step 11.
    As I change the month from January to February & February (to either of the month with less than 30 days) the hidden column will not appear anymore as it is always hidden even if I go to the month with 31 days.

    I changed this code:
    For Num_Col = 30 To 32
    For Num_Col = 32 To 34

    I used column from D7:AH70

    I tried also the same code you've provided, still the same scenario.

    Thank you,

    • Om goswami on 02/01/2019 at 18:31
    • Reply

    Thanks a lot sir
    I have found exactly what I want.

  1. in this calendar format i want to add week number... is this possible? if yes please let me know how can i make...

      • Gerrit on 31/05/2019 at 13:48
      • Reply

      Is your calendar reddy? Can i have a copy of it?

      1. Sorry no. I don't share it. It's better if you try by yourself

    • Maha on 29/09/2018 at 16:22
    • Reply

    I am still wondering if its possible to not clear the contents when moving the calendar forward.
    I use the calender as a way to keep track of my employees vacation.
    But when they type x in a date, lets says 1.7.2018. The x will then stay in that cell when i change month and year... Did you solve this?

    Anyone please?

    • kiran kumar ganapuram on 11/08/2018 at 13:17
    • Reply

    Hi good evening,

    Thank you for providing such an wonderful article.

    I tried my level best in preparing one of the daily update sheet for my employees based on the inputs provided by you. But when I am writing something in the month of January the same thing is repeating in all the months unchanged. How can I erase or hide the content of that particular month and start afresh in next month.
    Kindly, help me in this regard.


    • Kara Anderson on 12/07/2018 at 17:11
    • Reply

    Dear Frederic,

    Thanks for this fantastic video and help. I and wondering if its possible to not clear the contents when moving the calendar forward.
    For example when my team enters their availability for July 2018 and August. Switching back to July all the contents clear.

    1. The workbook doesn't save the previous values. To do that, it is compulsory to create a real program and it's not the purpose of the post.

        • Praful on 24/07/2018 at 15:16

        Can you please share the code to public.

      1. Which code ? It is in the post

    • Summer on 31/05/2018 at 13:41
    • Reply


    I have successfully made an automatic calendar (for meeting purposes) using your tutorial, however when I try to add the meeting title into the row below dates, in the correct date column it doesn't seem to change when the month changes it stays in the same position across the whole calendar.

    I hope this makes sense could you direct me on how to fix it please?

    Many thanks

      • Stian on 05/06/2018 at 10:24
      • Reply

      I use the calender as a way to keep track of my employees vacation.
      But when they type x in a date, lets says 1.7.2018. The x will then stay in that cell when i change month and year... Did you solve this?

      The tutorial was great btw.

        • Michelle on 22/06/2018 at 09:35

        I'm also having the same issue! I've spent so long working on this and I cannot work out how to get this to work!

      • Stian on 05/06/2018 at 11:14
      • Reply

      I have the same problem.

      I use the calender to keep track on my employees vacation. But when they type in "x" in a given cell. It follows that cell when i change month or year.

      Hope there is something we can do about this.

      Many thanks for your website.

    1. Hi, I have added the code to erase the content of the calendar when you change the month

        • James Layzell on 28/06/2018 at 14:46

        Hi, thank you for the great template. I want to use it to book functions/events in the future, how can the macro be adjusted to allow me to move into different dates and years without loosing the information? At the moment when I move from June to July, all of the information in June disappears, removing the ClearContents instruction results in the information appearing in any month you select.

        Many thanks for your time.


        • Danielle on 03/04/2019 at 21:50

        Hi Thank you for the post! It has been really helpful... I am having the same issue when changing from Jan to Feb... All of the information from Jan will past into Feb.

        Thanks in advance,

      1. That's strange because the instruction to clear the contain of the range B7:AF13 is written in the code.

        • Noelle Davis on 24/04/2019 at 14:28

        Hi Frederic, thanks for the tutorial. Is there something we can do to retain the data that has been input into cells when months are changed? I am using this as a communications planner and would like to be able to retain the data as we switch through months. Is this a possibility?

      2. Hi Noelle,
        Yes it's possible but it's a really program to manage new entries, change values, delete previous record and display previous month values.
        For me it's minimum 2 days of development.

    • Bettina on 02/04/2018 at 21:58
    • Reply

    Hi There,
    Many thanks for the tutorial.
    I've made my own version but can't figure out how to do step 11 (Hide the last columns with a macro).
    In my case I didn't make the calendar in columns, but in rows, and made a different layout than yours.
    I can't figure out how to do make the macro so it can work on mine.
    Could you help?

    Thanks in advance

      • Bettina on 11/04/2018 at 09:03
      • Reply

      Hi again,
      I still haven't had a breakthrough with my previous answer. Could you please help me?
      You can also write me an email, so you don't give away some of the answer for the paid part.

      Thx again

    • Zeno Dsouza on 01/04/2018 at 10:20
    • Reply


    Let me start by saying that this is an excellent template and your instructions were quite easy to follow thanks for making it so detailed,

    however I did have trouble trying to change the formula for the weekend highlight see where I am the weekends fall on a Friday and a Saturday can you advise what I should do in this case?


    • brooke on 19/03/2018 at 15:01
    • Reply

    The calendar does not automatically update the days within the months. I have tried to mess around with the formulas; however, everytime i chose a month from the drop down the days stay the same and nothing changes

    1. There is no reason. If you have properly follow the explanation it works. Maybe your DATE formula is not linked to the dropdown cell.
      Also, the solution is in the file you can download at the end of the article

    • Frede Rahbek Jensen on 16/03/2018 at 11:31
    • Reply

    Don't know if it's Excell 2016 or maybe because i use Danish version, but i had to replace , with ; in the formulas. 🙂

    1. That's the setting of your computer. Control panel>Regional setting and there is one option to change ',' by ';' (or the other way round)

  2. Hi There,
    does this method for creating a calender automatically accomodate february when 28 days vs 29 days.
    or what is the code to add to compensate and visibility when february is selected to only show the dates required for the full month?

    • Sumit on 20/02/2018 at 05:06
    • Reply

    It's superb and also i have created the same but if i will change the month data against the employee still the same how that will get change?

    • Lucie on 11/01/2018 at 14:04
    • Reply

    Hello Frédéric,

    thank you for great calendar. I've added here also combo box with years. Unfortunately years are read as serial numbers.
    E.g. my list of years starts from 2018, it got serial number 1, and is read as year 1901

    Could you give me a hint how to solve that?


    • Dave on 11/01/2018 at 03:09
    • Reply

    Great Calendar and I have it working to high light weekends but have spent 3 days and can not get the stat holidays and macro to work is there a copy where all of this is built, I am not a technical IT type but I do try. Thanks

    1. Hi Dave,
      I have added the file. It is included in the pay-per-view part

    • shamik on 08/01/2018 at 18:47
    • Reply


    i really liked you design, can you share the template ?

    1. It has been made from scratch and it's not downloadable.

Leave a Reply

Your email address will not be published.