How to Make an Availability Schedule in Excel (with Easy Steps)

Get FREE Advanced Excel Exercises with Solutions!

While there are many ready-to-use templates available, creating a customized template that suits your specific needs can be even more beneficial. By creating your own template, you can include the columns, formatting, and functionality that are most important to you, such as calculating total hours worked or highlighting certain shifts or tasks. Additionally, creating your own template can help you better understand how Excel works and give you the ability to make changes and updates as your needs evolve. Overall, investing the time to learn how to make Excel schedule templates can save you time and improve your productivity in the long run. This article will guide you on how to make an availability schedule in Excel with easy steps.

Overview of How to Make an Availability Schedule in Excel

Download Practice Workbook

Steps to Make an Availability Schedule Template in Excel

Here, we are showing you a step-by-step procedure to make an efficient availability schedule template in Excel. You can use this template or you can also follow these steps to make a similar type of template according to your need.

1st Step: Add Necessary Header Information Prior to Your Availability Scheduling Table

To begin with, add some header information, such as the Scheduling table title, Company name, Jurisdiction area, Office location, etc to give it a formal outlook and set proper applicability.

Necessary Header Information Prior to the Table is Added

2nd Step: Add a Validation Period to Your Availability Scheduling

Consequently, add the Schedule start date from which this schedule will be applicable along with the date when this schedule will be expired. You can also mention the duration of this schedule.

Validation Timeline is Added

3rd Step: Add a Calender to Match Weekly Days and Dates Easily

Optionally, you can add a display calendar to your Excel worksheet to easily get the associated dates with the weekdays during this one-week period, whenever you need.

To do that, first, you need to click on Add-ins under the Developer tab. A new window will pop up. Inside the window, click on STORE. Then inside the search bar type “calendar” and press ENTER. From the search result, click on the Add button beside “Mini Calendar and Date Picker”.

Adding Daily Calendar

A mini calendar will be visible on your worksheet. Resize and place it anywhere suitable for gaining consistency in the look.

Mini Daily Calendar is Added

4th Step: Build a Skeleton of Your Availability Scheduling Table

After that, create a table, which is going to accommodate your scheduling information. You need to specify the number of rows and columns and draw the table accordingly. You can also merge cells inside any rows or columns to give the table a more organized and suitable outlook.

Skeleton of the Scheduling Table is Built

5th Step: Add Necessary Headings to Each Column

After you are done with building the table skeleton, you need to give the necessary headings to the columns you just added to the table. You can add as many columns as you want to accommodate necessary information inside the table. But do not flow the table with unnecessary columns which can damage the integrity of the table.

Necessary Column Headings are Added

6th Step: Add Necessary Data to Each of the Rows

The row number is equal to the number of people for whom the scheduling is applicable. Add as many people as you need and fill the rows with corresponding information according to the columns.

Necessary Rows with Corresponding Information are Added

7th Step: Generate a VBA Worksheet Event to Improve Readability

Finally, you can generate the following VBA code, which will automatically turn the cell color into red whenever an employee is not available for any particular day. Also, if later that person is somehow available, the cell will be colorless automatically.

Now, to write this VBA Code, first launch the Visual Basic Editor by clicking on Visual Basic under the Developer tab.

Opening Visual Basic Window

Then, double-click on the sheet name inside which you want to write the code.

Writing Space for the Generated Code

Finally, inside the worksheet event, you can write the code to get your desired output (If you are using VBA for the first time, you may need to add the Developer tab inside the ribbon Excel).

Code for Highlighting the Unavailable Employees

Code Syntax:

Private Sub Worksheet_Change(ByVal Target As Range)
If LCase(Target.Value) = "no" Then
Target.Interior.ColorIndex = 3 'Red color
Target.Interior.ColorIndex = xlNone 'No color
End If
End Sub

Code Breakdown:

Private Sub Worksheet_Change(ByVal Target As Range)

This is a worksheet event procedure that runs whenever a cell on the worksheet is changed.

If LCase(Target.Value) = "no" Then

This line uses the LCase function to convert the value in the target cell to lowercase, and checks if it is equal to “no”.

Interior.ColorIndex = 3 'Red color

If the value in the target cell is “no”, this line sets the cell interior color to red.


If the value in the target cell is not “no”, this line begins the Else block.

Interior.ColorIndex = xlNone 'No color

This line sets the cell interior color to no color (i.e., the default color) if the value in the target cell is not “no”.

End If

It ends the If statement.

End Sub

This line ends the worksheet event procedure.

To make this code work, you need to type “No” inside the corresponding cell depending on employee unavailability. The cell will automatically turn red and if that employee is somehow available later, just replace the “No” with “Yes” and the cell will return to its colorless condition.

The code is written in such a way that, whether you type, “NO”, “No”, “nO” or “no” it will convert the letters into lowercase, recognize them, and color the cell.

Things to Remember

  • Determine the purpose of the schedule and what information needs to be included in it. This will help you decide on the structure and layout of the schedule.
  • Make sure to include all important details in the schedule, such as shift duration, work type, employee name, and date. This will ensure that the schedule is accurate and useful.


By following the steps outlined in this article, you can learn how to make a customized availability schedule in Excel that meets your needs, includes all necessary information, and helps you to stay organized, productive, and on track. For more tips and tricks on using Excel to manage your schedule, be sure to visit Exceldemy for expert guidance and support. If you have any questions or comments, please feel free to leave them in the comment section below.

Md. Nafis Soumik

Md. Nafis Soumik

I am Md. Nafis Soumik. I am a Naval Architecture & Marine Engineering (NAME) graduate from Bangladesh University of Engineering & Technology (BUET). My hobby is to listen and create music along with backpacking. My career goal is to pursue higher education eventually. I always attempt to learn from many sources and try to come up with creative answers.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF