How to Make a Roster in Excel (with Detailed Steps)

Get FREE Advanced Excel Exercises with Solutions!

A roster usually indicates a schedule that divides the turn of duties (or even leave) in an organization for an individual or a group. From attendance tracking to event planning, rosters are helpful to keep track of people and other information. Along with other templates and trackers, Microsoft Excel is a wonderful tool for making custom rosters along with all the prepared ones available online for organizations. This tutorial will provide a detailed step-by-step guide on how to make a roster in Excel.

how to make a roster in Excel


Watch Video – Make a Roster in Excel



Usefulness of an Excel Roster

Excel is a wonderful tool to create templates, calculators, trackers, budgets, invoices, balance sheets, to-do lists, etc. A roster is no exception. Some reasons to create a roster in Excel are:

  • Easy Data Entry: You can manipulate data in a tracker quite easily in Excel and even improve an already existing tracker too.
  • Flexible Formatting: If you don’t like the look of your roster, Excel offers a wide range of modification tools to influence the look.
  • Calculation Capabilities: Excel provides different functions such as SUM, AVERAGE, COUNT, etc. that we can use to create formulas to manage and track data of a roster.
  • Useful Features: Excel offers useful tools like Sort & Filter and Find & Select that a roster can take advantage of.
  • Compatibility with other software: Excel’s spreadsheet objects and data can be easily shared with other software such as Microsoft Word, PowerPoint, and Outlook which can enable us to share the roster from Excel to other platforms for other purposes.

How to Make a Roster in Excel: Step-by-Step Guide

Not only can you download and use a roster from Microsoft Excel’s template section but also you can create one on your own from scratch. We will focus more on the latter because that provides more control over the roster. We have included each step in its own subsection so that we can go into more details and it is understandable for every Excel user.

1st Step: Create a Spreadsheet for Different Attributes

First of all, we need a spreadsheet that contains all the repeatable information like employee names and months. This sheet is also important to manipulate data throughout the workbook. It is the basis of using the workbook as a template.

We have included months, years, weekends, shift types, shift codes, and employee names in different columns.

Roster particulars

We have named the sheet “Settings” which will be helpful in later steps. Be careful with the name you will pick here, some steps will change according to it.


2nd Step: Make Named Ranges for Particulars

This is the basis of all the dynamic operations of the roster. We are going to name each range so that we can use recall them in formulas. We will also opt for dynamic ranges except for the months and weekends as the roster can then work for new entries, such as other years or if you have a new kind of shift type or new employees. OFFSET and COUNTA functions will help us achieve that.

  • To name a static range, select the range and insert a name in the Name Box.

adding a static named range

  • To name a range dynamically, go to the Formulas tab >> select Name Manager from the Defined Names group >> select New from the Name Manager box.

adding a dynamic named range

  • In the next Edit Name box, name the range beside the Name field and insert formulas that define the dynamic range beside the Refers to field.

formula for dynamic named range

  • After clicking on OK and closing all of the boxes, you will have the named ranges.

We have to define each dynamic or static range individually. The formulas we have used for each range are:

Employee_List (employee names):

=OFFSET(Settings!$G$4,1,0,COUNTA(Settings!$G:$G)-1,1)

Shift_Codes (Shift Codes):

=OFFSET(Settings!$F$4,1,0,COUNTA(Settings!$F:$F)-1,1)

YearList (Years):

=OFFSET(Settings!$C$4,1,0,COUNTA(Settings!$C:$C)-1,1)

🔎 Formula Explanation

OFFSET(Settings!$G$4,1,0,COUNTA(Settings!$G:$G)-1,1)

👉 The COUNTA function counts the number of cells in the range within it. The Settings! before the range is from the spreadsheet name.

👉 COUNTA(Settings!$C:$C)-1 is the height argument of the OFFSET function while cell G4 is the reference. The OFFSET function basically picks up the range below the G4 cell and automatically puts it in the range “YearList”.


3rd Step: Resize Cells for Master Sheet

Next, we are going to create a Master sheet. This sheet will contain the main roster format.

  • We are going to merge B2:C4 to hold a place for the logo.

preparing cells for logo

  • We will also merge E4:V3 to hold the title, W2:AB3 to hold the monthly value, AC2:AF3 to place the year’s value, and AG2:AI3 to keep up with the roster below (we are keeping place for the whole thirty-one days of a month in it).

merging cells to make roster headers

  • We will also merge E4:I4, K4:L4, M4:O4, P4:R4, S4:T4, U4:W4, X4:Z4, AA4:AB4, AC4:AE4, and AG4:AI4 and prepare the headers like follows.

merging cells for month headers


4th Step: Insert Logo and Headers

We are going to insert the logo and headers next, in the prepared cells.

These are the headers and the logo we have selected to put in our template.

inserting logo and headers

If you are an advanced user you can modify these on your own but for beginners, we will recommend just following them as the steps suggest.


5th Step: Prepare Cells for Month and Year

In this step, we will assign lists that will work as a drop-down menu to select from for months and years.

  • Now to select the month from a drop-down list, select cell W2 and select Data Validation from the Data Tools group of the Data

adding month with data validation

  • In the Data Validation box, select List under Allow, and clicking on the Source field, press F3 on your keyboard. Select the named range for months from the “Settings” sheet.

data validation option for months

  • After clicking on OK, there will be a drop-down list available in the cell, with all the month names in it.

dropdown list working for months

  • Do the same for the cell beside it for the year values.

dropdown list for years

  • Now let’s select random values for them and in cell J4 too that will help us with the formulas for the next portions.

roster headers completed


6th Step: Prepare Cells for Other Month Particulars

The process gets more Excel-ish from now on with the formulas and functions. We are going to use a wide variety of them. But for now, we will only need DATEVALUE, IFERROR, and EOMONTH functions. Formula explanations are added for the complex ones to facilitate the usage in custom cases.

  • In cell P4, write down the following formula.
=IFERROR(DATEVALUE(J4&W2&AC2),"")

start date using formula and reference

🔎 Formula Explanation

  • J4&W2&AC2 puts together the values of these three cells to make up a date.
  • DATEVALUE(J4&W2&AC2) converts them into Excel’s date value.
  • We have put the previous function in an IFERROR function so that in cases of error (such as values like 32 in cell J4), Excel doesn’t display any error message.
  • You may have noticed the date is not in the correct format. To put it in the proper format, select the cell and press Ctrl+1 on your keyboard. This will open up the Format Cells Go to the Number tab in it, select Custom under Category, and put a custom mm-dd-yyyy format on the right side.

correcting date format

After clicking on OK, we can find the date in the proper format.

corrected date format

  • In cell X4, we will insert the following formula and use the same formatting technique.
=IFERROR(EOMONTH(P4,0)+J4-1,"")

17-end date in roster

🔎 Formula Explanation

IFERROR(EOMONTH(P4,0)+J4-1,””)

👉 EOMONTH(P4,0) indicates the month-ending value of the date in cell P4. We have used the argument 0 so that it returns the end value of the month in cell P4.

👉 The value after that (+J4-1) is there to make sure it is exactly a month after P4 in case the starting date is not the first day of the month and disregards how many days the month has.

👉 We put that inside an IFERROR function so that it doesn’t display an error value in case of invalid input such as more than 31 days as the starting day.

  • To count the “Total Days”, we have put the following formula in cell AF4.
=X4-P4+1

counting total days in the month in roster


7th Step: Insert Dynamic Dates and Days

Now it is time to prepare the main roster section. Our roster will have both days and dates above all the assigned shifts.

  • So we will put this inside cell E7 so that it maintains the starting date. Also, we are using the dd format as we don’t want the cells to be too large for this.
=P4

starting date in the roster main section

  • In cell F7, we will use the following formula and the format dd.
=IF(E7>=$X$4,"",E7+1)

formula for date values

  • We will replicate the formula till cell AI7.

replicating date values in roster

  • In cell E6, we will use the following formula for the day.
=TEXT(E7,"ddd")

inserting day values with formula in the roster

  • We have also changed the alignment vertically. To do that, press Ctrl+1 while the cell is selected and use 90 degrees in the Alignment tab of the Format Cells box that will open up.

rotating alignment for day values in the roster

  • After pressing OK, the day will look like this now.

cell values rotated 90 degrees

  • We dragged the fill handle icon to cell AI6 which replicated the formula.

replicating day values in the roster


8th Step: Organize Employee Sheet Columns Dynamically

We will have the entries of employees on the left of the calendar-ish formation we have in our roster. The employee list exists in the “Settings” sheet. We are willing to modify the employee’s column in the roster in such a way that it imports the value from that list and an ex-employee or a new one can be edited from there and the roster’s available input values will change automatically from here.

  • First, we have merged cells B6 and B7 to accommodate the heading in contrast with the rest of the roster.

creating employee header

  • Next, select the range for the number of employees you want to insert the roster for. You can always insert or delete rows in between to put up with the changing employee count. We have selected the range B8:B17 for 10 employees for the demonstration and selected Data Validation from the Data Tools group of the Data tab from the ribbon.

preparing cells for employee input

  • In the Data Validation box’s Settings tab, we have selected a List of “Employee_List” as a Source.

data validation option for employee input

  • A drop-down list will be available for each of the selected cells. You can select employee names from here.

data validation for employees


9th Step: Assemble Starting Shifts

We want to achieve employee shifts in a similar fashion.

  • First, we have merged the cells C6 and C7 for the header.

creating starting shifts header

  • Then selecting the range C8:C17, we selected Data Validation from the ribbon.

preparing cells for sharting shift input

  • In the Data Validation box, we selected a List from the Settings tab with the source “Shift_Codes”.

data validation option for starting shifts

  • After clicking on OK, we can select all the shift codes from the available drop-down menu.

dropdown for starting shifts


10th Step: Prepare Shifts to Update Based on Starting Shifts

We want the assigned shifts for each day to automatically fill up based on the starting shift. As employees will take turns in the working shifts, the periodic shuffle is achievable in Excel with formulas.

  • To automate the task, use the following formula in cell E8.
=IF(OR($C8="",E$7=""),"",IF(D8="",C8,INDEX(Shift_Codes,IF(MATCH(D8,Shift_Codes,0)+1>COUNTA(Shift_Codes),1,MATCH(D8,Shift_Codes,0)+1))))

formula to fill values based on starting shift

🔎 Formula Explanation

IF(OR($C8=””,E$7=””),””,IF(D8=””,C8,INDEX(Shift_Codes,IF(MATCH(D8,Shift_Codes,0)+1>COUNTA(Shift_Codes),1,MATCH(D8,Shift_Codes,0)+1))))

👉 OR($C8=””,E$7=””) checks if either of the cells C8 and E7 has any blank values. Those cells contain IFERROR formulas and may return an empty string depending on the values of other input cells. Also, we have locked columns in the first argument and rows for the second argument. This will be helpful while replicating the whole formula.

👉 The IF(OR($C8=””,E$7=””),””,…) portion of the formula puts an empty string in the cell if both C8 and E7 cells are empty. Otherwise, it moves on to the later portion of the formula.

👉 IF(D8=””,C8,…) comes into play when the previously mentioned logical argument is FALSE. This portion then checks if cell D8 contains a blank value. If it does, then we will just have the value of cell C8. otherwise, it will move on to the next section of the formula. This portion is for the first entries of the roster which will take direct value from the values we will select in column C.

👉 INDEX(Shift_Codes,IF(MATCH(D8,Shift_Codes,0)+1>COUNTA(Shift_Codes),1,MATCH(D8,Shift_Codes,0)+1)) is the value in case the cell D8 isn’t blank (the cell is not in the leftmost column in the roster). Shift_Codes is the array the INDEX function is looking for.

👉 IF(MATCH(D8,Shift_Codes,0)+1>COUNTA(Shift_Codes),1,MATCH(D8,Shift_Codes,0)+1) portion indicates the row number of the INDEX function. If the exact match of cell D8 in the array Shift_Codes is greater than the count of Shift_Codes (counted by the COUNTA function) by at least greater than 2 (compensated by +1), the row number becomes 1 with this IF function.

