You can easily create a schedule with Excel. The work schedule table is quickly created and can then be easily modified.
Create a schedule in Excel – this is how you do it
If your job involves changing working hours, a duty roster makes it easy to keep track of things. To create a duty roster in Excel, you first create a “basic framework” in which you later only enter the respective shifts. For the sake of simplicity, our example roster consists of only two shifts, an early and a late shift.
- Column A: Enter the heading “Date” in the first row. Right-click in the second row to open the context menu. After clicking on “Format cells”, select the “Numbers” tab in the next step. Under Category, select “Date” and on the right-hand side, select the format “Weekday and Date”. Finally, confirm your selection by clicking on the “OK” button.
- Cell A2: After clicking on cell A2, you will see a small black square in the bottom right corner. If you move the mouse pointer onto it and drag the square down while holding down the left mouse button, the date will be entered consecutively.
- Column B: The cell B1 receives the heading “early shift”. Column C: Enter the text “late shift” in the cell C1. Column D: Select the heading “notes” for this column.
- Formatting: For visual reasons, it makes sense to make columns B and C the same size. To do this, right-click in the respective column header and select the “Column Width” option from the context menu.
- Alternatively, drag column B to the desired width. Then click on column B and go to the “Clipboard” section in the “Home” tab of the ribbon. There, first click on the “Format Painter” broom icon and then on column C. You can then resize column D as needed.
Excel duty plan – the finishing touches
Finally, there are a few visual adjustments to be made, such as the Excel fonts, font size, and fixing the top row. First, click on the number 1 on the left. This selects the entire row. This has the advantage that you can format the contents of all columns in the first row at the same time.
- Once the “Home” tab is activated, make the appropriate settings in the two ribbon areas “Font” and “Alignment”.
- For example, it makes sense to select a slightly larger font for the header, to make it bold and to align it in the center. Then switch from the “Home” tab to the “View” tab in the menu bar.
- In the “View” tab menu, go to the “Window” section. Then click on the “Freeze Panes” menu item and select the “Freeze Top” option in the context menu. This ensures that the first row is always visible, no matter where you are in the table.
- Finally, you can also customize the table frame to suit your preferences. To do this, either highlight the entire table or the corresponding sub-area. Then, right-click and select “Format cells” from the context menu.
- After you have activated the “Frame” register card, the various design options will be displayed. In addition, you will find the fill color icon in the “Fonts” area, which you can use to highlight individual cells, rows or columns in color.
Excel duty plan – for multiple employees or as an annual duty plan
If you need the duty plan for the scheduling of multiple employees, you can also use the small Excel table. However, it should not be more than a maximum of ten employees. Otherwise, the duty plan in Excel quickly becomes confusing.
- One option for using the duty roster for multiple employees is to use abbreviations. Provide each employee with a unique abbreviation and then enter the abbreviations in the respective shift column.
- Alternatively, you can add the calendar weeks to the shift plan in Excel in column A and enter the names of the employees below them. After you have created the first calendar week, you can copy the others and just change the week number.
- If you want to create a shift plan for a full year, create the first worksheet for one month. Then copy the worksheet into the Excel folder a total of eleven times, so that you end up with twelve worksheets. Modify the first column “Date” in each individual duty schedule. Then label the individual Excel worksheets in the lower name register of the workbook with the corresponding month names.
