Work schedule – free Excel template to download

In companies with shift work, a work schedule is one of the most important tools for efficient staff man­age­ment. And it fulfils two purposes: it provides employees with an overview of work hours, and it helps the employer organize routine tasks like doc­u­ment­ing weekly hours and wage cal­cu­la­tions.

But pro­fes­sion­al work schedule software is expensive. And single-handedly creating a weekly or monthly plan in Excel can take a lot of time and effort. Instead, why not use our free work schedule template in Excel, which is ready for use in just a few steps.

Work schedules must meet certain content, design, and legal re­quire­ments to guarantee that every employee is at the right place at the right time.

First of all, a work schedule must include all vital in­for­ma­tion relevant to your employees: At first sight, it must make clear which calendar week it’s relating to and which col­leagues are logged for which shifts. That’s why it makes sense to con­cen­trate on the key points. This will reduce the risk of the schedule being mis­un­der­stood, which can lead to un­der­staffing.

As an employer and schedule planner, it’s practical if you’re able to see straight away how often employees have been assigned shifts, their weekly accounted hours, and their hourly wages. Excel work schedules are es­pe­cial­ly suited for this purpose, as they offer the pos­si­bil­i­ty to make cal­cu­la­tions out of quan­ti­ta­tive data like weekly hours and wages.

Less in­for­ma­tion often means a better overview. But blank lines and columns as well as different fonts and colors can also assist the structure. With the help of these and other design-related tools, your shift schedule should ideally be self-ex­plana­to­ry to users. You can also choose to add a legend, where you describe the meaning of certain color-coding, for example.

Work schedules must meet certain legal re­quire­ments: Changes to overtime policies, minimum wage, rest period re­quire­ments, and sched­ul­ing rules can sig­nif­i­cant­ly impact the bottom line. Staying compliant to all the federal, state, and city em­ploy­ment laws is also chal­leng­ing to manage, so it’s best to check in with the US De­part­ment of Labor for more in­for­ma­tion. Two areas of 2019 em­ploy­ment laws that affect work­forces, for example, include pre­dic­tive sched­ul­ing and minimum wage increases.

You can download the free work schedule template by following this link:

Next, we’ll describe how you can use the template and adapt it to your needs.

The down­load­able Excel template fulfils all re­quire­ments of a pro­fes­sion­al work schedule: It’s limited to the most important in­for­ma­tion and is designed in a clear and appealing way.

However, if the work schedule isn’t to your liking aes­thet­i­cal­ly, then you can easily adapt it – for example by changing the column width. Use the “fill color” button in the “start” menu to clearly define layers and tables from one another. Different fonts and font sizes can also com­ple­ment this. The right side of the template offers plenty of white space: for example, for ad­di­tion­al notes or to loosen up the work schedule with ad­di­tion­al columns.

Of course, it’s up to you to adjust the layout of the work schedule template. We recommend, however, that you keep the column seg­men­ta­tion as it is. Only then can the cal­cu­la­tion module correctly calculate the shifts, weekly hours, and wages. If you do have to make changes, then you should make yourself familiar with the COUNTIF function or the “if then” function in Excel. Or you can follow the following steps.

As you can see, some sample names have already been entered in the work schedule template. Click on the name in the weekly plan and you’ll notice that next to each cell is a small arrow. Click on it, open the drop-down menu, and select the employees assigned to the shift. In this way, you don’t have to manually type in the employee names for the shift each time.

Every drop-down menu is linked to the employee list in the lower end of the template. When you change the names in the list, the names in the drop-down menu will adapt ac­cord­ing­ly. In this way you can also change the names of the sample employees with those in your company. Should you need more employees than the number included (7), then you must amend the drop-down menu. To do this, follow these steps:

1. Write out the names of your employees under the “employee list” column. Note that the sequence will be mirrored in the drop-down menu.
    
2. Now click on any empty cell in the work schedule.
    
3. Click on the “val­i­da­tion” button in the menu ribbon under “data” and a context menu will open.
    
4. Under “settings,” select “list” in the “allow” drop-down.
    
5. Remove the check at “ignore blank” but keep the check for “in-cell dropdown.”
    
6. Now, click on the button next to the dialog box “source.”
    
7. With your mouse, mark your employee list. This will now be used as a source for your drop-down menu and will be shown the re­spec­tive dialog field with the formula, which could look something like this: =$C$35:$C$42. Al­ter­na­tive­ly, you can enter the source manually.
    
8. Next, in the “input message” tab, remove the check next to “show input message when cell is selected.” Do the same with the tick at “show error alert after invalid data is entered” in the “error alert” tab.
    
9. Click on “OK.”

You have now created a new drop-down menu that links to your employee list, making the shift al­lo­ca­tion process sig­nif­i­cant­ly easier. Now you have to adapt the cal­cu­la­tion module in the bottom of the Excel template. We’ll tell you how this works in the following steps.

The color-high­light­ed cal­cu­la­tion module on the margin of the Excel template sim­pli­fies the shift schedule for you by au­to­mat­i­cal­ly cal­cu­lat­ing the number of entries, the weekly hours, and the gross income of your employees. For example, if you want to assign your employee Anne-Marie to a shift, then this will be au­to­mat­i­cal­ly assigned and her weekly hours will be au­to­mat­i­cal­ly cal­cu­lat­ed (in the example 4 entries each at 8 working hours equal a total of 32 weekly hours). Depending on the gross hourly wage that you enter for Anne-Marie, Excel will calculate the gross income for Anne-Marie in the final column.

If you’ve entered ad­di­tion­al names into your employee list, then you must com­ple­ment the related cal­cu­la­tion formula. You can use this example as a guide:

• Excel cal­cu­lates the number of “entries” with the COUNTIF function. For the employee Anne-Marie, this will be as follows: =COUNTIF(C7:I30;C35). This tells the table cal­cu­la­tion program: If in the cell fields C7 to l30 the entry from C35 (Anne-Marie) then it should be counted. If on row 42 under Peter, you add a further employee, the formula should be =COUNTIF(C7:I30;C42) etc.
   
• In the work schedule example, 8-hour shifts apply. To calculate the total weekly hours of Anne-Marie’s shift, then the number of entries must just be mul­ti­plied by 8. The ac­com­pa­ny­ing formula is: =D35*8. For an ad­di­tion­al employee, the formula would be =D42*8.
   
• Excel cal­cu­lates gross income in the same way. To do this, enter the formula =E42*F42 in the example.

Your work schedule template is now adapted and can be used again and again for future staff man­age­ment plans.

The practical thing about your work schedule Excel template is that it can be used over and over, instead of having to build a new table from scratch each time. For every week, you can simply save a new .xlsx file. It’s even more practical, though, if you create new sheets in the same Excel table and directly paste the work schedule template into it. In this way, you’ll have a clear overview and can in­cor­po­rate several weekly plans into one monthly plan.

Pasting your work schedule template into a new sheet doesn’t take much effort, but sur­pris­ing­ly does not work with the approach suggested by Microsoft. The problem: While content and for­mat­ting are com­plete­ly the same, the same does not apply to column width. Instead, the result will be distorted, since Excel uses default values.

Here is a solution that actually works:

1. In the bottom left corner of the Excel window, click on the “+” button to add a new sheet.
    
2. Right-click on the new sheet and select “rename” to rename it by the given calendar week.
    
3. You can also change the “tab color” of your sheets to visually separate them from one another.
    
4. Click on the sheet with your work schedule template and mark the relevant field in the table. Pull your mouse across the area that includes the cells A1 to l41. Careful: Marking the entire sheet with the key com­bi­na­tion [CTRL] + [A] or by clicking on the in­ter­sec­tion between the column and row names will not lead to the desired result.
    
5. Use the key com­bi­na­tion [CTRL] + [C] to copy the marked field.
    
6. Go to your new sheet, click on the cell A1 and use the key com­bi­na­tion [CTRL] + [V] to paste in the work schedule template. The table contents and the cell colors, lines, and font for­mat­ting will also be carried over, but not the column widths, which keep their default settings.
    
7. The solution: While the pasted table is still marked, click on the SmartTag “(Ctrl)” in the bottom-right corner of the marked field.
    
8. In the opening context menu, click on “default width” under “enter.” The original for­mat­ting of your work schedule template will be fully restored.
    
9. Al­ter­na­tive­ly, you can mark the cell A1 in your new sheet and, under the start tab, click on the arrow in the menu bar under the “enter” button. Here, select the “enter content” option. In the opening window, mark “all” under the “enter” point. In a second, separate window, repeat this step, this time marking the point “column width” and then click on “OK.”

Sub­se­quent­ly, you should enter the current calendar week in cell C3 (including related details) so that your employees can see straight away which planning period the work schedule applies to.

Using the free work schedule template is super simple: Mark early, middle, late, and night shifts by using the “fill color” tool to mark these as different blocks, and type in the names of the employees on duty once (!) per shift. The cal­cu­la­tion module will au­to­mat­i­cal­ly count the items and will calculate the cor­re­spond­ing weekly hours and the gross income.

If you have created a drop-down menu, then you can just copy and paste it into the correct locations and select the suitable employee for each assigned shift.

Once you’re happy with the work sched­ul­ing for this week, you can:

• Print the Excel work schedule and hang it up, for example, in the break room
• Save the Excel work schedule as a PDF file and send it in an email to your employees

Click here for important legal dis­claimers.