👉 Otherwise, the row number is MATCH(D8,Shift_Codes,0)+1 which is one higher than the relative position of the value of cell D8 in the Shift_Codes array.

  • We have replicated the formula by dragging the fill handle icon to cell AI8.

filling up shift codes horizontally

  • To fill up the rest of the cells, we have dragged the selection from AI8 to AI17

filling up shift codes veritically


11th Step: Create Report Based on Days

It is always helpful to have a report on how many shifts are distributed in each day to make sure enough shifts are covered. Also, it can help us reassign shifts which will be pretty easy considering the list we have imported using Data Validation in the previous steps. We are going to add this result section below the roster.

  • First, we are going to use the following formula in cell E19.
=COUNTIF(E$8:E$17,"D*")

calculating total day shift employees

  • Then we replicated the formula till AI19.

filling up formula for day shift employees

  • Similarly, we inserted the following formula in cell E20 and dragged it till AI20.
=COUNTIF(E$8:E$17,"N*")

calculating and filling total night shift employees

Note: We have used “D*” and “N*” as criteria in the COUNTIF function. The asterisk is a wildcard here that indicates all the values that start with either “D” or “N” in their respective formulas. As our shift codes align as such, it fits our purpose.

12th Step: Create Report Based on Employees

It is also important to mark out how many of each shift every person has completed throughout the month.

  • We have merged AK6, AK7, and AL6, AL7 for the column headers.

merging cells and making headers for employee report

  • For the counts, we have used the following formula in cell AK8.
=COUNTIF($E8:$AI8,"D*")

formula to count an employee’s covered shifts

  • Then we dragged it down to cell AK17.

replicating employee report formula

  • Similarly, we have inserted the following formula in cell AL8 and dragged the fill handle to AL17.

applying and replicating employee report formula

  • We have also merged AK2:AL4 to create another header.

Final report header


13th Step: Add Formatting to Roster

Let’s add some conditional formatting to the roster’s main section as it would pop out different shifts more and will make the values more identifiable. We are going to use two rules to distinguish between day and night shifts. As shifts are cyclic, adding more would result in jamming the workbook with unnecessary data.

  • First, we have selected the range E8:AI17.
  • Then from the Home tab, we have selected Conditional Formatting from the Styles group and New Rule from the drop-down.

new rule for conditional formatting

  • In the New Formatting Rule box, we selected Use a formula to determine which cells to format under Select a Rule Type and inserted the following formula in the field.
=LEFT(E8)="N"

applying formula for conditional formatting

  • You can select your format style by clicking on the Format We opted for a grey fill for the night shifts. After clicking on OK, the night shifts will be filled.

conditional formatting applied for night shifts

  • We have used the same procedure for the day shifts, but inserted the following formula and a light green fill.
=LEFT(E8)="D"

day shift conditional formatting formula

  • After clicking on OK, we also have the formats applied for day shifts too.

coditional formatting applied for all sheets

  • While we are at it, let’s format other cells too, to make it more presentable.

how to make a roster in Excel


Last Step: Apply VBA to Automate Sheets for Rest of Months

We can comfortably use the roster we have created up until now and keep inserting values manually. The problem is for new months, we have to do it all over again. You can copy the sheet and make changes accordingly too. But we can also put that up to automation. For that, we need to use Visual Basic for Applications (VBA). For that, you need to enable the Developer tab on your ribbon.

  • To insert a VBA code in a workbook, you need to select Visual Basic from the Code group of the Developer tab on the ribbon.

selecting visual basic from the ribbon

  • From the Insert tab of the VBA window, we selected Module from the drop-down menu.

inserting module from the VBA window

  • Next, we inserted the following code in the newly created module.
Sub JumpToSheet()
Dim wsht As Worksheet, sht_name As String, wbookX As String, wbookY As Variant
Set wsht = ThisWorkbook.ActiveSheet
sht_name = ActiveSheet.Name
wbookX = Mid(sht_name, 1, Len(sht_name) - 5)
wbookY = Right(sht_name, 4)
    Sheet_Count = ThisWorkbook.Worksheets.Count
    For i = 1 To Sheet_Count
    Sheet_Check = wsht.Range("W2").Value & " " & wsht.Range("AC2").Value
    If Sheets(i).Name = Sheet_Check Then
    Sheets(Sheet_Check).Activate
    End If
    Next i
    If sht_name = "Roster" Then
    wsht.Range("W2").Value = "January"
    wsht.Range("AC2").Value = 2021
    Exit Sub
    Else
    wsht.Range("W2").Value = wbookX
    wsht.Range("AC2").Value = wbookY
    End If
