Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

How to Create a Monthly Schedule in Excel (3 Simple Ways)

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.


Download Practice Workbook

Free Template.


3 Ways to Create Monthly Schedule in Excel

Here, we will show you how to create a monthly schedule manually, using Excel Templates and Combo Box 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.

Using Excel Templates to Create Monthly Schedule in Excel

  • 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.

Inserting Excel Template to Create Monthly Schedule in Excel

  • 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: Changing 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.

Changing Titles to Create Monthly Schedule in Excel Using Excel Templates


Step-03: Using 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.

Using Data Validation Feature to Create Monthly Schedule in Excel Using Excel Templates

  • Now, the Data Validation box will appear.
  • Next, click on Clear All.

Opening Data Validation Box to Clear Previous List to Create Monthly Schedule in Excel

  • 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.

Using Name Box to Create Monthly Schedule in Excel Using Excel Templates

  • 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.

Opening Data Validation Box to Create New Drop-Down List to Create Monthly Schedule in Excel

  • 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: Changing Values for Creating Monthly Schedule

Next, you can also change the fields that are describing 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.

Changing Values for Creating a Monthly Schedule in Excel

  • 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: Hiding 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.

Hiding Column to Create Monthly Schedule in Excel Using Excel Templates

  • Thus, you can create your own Monthly Schedule based on your preferences by using Excel Templates in Excel.

How to Create Monthly Schedule in Excel Using Excel Templates

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: Creating Dates in Month

  • Firstly, insert the Shifts (or the Fields you want to add) and Year to create a monthly work schedule in Excel.

Manually Creating Monthly Work Schedule in Excel

  • Then, insert 1 in Cell C8.

Creating Dates in Month for Manually Creating Monthly Schedule in Excel

  • 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.

Changing Column Width to Create Monthly Schedule in Excel

  • Next, the Column Width box will appear.
  • After that, insert 5 as Column width.
  • Then, click on OK.

Opening Column Width Box to Create Monthly Schedule in Excel

  • 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 weeks.

  • Now, you will get all the days of a month like the image given below.


Step-02: Inserting 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.

Inserting Titles to Create Monthly Schedule in Excel Manually

  • 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: Using 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 in 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.

Using Data Validation Feature to Create Work Type Drop-Down to Create Monthly Schedule in Excel Manually

  • 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.

Selecting Work Type From Drop-Down to Create Monthly Schedule in Excel Manually

  • Similarly, add values to the rest of the Cells.

Read More: How to Create a Recurring Monthly Schedule in Excel


Step-04: Formatting 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.

Formatting Dataset to Create Monthly Schedule in Excel Manually

  • After that, select All Borders.

Selecting Border to Manually Create Monthly Schedule in Excel

  • 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-05: Inserting 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, E as the short form of Morning, Day and Evening shift in Cell range C9:AG13.

Inserting Data and Using Conditional Formatting to Manually Create Monthly Schedule in Excel

  • Then, select Cell range C9:AG13.
  • Next, go to the Home tab >> click on Conditional Formatting.

Using Conditional Formating to Manually Create Monthly Schedule in Excel

  • 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 format.
  • Next, click on OK.

Opening Text That Contains Box to Create Monthly Schedule in Excel Manually

  • After that, the dataset will look like the image given below.

  • Similarly, use Conditional Formatting for the other shifts.

How to Create Monthly Schedule in Excel Manually

Thus, you can create a monthly work schedule in Excel manually.


3. Use of 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: Inserting 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.

Use of Combo Box to Create Monthly Schedule in Excel

  • Then, insert a Combo Box in Cell B4.

Inserting a Combo Box to Create Monthly Schedule in Excel

  • 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.

Opening Format Object Box to Create Monthly Schedule in Excel

  • 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 going through the steps shown in Method1.


Step-02: Adding 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)

Using DATE Function to Create Monthly Schedule in Excel

Here, in the DATE function, we inserted Cell B7 as year, Cell B4 as month, and 1 as day.

  • Next, press ENTER.

Adding Dates to Create a Monthly Schedule in Excel

  • 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: Formatting 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.

Formatting Cells to Create Monthly Schedule in Excel Using Combo Box

  • 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.

Opening Format Cells Box to Create Monthly Schedule in Excel Using Combo Box

  • Next, change the Column Width of Cell range C9:AG9 going through the steps shown in Method2.

  • 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 in the monthly schedule.


Step-04: Inserting 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.

Inserting Values for Creating Monthly Schedule in Excel Using Combo Box

  • Finally, use the Conditional Formatting to highlight these Cells going through the same steps shown in Method2.


Creating 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: Using 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.

Creating Monthly Time Schedule in Excel

  • Then, insert the value of the months in Cell range J4:J15 and the value of week numbers in Cell range K4:K7.

Using Name Box and Data Validation Feature to Create Monthly Time Schedule in Excel

  • 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 month in Cell C4 and week number in Cell F4 using two drop-down buttons created by using the Data Validation feature going through the steps shown in Method1.
  • Then, hide Column J and Column K going through the steps shown in Method1.


Step-02: Inserting 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.

Inserting Values for Creating Monthly Time Schedule in Excel

  • Finally, use Conditional Formatting to highlight these Cells going through the same steps shown in Method2.

Read More: How to Make a Daily Schedule in Excel (6 Practical Examples)


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. Let us know any other approaches which we might have missed here. And, visit ExcelDemy for many more articles like this. Thank you!


Related Articles

Arin

Arin

Hello, I'm Arin. I graduated from Khulna University of Engineering and Technology (KUET) from the Department of Civil Engineering. I am passionate about learning new things and increasing my data analysis knowledge as well as critical thinking. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to it.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo