OmniDocHow to make an Excel schedule: from table structure to filling in
Use Excel to make a shift schedule from scratch, explaining how to arrange the three parts of personnel, dates and shifts, how to use shift codes and drop-down lists to reduce manual input, and how to count working hours and check before printing.
Key points of this article
- Personnel is arranged in rows, dates are arranged in columns, and shifts are filled in with short codes. This is the basic structure of the schedule that is easy to maintain.
- The shift code is unified into a single letter or a short text, and an illustration is attached to illustrate the corresponding time period, making it easier for others to understand
- Use data validation to set up drop-down lists to reduce the chance of entering the wrong shift
- Use COUNTIF to count labor hours per person and number of people on the job every day. The numbers will be updated automatically after changes.
- Check pagination and merged cells before printing to avoid people rows being cut off
Shift schedules are commonly used by small teams, stores, and duty groups. You can see who works on which day and at which time at a glance. Many people don't know where to start after opening Excel, and end up with a bunch of merged cells. If another person changes the table, they have to rework the entire table.
This article is written for people who need to make their own schedules, such as supervisors leading groups, club officers, or store managers. After reading, you will know what areas should be included in the shift schedule, how to use shift codes and drop-down lists to reduce manual input, and how to count working hours and check them before printing.
Only the basic functions of Excel are used throughout the process, no macros or complex formulas are required.
How to make a schedule? One sentence conclusion
One sentence conclusion: Make rows for personnel, columns for dates, and short codes to fill in shifts, and you can create an excel schedule schedule that can be modified repeatedly.
| your situation | Recommended practices | reason |
|---|---|---|
| Small number of people, fixed days per week | People are rows, dates are columns, one table per week | The structure is simple, and everyone can see it every day at a glance |
| There are multiple departures in the morning, noon and evening | One row for each person, one column for each day, fill in the shift code | The code is short and easy to count and filter. |
| Need to calculate labor hours per person | Use COUNTIF to count by code at the end of the table | Avoid hand calculation errors and automatically update after changes |
| Have to redo it every month | The date is generated using a formula. When changing the month, only the starting date is changed. | No need to redraw the form |
| Maintenance by multiple people | Shift rules and personnel lists are placed on a separate worksheet | Modifications do not interfere with each other |
Template structure overview
| Part | What do you write? | Length suggestion |
|---|---|---|
| Title area | Schedule name, start and end date, department or group | 1 Murray 2 lines |
| Date line | The date and week of each day | 1 line, covering a week or a month |
| Personnel column | Name, position, contact information as needed | One line per person |
| Shift area | Daily flight code for each person | Main body part |
| Statistical area | Per person's working hours and the number of people on duty per day | 3Murray 5 articles |
| Legend area | Time period and comments corresponding to each code | 3Murray 5 articles |
# how to write part by part
title area
The title is written as "[team name] schedule [start date] to [end date]", with a separate line. Merge cells are used only on the title, and do not merge the rest of the table, otherwise you cannot sort and filter later.
date line
Enter the start date in the first date cell and drag the fill handle to the right to generate the date continuously. The week can be placed on the next line of the date and obtained with the TEXT function, for example, write = TEXT ([date cell], "aaa") in the corresponding cell. The display format of language settings in different systems is slightly different, and the actual display shall prevail.
personnel column
Each person has one line, the name should not be repeated, and the post should be placed in the column to the right of the name. Example: [name], [position]. If the personnel will change, it is recommended to leave a few blank lines at the end of the list to facilitate subsequent insertion.
shift area
Fill in with a short code, for example: a for morning shift, B for middle shift, C for evening shift, X for rest. The code should be unified. Don't write "morning" and "morning shift" for a while. Example: [name] fill in An on Monday and X on Tuesday.
Then use the data validation to set the drop-down list, and the allowed values are written as A _ Magi B ~ M C ~ X. When typing in this way, you can only choose from the options, which can reduce typos. Then use the conditional format to fill different codes with different background colors, and you can see who is in which class at a glance.
Statistical area
Working hours per person: assuming that the length of each shift is [shift length] hours, you can write = COUNTIF ([shift region], "A") [shift length] + COUNTIF ([shift region], "B") [shift length] + COUNTIF ([shift region], "C") * [shift length] at the end of the line, and replace the contents in square brackets with your own shift length.
Number of people on duty every day: write = COUNTIF for each column at the end of the table ([shift area on that date], "A"), and count the number of morning, middle and evening shifts respectively. If the number of people on a certain shift is 0 on a certain day, it means that there are people missing here.
illustration area
Write down the time period on a separate line for each code, such as "A = [start time]-[end time]". The legend is placed at the bottom of the form or on the right. After changing the person in charge, the newcomer can also understand it.
# arrange the version in the editor
- First adjust the column width so that the date and name can be fully displayed, and the date column width is the same.
two. The title should be bold and slightly larger, with no more than three colors inside the form to avoid interfering with reading.
- Freeze the first row and column, and the date and name are always visible when scrolling.
- Set up the printing area, the weekly schedule is recommended to print horizontally, and the margins are set aside for binding.
- Check in the print preview to see if any person lines are divided into two pages and adjust the zoom if necessary.
- Save in .xlsx format with a date on the file name, such as "schedule _ [year and month]" to facilitate the tracing of historical versions.
If you need to send the schedule to someone for review, you can export it as PDF and share it again, so that the layout will not change when the other person opens it.
# Common Writing problems
The number of flights is written directly into long sentences in Chinese, such as "from 9: 00 a.m. to 5: 00 p.m.", which cannot be counted. -make a separate form every week, and you have to change several at a time. Omissions are very common. -merge a large number of cells, resulting in errors in sorting, filtering, and formula references. -there is no legend, after the replacement, others can not understand the meaning of the code. -do not count the number of people on duty every day, the missing posts will not be found until they are on the spot. -apply the online template directly but do not check it, and the structure does not match the actual shift rules.
# frequently asked questions (FAQ)
1. Is the schedule made in Excel or in regular documents?
Scheduling needs repeated revision and statistics, and Excel's formula and grid structure are more appropriate. Ordinary documents are suitable for one-time display, changes and summarization are more laborious.
2. How to mark holidays or rotational days?
You can set up another code, such as "section", or distinguish it with a background color and explain the meaning in the legend. Try to write a separate line of notes on the scheduling rules for holidays to facilitate follow-up check.
3. Will the statistics go wrong after the increase or decrease in the number of personnel?
Insert a row in the middle of the personnel list, and the range of the formula is usually automatically extended. If you write the newcomer in the blank space outside the form, the statistical scope may miss him, so the new recruits try to insert between the existing lists.
4. Can the online free scheduling template be used directly?
You can look at these points when choosing: whether registration is required, whether there is a watermark, whether the file can be opened properly, whether the structure can be modified, and whether personal information needs to be filled in the form. When it comes to name, phone number and other information, it is recommended to deal with it in the local form and do not upload it to an unknown website at will.
# Summary
The key to the schedule is to determine the structure first, then unify the shift code, and finally use COUNTIF statistics and check before printing. In this order, after an excel schedule is made, it can be reused for a long time only by changing the start date and personnel arrangement every month.