End Sub
Sub CopyToSheet()
Dim wsht As Worksheet, sht_name As String, wbookX As String, wbookY As Variant
Set wsht = ThisWorkbook.ActiveSheet
sht_name = ActiveSheet.Name
wbookX = Mid(sht_name, 1, Len(sht_name) - 5)
wbookY = Right(sht_name, 4)
    Sheet_Count = ThisWorkbook.Worksheets.Count
    ActiveSheet.Copy After:=Worksheets(Sheets.Count)
    If wsht.Range("W2").Value <> "" Then
    ActiveSheet.Name = wsht.Range("W2").Value & " " & wsht.Range("AC2").Value
    End If
    For i = 5 To 35
    If ActiveSheet.Cells(7, i).Value = "" Then
    ActiveSheet.Cells(7, i).EntireColumn.Hidden = True
    Else
    ActiveSheet.Cells(7, i).EntireColumn.Hidden = False
    End If
    Next i
    If sht_name = "Roster" Then
    wsht.Range("W2").Value = "January"
    wsht.Range("AC2").Value = 2021
    Exit Sub
    Else
    wsht.Range("W2").Value = wbookX
    wsht.Range("AC2").Value = wbookY
    End If
End Sub

code in the module

  • After that, in the “Roster” sheet, we have inserted the following code.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim wsht As Worksheet
Set wsht = ThisWorkbook.ActiveSheet
Sheet_Count = ThisWorkbook.Worksheets.Count
Check_Sheet = wsht.Range("W2").Value & " " & wsht.Range("AC2").Value
If Not Intersect(Target, Range("W2:AC2")) Is Nothing Then
If Check_Sheet = ActiveSheet.Name Then Exit Sub
    For i = 1 To Sheet_Count
    If Sheets(i).Name = Check_Sheet Then
    Call JumpToSheet
    Exit Sub
    End If
    Next i
    Call CopyToSheet
End If
End Sub

code in the sheet

  • Once you are done, close the window. Now we can select a new month in cell W2 and a new spreadsheet will open for that with all the formatting’s and formulas already applied to the cells.

new sheet created with the change of month

The roster is now finally complete.


Frequently Asked Questions (FAQs)

  • How can I add or remove columns and rows in a roster?

To add or remove columns or rows, right-click on the column or row names on the top or left (marked as A, B, 1, 2, etc.) and select Insert from the context menu.

  • How can I sort or filter data in a roster to easily view specific information?

You can sort or filter easily through any charts in an Excel spreadsheet using the Sort & Filter feature available in the Home tab. For more details, check out How to Sort and Filter Data in Excel.

  • Is there a way to customize the appearance of the roster, such as changing fonts or adding colors?

Yes, you can customize or format the appearance of cells in a roster just like you would in any other cells in Excel.

  • How can I print or share the Excel roster with others?

There is a Print option available in the backstage view which you get after clicking on the File tab on the ribbon. You can also press Ctrl+P. Then you can either print it or save it as a different file format and share it with others depending on the options you choose.


Things to Remember

  • Make sure to keep the changeable named ranges to dynamic ones. It will ensure any new entries will include in the drop-down menus in the roster.
  • Consider protecting your spreadsheet in order to prevent any accidental changes in the cells once it is complete.
  • You can use the conditional formatting feature for the purpose of error handling too. This is particularly helpful if you ignore data validations and insert values manually.
  • While using the formulas, use references and absolute references correctly.

Download Practice Workbook

You can download the workbook used for the demonstration from the link below which you can also use as a template to use as your own roster for your scenarios.


Conclusion

