What is a room block tracking sheet?
A room block tracking sheet sets the rooms you contracted with the hotel, by room type and night, against the rooms your reservations have filled, and shows what is left. Rows are room types, columns are nights. You see at a glance which type has free rooms on which night, or where the block is exceeded.
For the nightly total on its own, use the room night calculator; this page splits the same count by room type.
How to set the sheet up
- A reservation list. One row per room: room type, check-in, check-out. Guests sharing a room are one row.
- Night columns. Type the first night’s date and add one day in each cell to the right. The check-out day is not a night.
- Room type rows. One row for each room type in your contract: Single, Double, Twin, Suite…
- Two blocks. One holds the contracted block (typed by you), the other the rooms filled (calculated from the list). The difference is the rooms left in the block.
The Excel formulas
With reservations on a sheet called List, room type in E, check-in in F and check-out in G, the room type in A4 and the night’s date in B3, the rooms filled for that type that night:
=COUNTIFS(List!$E:$E, $A4, List!$F:$F, "<="&B$3, List!$G:$G, ">"&B$3)With the contracted block in B17, the rooms left in the block:
=B17-B4If each row has a number-of-rooms column, use SUMIFS instead of COUNTIFS. The formula counts the check-in day and not the check-out day.
The ready-made template
The Room nights sheet of the free rooming list template has this built in: type the first night and the rooms filled per room type are calculated from the list for 21 nights. Type a block per room type below it and you get the rooms left; where the block is exceeded the cell says “over 2”, so it does not depend on colour alone.
The download button at the top of the page gives the same file in English or Turkish. No sign-up.
How to read it
- Positive means that many rooms of that type are still free in the block that night. Rooms that stay free across many nights are attrition risk; the attrition guide covers what that costs.
- Zero means the block is full for that type that night; a new booking needs extra rooms from the hotel.
- “Over” means you have gone past the block for that type. Check your cutoff date before you talk to the hotel: cutoff calculator.
Common mistakes
- Treating all types as one block. If the contract gives a block per room type, a total hides it: Doubles can be full while Twins sit empty.
- Leaving cancellations in the list. A cancelled room keeps showing as filled.
- Counting the check-out day as a night. 11–14 March is three nights.
- Never comparing with the hotel’s list. The sheet shows your list; check it against the hotel’s record with the comparison tool.