How to Create Excel Template for Shift Arrangement Easily?

Can we make use of Excel function such as "Substitute", "Len", "Find", "Indirect", "Countifs" and Conditional Formatting?

Step 1: Spare a Column for Staff Code

For example, fill in the staff code in the column on the left:

Step 2: Define Time Period of each Shift in each Day

Time period of shift is defined in the column on the left.

Then, fill in the staff code under the corresponding day and shift.

Step 3: Count Number of Staff Show Up for Each Shift

In step 2, each staff code is separated by the comma “,”. Therefore, we can count the number of comma “,”, in order to check the number of staff in each shift.

We can use the following Excel function:

  • SUBSTITUTE: Using this formula to remove the comma
  • LEN: Check the length of the text string

The difference in length of text string, before and after removing the comma, represents the number of comma in the original text.

Step 4: When will each Staff on Duty?

Using the following Excel function to check whether a particular staff code appears in each shift that is filled-in in step 2.

  • FIND: Check if a particular text string appears in the Excel cell

  • INDIRECT: Point to the cell according to the location we input

Step 5: Summarize Number of Shift applicable to each Staff

Using "COUNTIFS" to count the number of shift that each staff member show up:

If we want to highlight staff member who shows up for four shifts or more, we can use “Conditional Formatting” in Excel:

Conditional Formatting

Fill in the colour we want for those greater or equal to 4.

Download Our Excel Template for Your Reference:


To make your HR work more easily, you can also refer to the following blogs regarding the use of Excel.

Share with your friend if the above is useful to you!