Creating a work schedule with Excel: a guide

The work schedule or shift plan is an integral part of staff management within a company. It is intended to ensure that the right number of employees with the right qualifications are in the right position at the right time. To achieve this, the work schedule defines the start and end of working hours. It therefore offers employees a practical overview of when they have to do their work.

The work schedule itself is quite a simple document. However, in order to provide orientation for the staff, it must meet certain formal (and possibly legal) requirements. Although there are numerous software solutions for scheduling, many of them involve monthly subscription costs. A common program is sufficient to create a professional, homemade work schedule: Excel.

As a planning tool for employers and guidance for workers, each work schedule should meet certain requirements:

• It must contain all essential information, including employees’ first and last names, working hours, and the planning period for which the work schedule applies.
• It should not just make sense to the manager creating it, but also to the employees. The work schedule should therefore be limited to the essentials for the sake of clarity (also make sure that the text is not too small) and be as an uncomplicated and self-explanatory as possible. If in doubt, it is a good idea to add an explanatory note.
• A visually appealing design is also an advantage. For example, empty rows or columns can loosen up the layout. Different colours also make individual areas stand out from each other.

It is also important to ensure that your work schedules comply with predictive scheduling laws or fair scheduling laws. The purpose of these laws is to ensure that workers are being assigned a fair amount of work, and to ensure that their work-life balance is being prioritised. Some laws also cover availability of additional work hours or call-in pay.

There are some steps you can try to take when creating your work schedule to ensure that you are complying with best practice. Employers should be able to give a reasonably accurate estimate of their potential schedule upon hiring them, to ensure that workers are not being promised hours that they will not receive, or being manipulated into working additional hours that they are not available for. Work schedules should also be issued well ahead of time (two weeks’ notice is ideal where possible), and employers should try to be flexible and accommodating with employee shift requests or overtime requests. Naturally, employee rest times should be prioritised so that employees are not exhausted and can have a work-life balance. Employees should be made aware of any changes to the work schedule with plenty of notice and employers should consider offering overtime or premium pay for any last minute schedule changes.

If you are unsure whether your work schedules are following best practice or complying with local laws, be sure to consult a legal professional to ensure that you are legally in the clear. Working hours and scheduling is overseen by ACAS in the UK.

Basically, our sample work schedule consists of the following three areas:

• A permanently visible header with the different shifts and corresponding clock times
• A sidebar with automatically generated, continuous dates
• A table where the names of employees on duty can be entered and which also calculates the number of shifts performed by each employee

In this section, we’ll show you how to format table columns and rows, and fix the header at the top of the screen. We will also give you tips on how to use Excel shortcuts:

1. To adjust the width of the columns in your schedule, you can simply click (hold) on the column boundary and drag it to the desired width (the current width is displayed in a small window over the mouse pointer).
2. Alternatively, you can click on a column and enter the desired value in the “Start” tab under “Format” à “Column” width.
3. Using the “Transfer Format” button (located in the upper left corner of the Start tab) you can even speed up your work: To do this, first click on an already formatted column, then on the button in question and finally on the column to be formatted. This saves you having to adjust all table columns manually.
4. In our example, column A (December 2019) has a width of 35; columns C,E,G, and I have a value of 20; empty columns B, D, F, H, and J, which are meant to break up the schedule are only 40 pixels wide (corresponds to a value of 5). You can set the columns K and L as you like – depending on how much space you have left.
5. To format the cells for individual layers, just type in “Early Shift” (or midday, late, or night shift) and then use the Excel shortcut [ALT] + [ENTER) to manually break lines. Now type in the time “6:00-10:00.” By double-clicking the cell last, you can adjust the font size of the shift name and time and mark things as bold.
6. Finally, fix the header at the top of the screen so that it is still visible when you scroll down the work schedule. To do this, navigate to the “View” tab and click on “Fix top line” under “Freeze Window.”

Here, you can see a video tutorial on how to use the “Transfer format” function of Excel correctly:

In this section, you will learn how to set a sequential date for your work schedule (this particular one excludes Sunday, as this is often a day of closure for businesses):

1. In cell A2 (column A, row 2), type the first date of the month that does not fall on a Sunday (e.g. December 2^nd 2019).
2. Right-click on the cell and click on “Format cells” in the drop-down menu that opens.
3. Select the “Numbers” tab in the following context menu and click on “Wednesday, March 14th 2012” under the “Date” category to display the date together with the day of the week (of course you can also choose a different display method).
4. Click on the A3 cell below and copy the following formula into it to exclude all Sundays from the consecutive dates: =WHEN(WEEKDAY(A2;2)=6;A2+2;A2+1).
5. Format this cell just like “Wednesday, March 14th 2012.” Instead of a cryptic sequence of numbers, the second date of the month should now be displayed, unless it falls on a Sunday (which is omitted).
6. Select cell A3. Click on the small square in the lower-right corner of the cell and drag it down until you reach the last date of the month. Then release the mouse pointer. Excel now automatically adds all dates without Sundays.
7. If you want to leave out the whole weekend, it’s even easier: enter the first date of the month and drag the small square down until the whole month is listed. Now, click on “Auto Fill Options” in the lower right corner of the highlighted area and select “Fill Weekdays.” Excel will now remove all Saturdays and Sundays from the list.

The following video explains the basic procedure for automatically filling in dates in Excel:

The last section explains how to have Excel calculate the shifts of your employees and make any final changes. We will also give you a few tips on macros:

1. Choose an area of your work schedule and list the names of all your employees. Write each employee’s name in a separate cell. In our example, Matthew S. is assigned to cell L9.
2. Enter the following formula in the adjacent cell M9: NUMVERIF(C2:I29;“Matthew S.”)
3. In our example, area C2:I29 represents the totality of cells in which employees can be entered and counted in their respective shifts.
4. Excel now automatically counts all Matthew S. entries in the work schedule and displays the total in cell M9. If you enter additional shifts for Matthew S., the numerical value is updated automatically.
5. Formatting the layout of your work schedule should be done at the very end. You can use the “Fill color” button on the “Start” tab to colour rows, columns, or individual cells differently in order to highlight them. Remember, however, to explain the colouring using a legend for your employees.
6. Do not forget to activate the border lines with the button of the same name. This is the only way to clearly separate the individual cells of your tabular work schedule.
7. Finally, you can protect your work schedule from being edited by others by activating “write protection” in the “Check” tab.

The following video explains how to use the “=COUNTIF” function in practice:

You can now use your finished work schedule for all coming months. All you have to do is create a new Excel worksheet for each month (click on the small “+” sign at the bottom left of the window) and copy your work schedule template into it (key combination [CTRL] + [C] and [CTRL] + [V]). You can then right-click and “Rename” the worksheet with the current month and add a “Register Color” to better distinguish the worksheets from each other.

However, you will notice that the column width of the template is not copied during the copy process. Microsoft suggests a certain procedure for this, but it does not work in practice. Proceed as follows instead:

1. Select the work schedule template manually by dragging an area with the mouse that covers all relevant cells.
2. Copy the table into your new worksheet.
3. Click on the SmartTag “(Ctrl)” in the lower-right corner of the inserted table. A context menu opens.
4. Under “Insert,” click the “Keep width of the original table (B)” button. The correct column width will now be restored.

Do you want to enter several employees in one shift? Proceed as follows: Right-click on the cell below the relevant shift, click on “Insert cell” and then on “Entire row.” With the option “Delete cells,” you can also delete them at any time.

Once you have divided all your employees into their respective shifts, all you have to do is save your work schedule – preferably as a PDF file. You can then print it out and use it as a notice board or send it directly to your employees by e-mail or intranet.

Please note the legal disclaimer relating to this article.