How to create a custom calendar in Excel

Microsoft Excel is a handy application for storing data, and it has many visual objects. It is a user-friendly application. A calendar is a graphic component that you can customize for your requirements. You can select one from a wide range of calendars available in Excel Template or create your own calendar and add your notes for your requirement.

You can download one of the excel calendar templates from the web and update it.  Suppose, you did not find a calendar template for your business and are interested in creating your own calendar.  In this article, you will learn both how to insert a calendar in excel using a template, and create a custom calendar in excel with the office 365 sequence function (advanced excel formulas). 

Insert a calendar using templates

Please follow the below steps to insert a calendar in Excel using templates

1. Open Microsoft Excel on your computer
2. Click the New button
3. Click the Calendars, Calendars link is there below the search box

You can see the number of calendar options and select the best suit for your needs. Suppose you need a calendar to track your employees shift. You may choose the Shift work calendar year at a glance from the excel templates.

Once you press create button, you can see the selected calendar is inserted in your excel worksheet. You can rename the sheet name if you want.

Use the spin button to change the year, which adjusts your calendar template automatically.

To update the shift pattern

Excel calendar template has a sheet Shift Pattern, where you can see the shift pattern DDDDxxNNNNxxDDDxNNNxxxDDxNNxx for 28 days in cell C11. You can customize this pattern as per your convenience, which will reflect on your calendar.

1. Go to cell C11
2. Type the below pattern, If you need the pattern for 30 days. [ 5 day Day Shift D, 2 day Holiday x, 5 day Night Shift N continue this pattern for 30 days ]

DDDDDxxNNNNNxxDDDDDxxNNNNNxxDD

After updating the cell, you see the same pattern reflected in your calendar.

To update Conditional Formatting

Please follow the below steps to update the conditional formatting, and shift background colors in your calendar.

1. Select all the day’s cells
2. Go to Home
3. Choose Conditional Formatting
4. Select the option Manage Rules… from the menu item – to popup Conditional Formatting Rules Manager dialog box

5. Double click the third rule Yellow fill color – to popup Edit Formatting Rule dialog box

6. Press Format – to popup format cells dialog box

7. You can see the Yellow color is selected in the Background Color palette. Now you change to Orange.
8. Press OK
9. Press OK to Edit Formatting Rule dialog box
10. Press OK to the Conditional Formatting Rules Manager dialog box

Now you can see the color has changed from yellow to orange for the Day Shift. In the same way, you can update others in the conditional formatting.

You learned how to insert a calendar in excel and update conditional formatting rules for your business needs.

Create a custom calendar using the excel sequence formula

You may find it helpful to add a calendar to your Excel spreadsheet and customize for your scheduled events.  Calendars are used to track the number of hours an employee spent in a day, identify the completed taks and mark client meetings.

Format custom calendar – Year

1. Open your excel workbook
2. Go to cell C5
3. Type text 2023, you are creating the 2023 calendar
4. Format text in Bold, Middle & Center
5. Choose Font Color White
6. Choose a background color Blue

Format custom calendar – Months

1. Go to cell C7
2. Type text JAN
3. Select cells C6 & C7
4. Drag until cell C29 to fill the months JAN to DEC

Select your Month cells JAN to DEC fill color

1. Fill Grey background color
2. Choose the White color for the text
3. Format the text Middle and Center

Create a column for the month number

1. Go to cell D7
2. Type number 1
3. Select cells D6 & D7
4. Drag until cell D29 to fill 1 to 12

Your sequence formula refers to this cell value for the month

Format custom calendar – Days

1. Go to E5
2. Type text SUN
3. Drag until cell AT5
4. Select all days and Choose Rotate Text UP
5. Align your day’s cells Bold, Middle, and Center

Add the Sequence formula for the date to your calendar

1. Go to cell E7
2. Type below formula

=SEQUENCE(1,42,DATE($C$5,D7,1)-WEEKDAY(DATE($C$5,D7,1),1)+1,1)

1. Select cells E6 and E7
2. Drag until cell E29 fills the formula for the whole year

Format your custom calendar body

1. Select cells E7 to AT29
2. Go to Home
3. Select More Number formats… to apply the custom format

4. Choose Custom in the Format Cells dialog box
5. Type d in the Type input box
6. Press OK

7. Change the Text color to white, Make sure you have selected cells E7 to AT29

Apply conditional formatting to your custom calendar

1. Go to Home
2. Select Conditional Formatting
3. Choose Manage Rules… – to popup Conditional Formatting Rules Manager dialog box

4. Select New Rule in the Conditional Formatting Rules Manager dialog box

2. Choose the option Use a formula to determine which cells to format in the New Formatting Rule dialog box
3. Type the below formula into the input box Format values where this formula is true

=MONTH(E7)=$D7

4. Press Format

5. Select Border
6. Select dotted line
7. Choose Outline

8. Go to Font
9. Choose Black color
10. Press OK
11. Press OK to Edit the Formatting Rule
12. Press OK to Conditional formatting Rules manager

Select the rows between months and resize the row height

1. Select all your Weekend days and format
2. Fill the cell background color with Grey
3. Choose the same Grey color for the text

Select weekends SAT/SUN fill only background with Grey color

Remove gridlines in Excel

1. Go to View
2. Uncheck Gridlines checkbox

Add Thick Outside Borders to cover the calendar

1. Select B4 to AU30
2. Just choose Thick outside border from Border

Adjust column widths and format row 2 to enter your company details.

This is a dynamic calendar, you can change the value in the year cell C5 and see the difference.

Your final output will look like the below image

Conclusion

You have used one of the advanced Excel formulas ( Sequence ) and Conditional formatting to create the custom calendar in excel. The sequence formula is not available in an older version of excel. This calendar will not work in an older version.

You can choose one of the excel calendar templates and customize it for your business or report.