How to Make an Interactive Calendar in Excel (2 Easy Ways)

For our daily busy life schedule, the calendar is a much-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.

Make an Interactive Calendar in Excel: 2 Easy Ways

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:

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

Step 3:

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

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

Step 4:

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

Step 5:

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

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 C4 or C5

Step 7:

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

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

Step 8:

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

Step 9:

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

• Here, the Date function will show the first date of each month of a specific year.
• 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 2 as the return type in the formula.

Step 10:

• Then, the formula will show the number corresponding to the first day of January 2023 which is 7 which indicates Sunday.

Step 11:

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

Step 12:

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

Step 13:

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

Step 14:

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

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 the Number Format dropdown in the Number group.

Step 16:

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

Step 17:

• Consequently, the result after changing the format will look like the following image.
• Here, you can see from the following image, that 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 G5 which is 7.

Step 18:

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

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.

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 Format Cells dialog box.
• Firstly, go to the Custom tab in the dialog box.
• Then, under the Type label, 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 C5, the dates will also be changed in the data range of the monthly calendar like the following image.

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 Home tab of the ribbon and select Conditional Formatting.
• Thirdly, from the dropdown, choose New Rule.

Step 28:

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

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

Step 29:

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

Step 30:

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

Step 31:

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

Read More: How to Make a Calendar in Excel Without Template

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. However, 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 7×7 tables like the following image.
• In these tables, enter the days of the week serially starting from Monday.

Step 2:

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

Step 3:

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

Step 4:

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

Step 5:

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

Step 6:

• Consequently, follow the above steps to name the other boxes as well.
• So, name all the tables as the twelve months in the 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.

• 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 Enter, you will get the dates for January 2023 in a correct sequence without doing any extra formatting.
• 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 Home tab and select Conditional Formatting.
• Thirdly, choose the New Rule command from the dropdown.

Step 13:

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

Step 14:

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

Step 15:

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

Step 16:

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

Read More: How to Create a Yearly Calendar in Excel

Download Practice Workbook

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

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.

Related Articles

• How to Create a Weekly Calendar in Excel
• How to Make a Blank Calendar in Excel