How to Create Monthly Duty Roster Format in Excel

To create a monthly duty roster in Excel, first we’ll create a roster template, then fill the roster for a specific month into this remplate as an example.

STEP 1 – Make Monthly Duty Roster Template

In this template, there must be headers for Serial Number, Employee Name and Designation and dedicated cells for the Month, Days of the Month and Date of those days.

Download the template from the Download Practice Workbook section below

Now create another Excel Sheet, where we will store a list of the Months.
Rename the sheet as Months.
Enter January in cell B4.
Drag down the Fill Handle to fill the names of the other Months sequentially.

The list of months is complete.

STEP 2 – Create Drop-Down Menu for Entry of Month

Select cell C4.
Go to the Data tab.
From the Data Tools group, select the Data Validation icon.
Select Data Validation from the list.

A dialog box named Data Validation will open up.

Select List from the drop-down menu in the Allow field.
In the Source field, enter =Months!$B$4:$B$15.
Click OK to proceed.

Here, Months is the name of the Excel Sheet where we put the months, and the list of months is in the range B4:B15.

Next to cell C4, a drop-down menu icon appears. Clicking it will open a list for choosing a Month.

We will create our monthly duty roster for the month of December.

Select December from the drop-down list.

STEP 3 – Insert Employee Information

Insert the employee information serially as in the image below.

STEP 4 – Input Date and Duty on the Duty Roster

In cells F4 and G4, enter 1 and 2 respectively (representing 1st and 2nd December).
Drag the Fill Handle right to fill all the cells of Date up to 31st December serially.

Let’s fill in the Day row.

As 1st December is Thursday, enter Thu in cell F5.
Drag the Fill Handle right to fill the Days serially.

The rows for Date and Day are complete. Below is what the roster for the first 7 days looks like:

And below is the roster template for 20th to 31st December.

Our monthly duty roster format is ready.

Now we can insert the duty times of the employees for the month.

For example, here is a filled in roster up to 7th December, where M is for Morning, N is for Night and A is for Afternoon.

And here is the roster from 20th to 31st December.

STEP 5 – Highlight Weekends

Assuming there are no shifts on the weekend, we will insert WO (Weekend Off) on the holidays and weekends, i.e. on every Saturday and Sunday.

Let’s highlight the weekends and vacations.

Select the range F6 to AJ6 (the overall monthly roster duty schedule).
Click on the Home tab.
From the Styles group, click on the drop-down menu of Conditional Formatting.
From the drop-down menu, select Highlight Cells Rules >> Text That Contains.

A window named Text That Contains will open up.

Enter WO as Format Cells that contain the text.
Select Light Red Fill with Red Text highlighter.
Click OK to proceed.

As a result, cells containing WO are highlighted.

Here’s what our roster looks like up to 7th December.

And here from 20th to 31st December.

In the last step, we will insert an informational text box.

Click on the Insert tab.
Select Text >> Text Box.

An arrow appears, indicating text boxes.

Place this arrow where you want to put your textbox.

In the text box, enter information relevant to the roster, such as explanations of what M, N, A and WO represent.
Modify the color of the text and the cell as desired using the icons in the image below.