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.
- 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.
- Secondly, 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.
- Thirdly, 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.
- Fourthly, 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.
- Fifthly, 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.
- 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
- 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.
- Then, after pressing Enter, you will find the month number of cell C5 in G4 just like the following image.
- 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 the WEEKDAY function and the DATE function 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.
- Then, the formula will show the number corresponding to the first day for January 2023 which is 7 which indicates Sunday.
- 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.
- 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.
- 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.
- So, you will get the result like the following image through the table by pressing Enter after inserting the formula.
- 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.
- Then, from the dropdown, choose Short Date as the format for the selected cell range.
- 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 G5 which is 7.
- So, insert the following formula in cell B9, where I will subtract the cell value of G5 from the previous value of this formula.
- 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.
- So, I will extract those extra days from the calendar.
- In order to do that, insert the following combination formula in cell B9.
- 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 C4, G4, and G5.
- IF(MONTH(DATE(C4,G4,1)+SEQUENCE(6,7)-G5)=G4,DATE(C4,G4,1)+SEQUENCE(6,7)-G5,””): This part shows the whole conditions along with months, dates and weekdays.
- Consequently, you can see that the extra dates from the monthly calendar have disappeared after the previous step.
- 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.
- 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.
- So, the previous step only keeps the dates in the table as a result.
- Moreover, I will change the font color for the weekends from black to red in the calendar.
- 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.
- 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.
- 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 the ISNUMBER function and the VLOOKUP function.
- Thirdly, press Format to format the cells that will be true for the above formula.
- VLOOKUP(B9,’Data Set’!$C:$C1,0): Firstly, 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.
- ISNUMBER(VLOOKUP(B9,’Data Set’!$C:$C,1,0)): Then, the ISNUMBER function will check if the value from the previous step is a number and will return TRUE or FALSE as the output.
- Afterward, choosing the desired color and font style for the specified cells click OK to apply the above formula.
- Finally, your monthly calendar will be ready and will look like the following image after Conditional Formatting to highlight holidays.
- Consequently, change the value of cell C5 to see the calendar for other months with all the formatting.
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.
- 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.
- Secondly, type 1 in cell B6 which is positioned just above the first table.
- Thirdly, after pressing Enter, right-click on cell B6.
- Then, from the context select Format Cells.
- 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.
- Fifthly, after pressing OK you will see January as the header for the first table.
- 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.
- Moreover, color the header of this table as per your choice like the following image.
- 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.
- 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.
- 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.
- In this step, I will highlight the holidays for the first month of the year.
- For that, firstly select cell range B8:H13.
- Then, go to the Home tab and select Conditional Formatting.
- Thirdly, choose the New Rule command from the dropdown.
- 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.
- 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.
- Similarly, use Conditional Formatting for the other eleven months to highlight their respective holidays.
- Consequently, change the year value in cell B4 to see the respective year’s calendar.
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.