If you know how to make schedules in Microsoft Excel, you can save time and effort when creating and sending work schedules to staff. For example, saving time when making team schedules is as easy as creating a template in Excel.
In addition, the time it takes to make changes to a digital file is typically far less than the time it takes to create a new paper plan each week.
How to make a schedule in Excel:
Step 1. Creating And Setting Up A Schedule In Excel
First, you need to start Excel, and an empty new worksheet should be opened. Rename the sheet tab to Weekly Schedule.
Then, set your margins in the Page Layout tab. Select the Wide option or the one with one-inch margins.
The one-inch margin should be plenty if you want to print this and afterward hole-punch and file it in an event organizer.
Under the View tab, select Page Layout from the workbook views.
Step 2. Add The Dates Of The Week
In row 1, you will insert the dates for the schedule. Select the second cell (B). Enter =Date( the first field you need to add is the Year followed by a comma, like this 2023. Then enter the corresponding month as a number followed by a comma again.
Finally, add the day followed by the closing bracket and press Return (enter Button). It should look like this: =Date(2023,1,15) for 15 January 2023.
Now select the cell next to it. Reference the first cell with the Date you entered and add +1 in the formula bar. To do this, enter =B1+1 (enter the number of the cell you used).
Then, you will copy and paste this to the adjacent columns and drag copy from C1 ending with column H1.
To drag copy, select the second cell and hover your mouse on the bottom right corner where there is a +. Left-click and drag to column H.
Step 3. Adding The Days Of The Week
In row two, just beneath the dates, you will type the days of the week, starting with Monday. And then, once you type in Monday, you can drag-copy to auto-fill the rest of the weekdays.
For example, drag it up to Sunday. If you don’t want to add Sunday to the schedule, drag it to Saturday.
You will need to delete the Date in row 1 for Sunday.
Step 4. Adding Time Slots For Each Day
Next, you will add blocks of time along the left side in row 3 (A3). Now add half-hour blocks starting at 8:00 am (Enter 08:00, then space bar, then am, and press return(enter button)).
Again, it’s important to type out 8:00 am, so it’s automatically formatted as time.
Then, in the next cell below this one, enter 8:30 am, and once you have those two, select both cells with your mouse and drag down to auto-fill the rest of the time slots.
You can extend this until 6:00 pm or go to whatever time you want. Finally, update this time column header A2 to say time; if you wish, you can bold column A.
Step 5. Formatting Your Schedule Into Table
Ok, now let’s put some styling on the schedule. So next, you can select the row with the days of the week, the time slots, and all the areas. Everything except for the dates row along the top.
To create a Table, you will need to do the following. The date field row was removed because it contained a formula that could not be used in the table’s header.
To format it as a table, ensure you are in the Home tab. Under the Styles section of the Home ribbon, choose Format as Table.
Then, select a color scheme from the dropdown menu that speaks to your group’s culture. While you have everything selected, still in the Home tab under the alignment area, format the selection so everything is Center, Middle Aligned, and Wrap Texted.
Note that you convert it as a table for presentational purposes, so the table’s layout remains unaltered when you copy and paste data from within the cell sections.
Step 6. Format The Date Row To Reflect The Table Format
You can format the dates row to blend in better with the table. To make it easier, highlight the row color you want.
For example, in row 3, where the days of the week are, select cells B3-H3, then click on the Format Painter in the clipboard section of the Home ribbon to color your dates in row one (Dates).
It shades them to the desired color, but you also lose the date formatting, so be sure to reformat after you shaded the row.
Select the date row and under the Number section on the Home ribbon, click on the dropdown menu and select Short Date.
Alternatively, follow these steps to choose the display type:
With the dates still selected, right-click, choose Format Cells, and select the Date option from the category column.
Then choose the format type from one of the options to display the month and date numbers the way you like it. Then click OK.
Step 7. Setting Up An Activities Sheet
The most convenient way to work within your schedule is to create a task or activities list you can select when adding an item to the schedule.
Next, add another sheet to your workbook to list items and meetings that you can add to your calendar.
Finally, you can rename this sheet to “Activities.”
Step 8. Create The Dropdown Activities List
In the activities sheet, create a list you will reference in your table with these items.
Again, it will reduce your scheduling time if you can select one of these activities from a dropdown list to add to the schedule and not need to type it out every time.
You can call this your Activities list by adding it to the top of the list and adding things like Team Meetings, Planning Sessions, and blocking out time for productivity.
Also, remember to include Lunch, tea breaks, and any other activity your team encounters regularly. If you can’t think of all the meetings and activities at this point, don’t worry. You can keep adding to this list later.
Now select the list, including all the activities and heading. Then, in the Home tab, select Format to Table and check the box that says “My table has headers.” Excel will automatically assign a name like Table 1 to this table. Still, you want to set your own name on this table.
You will find the Name Box in the left corner of the sheet above the A column.
Select the list without the heading and enter Activities as the list name.
Giving it a name that you choose is important because as you keep adding activities to this table, new entries will be added automatically to the Activities named range.
Step 9. Adding The Activities List To Your Schedule
Now go back to the calendar schedule and select the open schedule area where you would add the activity items. Then, with this area selected, go to the Data tab and choose Data Validation.
Here, you choose the list option under Allow within the Settings tab. Then, in the Source section, type = Activities (the name you gave your list). Next, select the Error Alert tab, and uncheck where it says, “Show error alert after invalid data is entered.”
If you leave it unchecked, it allows you to type a new item and not necessarily add it to your dropdown list if it’s a one-time event. And when done, click OK.
You can schedule activities simply by selecting them from the dropdown list or entering items manually.
How To Save The Schedule As A Template
You’ll have the option to use it again if you store the schedule as a template rather than always making a new one. Or clear all the items in the one you’re using now.
- First, select the file with the schedule.
- Then you go to export.
- You can change the File Type.
- Then select a template.
- Go to Save As. Then the Save As box will open.
- Open the folder labeled “Custom Office Templates.”
- After giving the template a name, click on Save to store it.
- If you need to use the template in the future, go to the New screen and pick the Personal option. Once there, select the schedule template. It will start with a fresh workbook for you.
- Before printing the schedule, you will need to prepare the print area if you intend to use a hardcopy version.