Create roster in Excel: a quick guide

With changing working hours, it is not always easy to keep an overview. An uncomplicated roster in Excel can be very useful. We show you how to quickly create a shift or work plan with Office 2010.

The structure of the shift plan

Our roster basically consists of only three components:

• The header, in which the different shift types and the associated times are listed.
• The sidebar with the date and day of the week
• The table in which you enter your respective layers.

Shift plan for workers

This roster is primarily intended for workers who want to write down their shifts.

• If you want to create a roster for your employees, first follow these instructions.
• Then simply add a few lines under the days of the week - depending on how many employees work per shift.
• There you then enter the names of your employees who you want to assign to the relevant shifts.

1. The format of the work calendar

First, format the rows and columns according to your requirements.

1. Go to the column header and click on column D.
2. Move the mouse over the right line boundary and drag the column to a width of 5 cm.
3. Double-click the "format symbol" in the quick start bar of the "Start menu".
4. Then format the columns F, H, J and L 5 cm wide in the same way.
5. Increase columns E, G, I and K to 15 cm each.
6. For the M column, select a width of 30 cm.

2. Label the header of the roster

Enter your shift types in the header. In our example, this would be the early, intermediate, late and night shifts. If you have fewer or more layers, simply adjust the fields accordingly.

1. In the first row you start with cell A1 and enter "January" or another month there.
2. Cell E1: early
3. Cell G1: Between
4. Cell I1: late
5. Cell K1: night
6. Cell M1: notes
7. In the second row, write the respective office hours.

3. Format the time slots in the shift schedule

As is so often the case, there are of course a wide variety of design options. In our example, we chose two columns, each of which lists the date and the associated day of the week.

1. Click on column B in the header of the worksheet.
2. Right-click to open the context menu.
3. Select the "Format cells" option.
4. Then click the "Numbers" tab.
5. In the "Category" select "Date".
6. Under "Type" select the fifth entry in the form "14.03.01".
7. Confirm your selection with "OK".

4. Fill the two columns of the roster with life

In the following step you quickly enter the date and day of the week.

1. In the first step, click on cell B4.
2. Enter the date "01/01/14" there.
3. Then click on the small square you see at the bottom right and hold the mouse button down. Drag the mouse down until the last day of the month appears.
4. The procedure in cell C4 is almost identical. Click on the cell.
5. Enter the first day of the week - in our case Wednesday.
6. Then do the same as for the date. Click on the small, black square and drag the days of the week down with the left mouse button pressed until the end of the month.

5. Finishing touches on your work calendar

Finally, a few visual measures such as font size, bold font and fixing the top row. Thanks to the fixation, you can still see the header of the shift plan even if you have to scroll down. You can find further tips on the subject of "Fonts in Excel" here.

1. Click on the edge of line 1. Use the symbols at the top of the program to set the font size to 14 and bold and center the text.
2. In line 2, set the font size to 12 and also align the text in the center.
3. Select the "View" tab at the top of the program.
4. Click on the menu item "Freeze window".
5. Select the "Freeze Top Line" option from the context menu.

$config[ads_text5] not found

Now we adjust the frames.

1. Then mark the cells from E5 to K4 and down to E34 - K34.
2. Press the right mouse button and click on "Format cells" in the context menu.
3. Select the "Frame" tab.
4. Select the "Interior" type under "Preferences".
5. Confirm with OK".
6. Hold down the [CTRL] key and click on columns F, H, J and L.
7. Go to the table symbol in the tab menu and select "No frame".

6. Enter services

Now you can enter your layers, for example by writing in X in the corresponding cell. Or you can click in the corresponding cell and use the "Fill cells" button to select a nice color with which you mark your service.

$config[ads_text6] not found

• In our example, we also highlighted the Sundays in color for clarity. To do this, mark the corresponding cells and select a suitable color with the "Fill cells" button.

If you want to create an annual calendar right away, simply copy your newly created service calendar and modify the two columns, date and weekday. Here you can find out how to rename the individual worksheets in the tab below.