Presenting a free Excel template with advanced features for hoteliers to check room availability and make bookings efficiently. While this Excel hotel room availability and booking template is free, it offers advanced functionality.
Here’s a quick overview of the features, with detailed instructions provided in the ‘How to Use the Template’ section below:
- You can view room availability and booking status for up to 100 days from any start date.
- The name of the booked person will be shown in each room in the chart.
- The template utilizes a Gantt Chart-like format to display booking statuses.
- You have the option to view confirmed and tentative bookings separately or all bookings at once.
- While you can enter all customer details, essential inputs include selecting rooms, status (tentative or confirmed), and check-in and check-out dates.
- To prevent accidental double bookings, the template includes highlighting invalid entries.
I also have a similar Google Sheets template that has been widely downloaded: Reservation and Booking Status Calendar Template in Google Sheets. After receiving feedback from hundreds of users, I’ve made several changes to that template. Drawing inspiration from it, I’ve created this free Excel template with some enhancements.
Please note that the free Hotel Room Availability and Booking Template is designed to function with Excel for Microsoft 365, as it leverages Lambda functions for advanced functionality.
Template Customization by Entering Room Numbers
Download the template by clicking the button below.
Open the file in Excel for Microsoft 365.
In the “Status” tab, input all your room numbers in cells A4:A104 (for 101 rooms), room types (king, queen, single, etc.) with their respective price brackets in cells B4:B104, and indicate whether smoking is allowed or not in cells C4:C104. You will see some sample entries; feel free to remove them from cells A4:C104.
Entering Booking Details and Understanding Booking Duration
In the “Booking” tab, all titles starting with “ƒ(x)” contain formulas. Do not delete the content within those columns in the table.
- Select a room from the drop-down menu in cell A3 (the drop-down list is populated from Status!$A$4:$A$104).
- Enter the name of the booking person in cell C3.
- Input the check-in date in cell D3.
- Input the check-out date in cell E3.
- Select the booking status in cell G3.
These entries are essential; all others are optional.
Please note: If the check-in date is 03/04/2024 and the check-out date is 04/04/2024, it indicates a one-night booking. Do not enter the same date in the check-in and check-out fields, as this will result in 0 nights. Since the check-out date is 04/04/2024, it will be available for other bookings.
Please refer closely to the screenshots from the “Booking” and “Status” tabs for the above entry.
Booking Tab:
Status Tab:
In the “Booking” tab, we utilize a table. When adding another booking, begin by entering the check-in date. This action will replicate the table formatting, automatically providing the drop-down menu in column A for selection.
How to Use the Excel Hotel Room Availability and Booking Template
The purpose of the free Excel hotel room availability and booking template is to help you manage your bookings accurately.
Before making each booking in the “Booking” tab, navigate to the “Status” tab to check if the room is available for the requested dates.
To do this, enter the start date in cell E1 on the “Status” tab. This will display the availability status for 100 days from the entered date.
Ensure that the selected option in cell J1 is set to “All”, indicating all types of bookings, whether tentative or confirmed.
Even after checking room availability, errors may occur when entering check-in and check-out dates. To prevent this, I’ve added a highlighting rule that will mark bookings in red to alert you.
Sometimes, the highlighting may apply to more than one row. In such cases, correct the entry for the current row (the latest entry) only.
The Formulas Used in the Hotel Room Availability and Booking Template
This section explains the cells containing formulas and the range where the highlighting rule applies.
This will help you avoid accidentally deleting them and also provide guidance on how to recover them if accidentally deleted.
Status Tab
The “Status” tab contains the following dynamic array formula in cell D4.
To display the booking person’s name on the bar:
=LET(dt,D3:CY3,room,A4:A104,guest,Table1[Name],r_room,Table1[Room],
start,Table1[Check-In Date],end,Table1[Check-Out Date],status,Table1[Status],
DROP(REDUCE("",room,LAMBDA(a,v,VSTACK(a,MAP(dt,LAMBDA(c,IFERROR(FILTER(guest,IF(J1="All", 1, status=J1)*(r_room=v)*(c>=start)*(c<end)),"")))))),1))And there are three highlighting rules for the range $D$4:$CY$104 to return the bars depending on the value in cell J1:
=AND(D4<>"", $J$1="Confirmed")
=AND(D4<>"", $J$1="Tentative")
=AND(D4<>"", $J$1="All")The above are the key formulas in the “Status” tab of the Hotel Room Availability and Booking Template. Additionally, there are two more dynamic array formulas in cells D2 and D3 to plot the weekday names and header dates based on the start date entered in cell E1. Here are them:
D2: Return the Weekday Names
=IF(LEN(D3:CY3),TEXT(D3:CY3,"ddd"),"")D3: Return the Calendar Dates
=SEQUENCE(1, 100, E1)Booking Tab
As mentioned earlier, below each field label that starts with “ƒ(x)”, you can find structured formulas.
B3: Populate the Room Type Based on the Room Selected
=XLOOKUP([@Room],Status!$A$4:$A$104, Status!$B$4:$B$104)F3: Return the Booking Duration (Nights)
=[@[Check-Out Date]]-[@[Check-In Date]]N3: A Helper Formula to Find Double Bookings
=LET(dt,SEQUENCE([@[Check-Out Date]]-[@[Check-In Date]]+1,1,[@[Check-In Date]]), bkd, FILTER(Table1[[Check-In Date]:[Check-Out Date]],([Room]=[@Room])*(ROW([Room])<>ROW([@Room]))),IFERROR( SUM(MAP(CHOOSECOLS(bkd, 1), CHOOSECOLS(bkd, 2), LAMBDA(x,y, SUM((dt>x)*(dt<y))))),0)>0)Highlight Rule:
=$N3=TRUEThis highlights duplicates.
The data is arranged in a table in this tab. So, when you want to delete any row, please do not select the entire row by clicking the row number. Instead, select the row range in the table, right-click on any selected cell, and select Delete > Table Rows.
Conclusion
I hope you find our free Hotel Room Availability and Booking Excel template useful. It’s easy to use and contains some advanced formulas.
Please refer to the “©” tab in the template to understand the fair-use policy.
