For our daily busy life schedule, the calendar is a must needed tool. We have wall calendars in our rooms or pocket calendars on our mobiles or watches. But making an interactive calendar in **Microsoft Excel** is fun, and the result is really soothing. By changing the month or year in an interactive calendar, you can automatically adjust the calendar like in the following animation. In this article, I will show you how to make an interactive calendar in Excel.

## Download Practice Workbook

You can download the free **Excel** workbook here and practice on your own.

## 2 Easy Ways to Make an Interactive Calendar in Excel

In this article, you will see two different approaches to making an interactive calendar in Excel. In my first method, I will make a monthly interactive calendar. For my second procedure, you will see the steps of creating an interactive yearly calendar in Excel.

For both procedures, I will use the following sample data set. Here I have two sets of data. In the first set, I have the list of federal holidays for the year 2023. And in the second set, you will see the list of months in a year.

### 1. Making Interactive Monthly Calendar in Excel

In my first approach, I will show you the process of making an interactive monthly calendar in Excel. By applying some formulas and formatting in Excel, you can accomplish this task. To make the calendar, follow the below-given steps.

**Step 1:**

- First of all, in a new sheet, make four fields for user inputs and name them like the following image.
- As I am making the monthly calendar for the year 2023, type that in the year field.

**Step 2:**

- Secondly, select cell
under the month header and go to the*C5*tab of the ribbon.*Data* - Then, from the
group select*Data Tools*.*Data Validation*

**Step 3:**

- Thirdly, under the
dropdown select*Allow*, and in the*List*type box, select the data range of the twelve months from the sample data set sheet.*Source* - After that, press
.*OK*

- Consequently, this will create a dropdown of twelve months in cell
, which will help in my further procedure.*C5*

**Step 4:**

- Fourthly, create a
table in the worksheet like the following image.*7×7* - Then, name the top seven columns of the table according to all the day names of the week, starting from
.*Monday*

**Step 5:**

- Fifthly, to make the calendar header a dynamic one, join the cell values of
and*C5*like the following syntax in the merged cell from*C4*.*B7:H7*

`=C5&" "&C4`

**Step 6:**

- So, the calendar header will look like the following image after the previous step.
- Here, the header will change, if any of the values from cell
or*C4**C5*

**Step 7:**

- In this step, insert the following formula of
in cell*the MATCH function*.*G4*

`=MATCH(C5,'Data Set'!$B$17:$B$28,0)`

- Here,
will search the cell value of*the MATCH function*that’s the month’s name in the*C5*cell range of the sample data set and will return the relative position of that month in that range.*B17:B28*

**Step 8:**

- Then, after pressing
, you will find the month number of cell*Enter*in*C5*just like the following image.*G4*

**Step 9:**

- In this step, I will determine the first day for each month in a year in the form of a number.
- Here,
will represent Monday,*1*will represent Tuesday, and so on.*2* - So, to do that, insert the following combination formula of
and*the WEEKDAY function**the*in cell*DATE function*.*G5*

`=WEEKDAY(DATE($C$4,$G$4,1),2)`

- Here,
will show the first date of each month of a specific year.*the Date function* - Then, the combination formula will return the first day for each month through
.*the WEEKDAY function* - Also, I want to start the week from Monday so, I will insert
as the return type in the formula.*2*

**Step 10:**

- Then, the formula will show the number corresponding to the first day for January 2023 which is
which indicates Sunday.*7*

**Step 11:**

- In this step, I will fill up the previously created table with numbers.
- For that, I will use the following formula of
in cell*the SEQUENCE function*.*B9*

`=SEQUENCE(6,7)`

**Step 12:**

- As there are
columns and*6*rows in the table, through the above function, the table will fill up by numbers from*7*serially.*1-42*

**Step 13:**

- In this step, I will turn these numbers into dates by adding a formula of
with the formula of the previous step.*the DATE function* - To do that, use the following formula in cell
.*B9*

`=DATE(C4,G4,1)+SEQUENCE(6,7)`

**Step 14:**

- So, you will get the result like the following image through the table by pressing
after inserting the formula.*Enter*

**Step 15:**