These are the steps you can follow to make a dynamic roster in Excel. To summarize, we have used drop-down menus imported from other sheets to minimize errors in a roster by using the named ranges. We have also used formulas to automatically fill up cells in the roster as our values repeat in certain cycle. In the end, we have used vba to jump to the sheet containing month of the selected year or create new one if it doesn’t exist already.

Hopefully, you can make a roster in Excel easily now that you have gone through the article. I hope you found this guide helpful and informative. If you have any questions or suggestions, let us know in the comments below.

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Junaed-Ar-Rahman
Junaed-Ar-Rahman

Md Junaed-Ar-Rahman, a Biomedical Engineering graduate from Bangladesh University of Engineering and Technology, has contributed to the ExcelDemy project for one year. As a technical content developer, he has authored 15+ unique articles and actively addressed user problems. He participated in 2 specialized training programs on VBA and Chart & Dashboard design in Excel. His passion lies in solving problems uniquely and exploring new functions and formulas. Eager for future exploration, he aims to gain proficiency in applications... Read Full Bio

4 Comments
  1. It is great for me. Thank you exceldemy.com.

  2. Hi Junaed-Ar-Rahman,

    I have 4 staff , with 3 working shifts 24/7 5 days in 2 days off. The fourth person only works day shift. I am struggling to find the right setup whereby i need to ensure that if one person is 2 days off that the other 2 will cover the afternoon and night shift. The day shift will be covered by the Team Leader.

    Please help.

    • Reply Lutfor Rahman Shimanto
      Lutfor Rahman Shimanto Nov 8, 2023 at 3:47 PM

      Hello KRISH

      Thanks for reaching out and posting your question. The mentioned schedule can be maintained in several combinations. However, I am presenting a suitable one that will fulfil your requirements.

      SHIFT:
      Day Shift (D): 8 AM to 4 PM
      Afternoon Shift (A): 4 PM to 12 AM
      Night Shift (B): 12 AM to 8 AM

      MEMBERS:

      You have 3 staff members (A, B, and C) and a Team Leader (D).

      Staff members A, B, and C will follow a 5-day on, 2-day off schedule, rotating through the three shifts.

      The Team Leader (D) will work the day shift (D) if other staff members have an off day.

      Staff Rotation Schedule (7-Day Cycle):
      Day 1 (Staff D’s day off):

      Day Shift: Staff A works from 8 AM to 4 PM.

      Afternoon Shift: Staff B works from 4 PM to 12 AM.

      Night Shift: Staff C works from 12 AM to 8 AM.

      Day 2 (Staff B’s day off):

      Day Shift: Staff D (Team Leader) from 8 AM to 4 PM.

      Afternoon Shift: Staff C works from 4 PM to 12 AM.

      Night Shift: Staff A works from 12 AM to 8 AM.

      Day 3 (Staff C’s day off):

      Day Shift: Staff D (Team Leader) works from 8 AM to 4 PM.

      Afternoon Shift: Staff A works from 4 PM to 12 AM.

      Night Shift: Staff B works from 12 AM to 8 AM.

      Day 4 (Staff A’s day off):

      Day Shift: Staff D (Team Leader) works from 8 AM to 4 PM.

      Afternoon Shift: Staff B works from 4 PM to 12 AM.

      Night Shift: Staff C works from 12 AM to 8 AM.

      Day 5 (Staff A’s day off):

      Day Shift: Staff D (Team Leader) works from 8 AM to 4 PM.

      Afternoon Shift: Staff B works from 4 PM to 12 AM.

      Night Shift: Staff C (Team Leader) works from 12 AM to 8 AM.

      Day 6 (Staff B’s day off):

      Day Shift: Staff C works from 8 AM to 4 PM.

      Afternoon Shift: Staff A works from 4 PM to 12 AM.

      Night Shift: Staff D (Team Leader) works from 12 AM to 8 AM.

      Day 7 (Staff C’s day off):

      Day Shift: Staff D (Team Leader) works from 8 AM to 4 PM.

      Afternoon Shift: Staff B works from 4 PM to 12 AM.

      Night Shift: Staff A works from 12 AM to 8 AM.

      Hopefully, the idea will help you. Good luck!

      Regards
      Lutfor Rahman Shimanto

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo