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.

Excel with Office 365 and 1&1 IONOS!

Included in all Office 365 packages, use Excel to create spreadsheets and organize your data, starting from only $10/month!

Office Online
OneDrive with 1TB
24/7 support

Formal and legal requirements for a professional work schedule

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 colors 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. These types of laws are becoming more and more common at the state and local level in the USA. 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 prioritized. Some laws also cover availability of additional work hours or call-in pay.

While there is no federal legislation available on this topic, and state laws can vary from locality to locality, 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 prioritized 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.

Step-by-step guide: How do you create a work schedule with Excel?

Work schedules can look different depending on their application purpose. There are weekly schedules for individual employees as well as long-term shift schedules for the entire workforce.

Our example is the work schedule for a fictitious retail store that is open from Monday to Friday from 6:00 to 22:00 and on Saturday from 10:00 to 18:00 during the 2019 Christmas shopping season. When using a reusable monthly plan in Excel, it should clearly display the working times of all employees and at the same time offer some automatic functions that make it easier for the employer to plan personnel deployment. 

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
Note

The sample work schedule created here was made with Microsoft Excel 2013, but the user interface and procedure are very similar in all software versions.

Create header

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.
Note

Apart from the key combination [ALT] + [ENTER] for the manual line break, there are many other Excel shortcuts that can make working with the spreadsheet program much easier.

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

Formatting the time column

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 2nd 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.
Fact

An important component of Excel are the “Formulas,” which all begin with an actual equal sign (=) and accelerate the work with the table software clearly. The IF function is one of the most common of these formulas and is used to enable a logical comparison between a value and an expected value. Basically, it tells the program: “IF a certain value is true, do the following, if not, do something else.”

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

Tables

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 color rows, columns, or individual cells differently in order to highlight them. Remember, however, to explain the coloring 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.
Note

With the COUNTIF function, you can let Excel take over even more complex calculations. For example, the working hours can be derived from the number of shifts that will be undertaken. If you want to use particularly extensive command chains, you should consider creating a macro. This can reduce your workload and reduce the error rate for manual input.

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

Using the Excel work schedule

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.

Note

When reusing your work schedule Excel template, remember that you must re-enter the consecutive date each time. Just follow the step-by-step instructions again.

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.

Tip

It doesn’t always have to be Microsoft Excel: find out in the 1&1 IONOS Digital Guide what free alternatives to Excel are available.

Click here for important legal disclaimers.

1