- As the outputs after inserting the formula, in this case, are not in the correct format.
- In order to modify them, select the cell range,
**B9: H14,**and choose thedropdown in the*Number Format*group.**Number**

**Step 16:**

- Then, from the dropdown, choose
as the format for the selected cell range.**Short Date**

**Step 17:**

- Consequently, the result after changing the format will look like the following image.
- Here, you can see from the following image, the starting date or day of Jan, 2023 is not correct in this case.
- So, I will modify it, so that the starting day of Jan 2023 becomes Sunday, according to the value of cell
which is*G5*.*7*

**Step 18:**

- So, insert the following formula in cell
, where I will subtract the cell value of*B9*from the previous value of this formula.*G5*

`=DATE(C4,G4,1)+SEQUENCE(6,7)-G5`

**Step 19:**

- In this step, you can see that the starting date for January, 01-01-2023, is Sunday from the following image.
- Also, there are some extra dates from the previous and following months of January 2023.

**Step 20:**

- So, I will extract those extra days from the calendar.
- In order to do that, insert the following combination formula in cell
.*B9*

`=IF(MONTH(DATE(C4,G4,1)+SEQUENCE(6,7)-G5)=G4,DATE(C4,G4,1)+SEQUENCE(6,7)-G5,"")`

**Formula Breakdown**

** =IF(MONTH(DATE(C4,G4,1)+SEQUENCE(6,7)-G5)=G4,DATE(C4,G4,1)+SEQUENCE(6,7)-G5,””) **

: This part of the formula expresses that the dates in the calendar are organized serially with the consideration of dates and weekdays with along with values from cell*DATE(C4,G4,1)+SEQUENCE(6,7)-G5)=G4,DATE(C4,G4,1)+SEQUENCE(6,7)-G5*,*C4*, and*G4*.*G5*: This part shows the whole conditions along with months, dates and weekdays.*IF(MONTH(DATE(C4,G4,1)+SEQUENCE(6,7)-G5)=G4,DATE(C4,G4,1)+SEQUENCE(6,7)-G5,””)*

**Step 21:**

- Consequently, you can see that the extra dates from the monthly calendar have disappeared after the previous step.

**Step 22:**

- Furthermore, I will transform the format of the dates from the table in this step.
- To do that, right-click on the mouse after selecting the data range
.*B9:H14* - Then, from the context, select
.*Format Cells*

**Step 23:**

- Additionally, you will see the
dialog box.*Format Cells* - Firstly, go to the
tab in the dialog box.*Custom* - Then, under the
label, type*Type*.*dd* - Lastly, press
.*OK*

**Step 24:**

- So, the previous step only keeps the dates in the table as a result.

**Step 25:**

- Moreover, I will change the font color for the weekends from black to red in the calendar.

**Step 26:**

- Consequently, when you change the month name from the dropdown in cell
, the dates will also be changed in the data range of the monthly calendar like the following image.*C5*

**Step 27:**

- Additionally, the next steps of this procedure will demonstrate the way to highlight the holidays for each month.
- In order to do that, first of all, select the cell range,
.*B5:H14* - Secondly, go to the
tab of the ribbon and select*Home*.*Conditional Formatting* - Thirdly, from the dropdown, choose
.*New Rule*

**Step 28:**

- In this step, you will get the
dialog box.*New Formatting Rule* - Firstly, under
header, select*Select a Rule Type*.*Use a formula to determine which cells to format* - Then, in the formula box, insert the following combination formula of
and*the ISNUMBER function*.*the VLOOKUP function*

`=ISNUMBER(VLOOKUP(B9,'Data Set'!$C:$C,1,0))`

- Thirdly, press
to format the cells that will be true for the above formula.*Format*

**Formula Breakdown**

** =ISNUMBER(VLOOKUP(B9,’Data Set’!$C:$C,1,0)) **

: Firstly,*VLOOKUP(B9,’Data Set’!$C:$C1,0)***theVLOOKUP function**here looks for a value in the leftmost column of the holiday table from the Data Set worksheet, and then returns the value in the same row from the column you specify.: Then,*ISNUMBER(VLOOKUP(B9,’Data Set’!$C:$C,1,0))*will check if the value from the previous step is a number and will return TRUE or FALSE as the output.*the ISNUMBER function*

