Looking for ways to know how to create a monthly schedule in Excel? We can make our day-to-day life easier by making a schedule for every work. Here, you will find 3 ways to create a monthly schedule in Excel.
Watch Video – Create a Monthly Schedule in Excel
1. Using Excel Templates to Create Monthly Schedule in Excel
In the First method, you will find steps to use Excel Templates to create a monthly schedule in Excel.
Step-01: Inserting Excel Template
In the first step, we will insert an Excel Template to create a monthly schedule in Excel. Follow the steps given to do it on your own.
- Firstly, click on the File tab.
- Then, go to the New option.
- Next, type Monthly Schedule in the Search box.
- After that, click ENTER.
- Now, several Excel Templates will appear. Choose any sheet according to your choice. Here, we will click on the Employee absence schedule template.
- Then, click on the Create button.
- Now, a Monthly Schedule table will open.
Here, it’s an Employee Absence Schedule. You can edit it according to your schedule. Let me show you the process of changing it.
Step-02: Change Titles
Here, in the Excel sheet, you can change Titles by typing on the Cell according to your wish.
- Now, we will type Work Schedule in cell B1, change the titles Dates of Absence to Work Distribution, and Employee Name to Work Type.
- Additionally, we will insert 2022 in the Enter year box.
Step-03: Use Data Validation Feature
You can also discard the entries and add new data in Cell range B7:B11 using the Data Validation Feature.
- Firstly, select Cell range B7:B11.
- After that, go to the Data tab >> click on Data Tools >> click on Data Validation >> select Data Validation.
- Now, the Data Validation box will appear.
- Next, click on Clear All.
- Then, click on OK.
- Next, insert values that you want to add to those cells using the Data Validation feature in any other Cell range. Here, we will add different work types in Cell range AI7:AI11.
- After that, select Cell range AI7:AI11.
- Then, go to the Name box and Type Work_Type.
- Next, press ENTER.
- Now, select Cell range B7:B11.
- Afterward, go to the Data tab >> click on Data Tools >> click on Data Validation >> select Data Validation.
- Next, the Data Validation box will appear.
- Then, select List from the Allow drop-down >> select Work_Type as Source.
- Finally, click on OK.
- After that, select Cell B7.
- Then, click on the Button shown below.
- Next, select any option of your choice. Here, we will select the Presentation option.
- Now, it will show like the image given below.
- Similarly, add data in Cell range B8:B11.
Step-04: Change Values for Creating Monthly Schedule
Next, you can also change the fields that describe the Schedule by going through the following steps.
- In the beginning, you can change the values of cell range B2:M2 by typing your desired fields. Here, we will type Shift in Cell B2 and then type Morning, Day, and Evening as new fields and M, D, and E respectively to represent them.
- Then, select the Cell range that contains the schedule for the whole month.
- Next, click on the Delete button from your keyboard.
- After that, insert M, D, and E as inputs of the schedule according to your preference.
Step-05: Hide Column
Finally, we will show you how you can hide the column that you may have created to make the drop-down list using the Data Validation feature.
- Firstly, click on the column you want to hide. Here, we will click on column AI to hide this column and Right-click on it.
- Then, click on Hide.
- Thus, you can create your own Monthly Schedule based on your preferences by using Excel Templates in Excel.
Read More: How to Create a Recurring Monthly Schedule in Excel
2. Manually Creating Monthly Work Schedule in Excel
Now, we will show you how to create a monthly schedule manually in Excel. Follow the steps given below to do it on your own.
Step-01: Create Dates in Month
- Firstly, insert the Shift (or the Fields you want to add) and Year to create a monthly work schedule in Excel.
- Then, insert 1 in Cell C8.
- Next, insert 2 in Cell D8.
- After that, select Cell C8 and Cell D8.
- Next, drag right the Fill Handle tool to add dates up to 31 of a month.
- Then, select Cell range C8:AG8.
- Now, go to the Home tab >> click on Cells >> click on Format >> select Column Width.
- Next, the Column Width box will appear.
- After that, insert 5 as Column width.
- Then, click on OK.
- Now, insert Sat in Cell C7.
- Next, insert Sun in Cell D7.
- After that, select Cell C7 and Cell D7.
- Then, drag right the Fill Handle tool to AutoFill the days of the week.
- Now, you will get all the days of a month like the image given below.
Step-02: Insert Titles
Next, we will add Titles to clearly describe the monthly schedule. Here are the steps:
- In the beginning, select Cell range C6:AG6.
- Then, click on Merge & Center from the Home tab.
- Now, type your preferred title. Here, we will type Work Distribution.
- Next, press ENTER.
- After that, go to the Home tab >> select 14 as Font Size and click on the Bold button to bold the text.
- Then, we will add JANUARY and Work Type as titles.
Step-03: Use Data Validation Feature to Create Work Type Drop-Down
Now, we will show you how to use the Data Validation Feature to create a work type drop-down in Excel to create a monthly schedule.
- Firstly, add different fields that you want to add to the schedule in a specific column. Here, we will add Presentation, Field Visit, Conference, Report Submission and Meeting as Work types in Cell range AH4:AH8.
- Then, select Cell range AH4:AH8.
- Now, go to the Name box and type Work_Type.
- Next, press ENTER.
- After that, create a drop-down button in cell B9 using the Data Validation feature going through the same steps in Method1.
- Then, select Cell B9.
- Next, click on the drop-down button.
- Now, select any option from the drop-down button. Here, we will select Presentation.
- Similarly, add values to the rest of the Cells.
Step-03: Format the Dataset
Next, we will show you how to format the dataset to create a monthly schedule in Excel. The steps are here.
- In the beginning, select cell range B6:AG13.
- Then, click on the Borders button from the Home tab.
- After that, select All Borders.
- Now, the dataset will look like the image given below.
- Finally, format the dataset using Fill Color and Font Color according to your preference.
Step-04: Insert Data and Using Conditional Formatting
In the final step, we will show you how to insert data in the dataset and use Conditional Formatting to highlight those Cells.
- Firstly, insert M, D, and E as the short form of Morning, Day, and Evening shifts in cell range C9:AG13.
- After that, click on Highlight Cells Rules >> select Text that Contains.
- Now, the Text That Contains box will open.
- Then, insert M in the box and choose Light Red Fill with Dark Red Text as a format.
- Next, click on OK.
- After that, the dataset will look like the image given below.
- Similarly, use Conditional Formatting for the other shifts.
Thus, you can create a monthly work schedule in Excel manually.
Read More: How to Make an Hourly Schedule in Excel
3. Using Combo Box to Create Monthly Schedule in Excel
In the final method, you will find how to create a monthly schedule using Combo Box in Excel. Go through the steps given below to do it on your own.
Step-01: Insert a Combo Box
In the beginning, we will show you how to create a drop-down for the months using Combo Box in Excel. Follow the steps given below to do it on your own.
- Firstly, go to the Developer tab >> click on Insert >> select Combo Box from Form Controls.
- Then, insert a Combo Box in Cell B4.
- After that, type the value of 12 months in Cell range AH4:AH15.
- Next, select the Combo box and Right-click on it.
- Then, click on Format Control.
- Now, the Format Object box will appear.
- After that, insert Cell range AH4:AH15 as Input range, Cell B4 as Cell link, and 12 as Drop down lines.
- Next, click on OK.
- Then, select any month from the drop-down list. Here, we will select January.
- Afterward, add your desired year in cell B7.
- Next, hide Column AH by going through the steps shown in Method 1.
Step-02: Add Dates to Create a Monthly Schedule
Now, we will add dates to create a monthly schedule using the DATE function. Here are the steps.
- In the beginning, select cell C9.
- Then, insert the following formula.
=DATE(B7,B4,1)
Here, in the DATE function, we inserted Cell B7 as year, Cell B4 as month, and 1 as day.
- Next, press ENTER.
- After that, select cell D9 and insert the following formula.
=C9+1
Now, in the formula, we added 1 with cell C9 to get the consecutive date.
- Next, press ENTER.
- Then, drag down the Fill Handle tool to AutoFill the formula for the rest of the cells.
Step-03: Format Cells
Next, we will show you how to format cells to create a monthly schedule in Excel. The steps are here.
- Firstly, select cell range C9:AG9.
- Then, go to the Home tab >> click on Orientation >> select Angle Counterclockwise.
- Next, click on Row 9 and Right-click on it.
- After that, click on Format Cells.
- Now, the Format Cells box will open.
- Then, go to the Custom option >> Type ddd dd in the box.
- After that, click on OK.
- Next, change the Column Width of Cell range C9:AG9 going through the steps shown in Method 2.
- Then, format Cell range C9:AG10 according to your preference to create a monthly schedule. Here, we will use Cell range C10:AG10 to add fields to the monthly schedule.
Step-04: Insert Values for Creating Monthly Schedule
In the final step, we will insert scheduled works as values and use Conditional Formatting to highlight those Cells.
- Now, you can add different works or fields in Cell range C10:AG10. Here, we will add Weekend, Quiz, and Presentation as my preferred fields.
- Finally, use Conditional Formatting to highlight these Cells going through the same steps shown in Method 2.
Read More: How to Create a Weekly Schedule in Excel
How to Create Monthly Time Schedule in Excel
Additionally, here, we will show you how you can create a monthly time schedule by going through the steps given below in Excel.
Step-01: Use Name Box and Data Validation Feature
In the first step, we will add the value of Month and Week numbers using the Name box and Data Validation feature.
- Firstly, create a dataset with formatting like the image given below.
- Then, insert the value of the months in cell range J4:J15 and the value of week numbers in cell range K4:K7.
- After that, select Cell range J4:J15.
- Now, go to the Name box and type Month.
- Next, press ENTER.
- Then, select Cell range K4:K7.
- Again, go to the Name box and type Week.
- Next, press ENTER.
- After that, insert the value of the month in Cell C4 and the week number in Cell F4 using two drop-down buttons created by using the Data Validation feature and going through the steps shown in Method 1.
- Then, hide Column J and Column K going through the steps shown in Method 1.
Step-02: Insert Values for Creating Monthly Schedule
Finally, we will insert values to create a monthly schedule and use Conditional Formatting to highlight those Cells.
- Firstly, insert values in Cell range C7:I15 to create a schedule. Here, we will insert Breakfast, Lunch, Swimming, English Class, Math Class, and Piano Class as work to do in the schedule.
- Finally, use Conditional Formatting to highlight these Cells going through the same steps shown in Method 2.
Free Template.
Conclusion
So, in this article, you will find 3 ways to create a monthly schedule in Excel. Use any of these ways to accomplish the result in this regard. Hope you find this article helpful and informative. Feel free to comment if something seems difficult to understand.