**Step 29:**

- Afterward, choosing the desired color and font style for the specified cells click
to apply the above formula.*OK*

**Step 30:**

- Finally, your monthly calendar will be ready and will look like the following image after
to highlight holidays.*Conditional Formatting*

**Step 31:**

- Consequently, change the value of cell
to see the calendar for other months with all the formatting.*C5*

**Read More:** **How to Create a Weekly Calendar in Excel (3 Suitable Ways)**

### 2. Creating Interactive Yearly Calendar in Excel

In this section of the article, I will show you the step-by-step procedures to create an interactive** yearly calendar in Excel.** The steps are quite similar to those of making a monthly calendar. But you will notice some differences and changes regarding the creating and formatting process throughout the steps. So, to learn the whole procedure, go through the following steps.

**Step 1:**

- First of all, make twelve
tables like the following image.*7×7* - In these tables, enter the days of the week serially starting from Monday.

**Step 2:**

- Secondly, type
in cell*1*which is positioned just above the first table.*B6*

**Step 3:**

- Thirdly, after pressing
, right-click on cell*Enter*.*B6* - Then, from the context select
.*Format Cells*

**Step 4:**

- Fourthly, you will see the
dialog box.*Format Cells* - Then, go to the
tab of the box, and under the*Custom*header, type*Type*.*January* - Lastly, press
.*OK*

**Step 5:**

- Fifthly, after pressing
you will see*OK*as the header for the first table.*January*

**Step 6:**

- Consequently, follow the above steps to name the other boxes as well.
- So, name all the tables as the twelve months in form of numbers from
.*1-12*

**Step 7:**

- Moreover, color the header of this table as per your choice like the following image.

**Step 8:**

- In this step, to create the dates for the first month write the following formula in cell
.*B8*

`=IF(MONTH(DATE($B$4,B6,1)+SEQUENCE(6,7)-WEEKDAY(DATE($B$4,B6,1),2))=B6,DATE($B$4,B6,1)+SEQUENCE(6,7)-WEEKDAY(DATE($B$4,B6,1),2),"")`

- Here, the formula is the same, as I used it in the monthly calendar section to create dates.
- Moreover, I did not create separate fields for years and months in this sheet so, I am merging all those formulas into a single one.

**Step 9:**

- Then, after pressing
, you will get the dates for January 2023 in a correct sequence without doing any extra formatting.**Enter** - Furthermore, change the font color to red for the dates on the weekends after getting the value.

**Step 10:**

- Similarly, insert the above formula in the first cell of the tables for all the months to get the respective dates like the following image.

**Step 11:**

- In this step, I will highlight the holidays for the first month of the year.
- For that, firstly select cell range
.*B8:H13*

**Step 12:**

- Then, go to the
tab and select*Home*.*Conditional Formatting* - Thirdly, choose the
command from the dropdown.*New Rule*

**Step 13:**

- Then, as shown in the monthly calendar section, edit the
box in the same way.*Edit Formatting Rule* - Here, I will use the same
and**ISNUMBER**functions’ combination formula from the monthly calendar section.**VLOOKUP** - Finally, after all the editing press
.*OK*

`=ISNUMBER(VLOOKUP(B9,'Data Set'!$C:$C,1,0))`

**Step 14:**

- Consequently, the dates of the month of January will look like the following image in the yearly calendar.
- Here, through
, all the holidays have been highlighted.*Conditional Formatting*

**Step 15:**

- Similarly, use
for the other eleven months to highlight their respective holidays.*Conditional Formatting*

**Step 16:**

- Consequently, change the year value in cell
to see the respective year’s calendar.*B4*

**Read More:** **How to Make a Calendar in Excel Without Template (2 Examples)**

## Conclusion

That’s the end of this article. I hope you find this article helpful. After reading the above description, you will be able to make an interactive calendar in Excel. Please share any further queries or recommendations with us in the comments section below.

The** ExcelDemy** team is always concerned about your preferences. Therefore, after commenting, please give us some moments to solve your issues, and we will reply to your queries with the best possible solutions ever.