An occupancy, ADR and RevPAR spreadsheet: a daily sheet with its formulas
This guide builds a daily hotel occupancy spreadsheet column by column. It provides Excel formulas for occupancy, ADR and RevPAR, and shows how to total months correctly.
Contents
Key takeaways
- One row per night on the business date, with rooms available, rooms sold and room revenue as the only inputs; every ratio is a formula on those three.
- Occupancy rate is rooms sold divided by rooms available, ADR is room revenue divided by rooms sold, and RevPAR is room revenue divided by rooms available.
- Monthly and yearly figures add the numerators and denominators first and divide last; averaging daily percentages gives a number no guest ever paid.
- Complimentary and house-use rooms belong in rooms sold but not in revenue, so they lower ADR and need a column of their own.
- Three checks catch most typing errors: the rooms reconcile, ADR times occupancy equals RevPAR, and the month's room revenue matches the night audit's room line.
A hotel occupancy spreadsheet holds one row per night with rooms available, rooms sold and room revenue. Occupancy rate is rooms sold divided by rooms available; ADR is room revenue divided by rooms sold; RevPAR is room revenue divided by rooms available, which also equals ADR multiplied by occupancy rate.
Three people in the same hotel can quote three different occupancy figures for the same night, and all three can be honestly wrong. The arithmetic is rarely the problem; the problem is which rooms went into the denominator. This guide builds the daily sheet column by column, gives the formulas in both English and Turkish Excel, shows how to total a month without averaging averages, and ends with the checks that catch mistakes before the month closes.
What does a daily hotel occupancy spreadsheet look like?
It is one row per night, with the inputs on the left and the three ratios on the right, and nothing typed twice. The sheet earns trust not through cleverness but through repetition: the same columns, filled the same way, every night, with no gaps.
The columns, in order
The date column holds the business date, the day the night audit closes, not the clock date at midnight. A guest who checks in at 01:00 belongs to the previous business day, and putting them on the wrong row shifts one night's rooms and revenue onto the next.
Rooms available is the count of sellable rooms that night, after out-of-order and closed rooms are removed. It is the denominator of two of the three ratios, so a mistake here poisons both occupancy and RevPAR. Rooms sold counts rooms occupied for the night, including complimentary and house-use rooms, and excluding day-use rooms.
Room revenue is the room charge only, net of VAT and excluding food, beverage, minibar and other extras, because ADR measures the room and nothing else. Guests, often written pax, is a separate column for headcount; it is useful for breakfast planning and per-guest figures but it enters none of the three ratios, as explained in what pax means and how guests, rooms and nights are counted.
| Column | What goes in it | Where the number comes from |
|---|---|---|
| Business date | The night audit's business day, one row per night, no gaps | Night audit |
| Rooms available | Sellable rooms that night, out-of-order removed | Front desk room status |
| Rooms out of order | Rooms not for sale, reason in the notes column | Housekeeping or maintenance |
| Rooms sold | Rooms occupied overnight, comps and house use included | Night audit |
| Complimentary and house use | Subset of rooms sold that earned no room revenue | Front desk log |
| Day use | Rooms sold by the day, kept outside the overnight count | Front desk log |
| Room revenue net | Room charge only, VAT and extras excluded | Night audit room line |
| Guests / pax | Headcount, not used in any ratio | Registration cards |
| Occupancy rate | Formula | Calculated |
| ADR | Formula | Calculated |
| RevPAR | Formula | Calculated |
Where each number comes from
Every input should trace to one source: the night audit, the reservations system or the front desk log. Write that source in a header cell so the next person knows where to check when a number looks wrong. A figure that cannot be traced to a document is an opinion.
Leave a column for notes. The night something unusual happened, a water outage, a late group arrival, a room given away, is exactly the night someone will question a year later, and a two-word note answers the question.
What are the Excel formulas for occupancy rate, ADR and RevPAR?
Occupancy rate is rooms sold divided by rooms available, ADR is room revenue divided by rooms sold, and RevPAR is room revenue divided by rooms available. These are the same definitions covered in RevPAR, ADR and occupancy rate: formulas and common mistakes; here they become cells.
Write each formula against the cells of the same row, then fill it down the column. Never type a ratio in by hand, because a typed number stops updating the night the inputs change, and the person who retypes it will eventually retype it wrong.
Guard every division against a zero denominator. A hotel that closes for a season, or a floor taken out for renovation, will have a night with no sellable rooms, and an unguarded formula shows a division error instead of a blank. Format occupancy as a percentage with one decimal, and ADR and RevPAR as currency with no decimals; that is display only, the stored value keeps its precision.
The same formula in English and Turkish Excel
Turkish Excel separates arguments with semicolons, uses Turkish function names and reads the decimal comma. The logic does not change, only the spelling.
| Ratio | English Excel | Turkish Excel |
|---|---|---|
| Occupancy rate | =IF(B2=0,"",C2/B2) | =EĞER(B2=0;"";C2/B2) |
| ADR | =IF(C2=0,"",D2/C2) | =EĞER(C2=0;"";D2/C2) |
| RevPAR | =IF(B2=0,"",D2/B2) | =EĞER(B2=0;"";D2/B2) |
| RevPAR as a check | =IF(B2=0,"",E2*F2) | =EĞER(B2=0;"";E2*F2) |
The row assumes column A is the business date, B rooms available, C rooms sold, D room revenue net, and row 2 is the first night; in the check formula, E is occupancy and F is ADR. RevPAR can also be written as ADR multiplied by occupancy rate; use that form as a check on the row, not as a second source of truth.
If your sheet has a month column, the same formulas work with SUMIFS for monthly totals. If it does not, add one; a month column with values like 2026-03 is the cheapest thing you will ever add to a spreadsheet.
How do I total a month without averaging averages?
Add the numerators, add the denominators, then divide once: a month's occupancy is total rooms sold divided by total rooms available, never the average of the daily percentages. Averaging daily ratios gives a quiet Tuesday the same weight as a full Saturday, which is not how the month earned its money.
Monthly ADR is total room revenue divided by total rooms sold. Monthly RevPAR is total room revenue divided by total rooms available. Use a SUM over the month's block, or SUMIFS against the month column if the sheet runs the whole year on one page.
Key pointAdd first, divide last
Monthly occupancy = SUM(rooms sold) / SUM(rooms available). Monthly ADR = SUM(room revenue) / SUM(rooms sold). Monthly RevPAR = SUM(room revenue) / SUM(rooms available). The same three formulas, pointed at the year's block, give the year-to-date figures.
Year to date follows the same rule from 1 January. Do not chain twelve monthly ratios together, because each month carried a different number of rooms available. Keep the daily ratios for reading the shape of the month and the summed ratios for reporting it; they answer different questions and should not be mixed.
The one place an average is safe is a count: average guests per night, average length of stay in nights, average rooms sold. Averages of counts are fine; averages of ratios are not.
How should closed rooms, complimentary rooms and day use be treated?
Decide the rule once, write it at the top of the sheet, and apply it every night, because the same room counted two ways produces two different ADRs. The rule has to survive a change of staff, so put it in a cell above the table in plain words, not in someone's memory.
Out-of-order rooms leave the denominator: a room under renovation is not for sale, and counting it as available punishes the hotel for a decision it made on purpose. Complimentary and house-use rooms enter rooms sold but add no revenue, so they lower ADR; keep them in their own column so you can report the figure both with and without them.
Day-use rooms are usually kept out of overnight occupancy and ADR; if you sell them, report them on their own line rather than folding them into the night. For groups, count rooms picked up, not rooms blocked; an unsold block is a hope, not occupancy. Children, infants and extra beds change the guest count, not the room count; keep the two apart or the pax column will quietly disagree with the front desk.
The rules to write at the top of the sheet
Checklist
- Out-of-order rooms are removed from rooms available and the reason is noted.
- Complimentary and house-use rooms count in rooms sold and not in room revenue.
- Day-use rooms are reported separately and excluded from overnight occupancy.
- Group rooms are counted when picked up, not when blocked.
- Guests are counted separately from rooms, with the child rule stated.
- Room revenue is net of VAT and excludes food, beverage and extras.
- The same rules apply to every month in the file, including last year's rows.
How do I compare this year with last year on the same sheet?
Keep last year's rows in the same columns under the same rules, and compare the three ratios rather than the raw counts. Save a closed copy of each year's file; a sheet that gets edited after the fact cannot be compared with anything, including itself.
Align by business date and by day of week, because a religious or public holiday that moves changes the shape of the month without the hotel changing at all. If last year's rules differed, for example comps counted another way or day use included, restate last year before you compare; an unexplained jump is usually a definition, not a result.
A RevPAR change splits into an occupancy part and an ADR part; a hotel that filled its rooms by cutting price shows the split immediately. Compare month to month and year to date, not night to night; a single night is noise, and a single night in a small hotel is mostly weather. Write one sentence under the comparison saying what changed and why; the sentence is the report, the columns are only the evidence.
NoteThe comparison is only as good as the rules behind it
If the rules changed between the two years, restate the older year first, then compare. A ratio compared across two definitions is worse than no comparison.
What checks catch mistakes before the month closes?
Three checks catch most errors: the rooms reconcile, the ratios agree with each other, and the revenue agrees with the night audit and the accountant. Run them in order, because each check is cheaper than the one after it.
Rooms check: rooms sold plus rooms empty plus rooms out of order equals total rooms. A row that fails this is a typing error, not a business event. Ratio check: ADR multiplied by occupancy rate equals RevPAR on the same row, within rounding; if it does not, one of the three cells is pointing at the wrong column.
Revenue check: the month's room revenue matches the room revenue line in the night audit trial balance, which is the document that closes the business day; see night audit reports explained: trial balance, manager's flash and exception lists. The accountant's figure will not match the sheet, because it includes VAT, food and beverage and other charges; compare the room line only, and say so in the file.
Keep a correction log beside the sheet: date, cell, old value, new value, reason. Never overwrite a number silently, because the silent overwrite is what makes a sheet untrustworthy. Reconcile the month's room revenue to the monthly income and expense sheet, covered in the monthly income and expense sheet a small hotel keeps, so the two documents tell one story to the owner.
The three checks, in order
Checklist
- Rooms sold + rooms empty + rooms out of order = total rooms, every row.
- ADR x occupancy rate = RevPAR, every row, within rounding.
- Month room revenue = night audit room revenue line.
- Month room revenue appears once, in the same amount, on the monthly income sheet.
- Every correction has a line in the correction log.
- Every unusual night has a note in the notes column.
When is a spreadsheet no longer enough?
When more than one person edits it, when rooms arrive from more than two channels, or when you need the number before the night audit runs. Two editors and one file means version conflicts and a figure nobody can defend in a meeting.
A sheet has no audit trail: a changed cell leaves no record of who changed it, when, or why. Retyping availability and rates from channels into the sheet adds errors that no formula can catch, because the formula trusts what it is given. The sheet cannot see the guest bill, so room revenue and total revenue drift apart as the hotel sells more than rooms.
The test is not the size of the hotel but the consequence of a wrong number: once a wrong cell changes a price or a decision, the sheet has done its job and should be replaced, a decision covered in Excel or a hotel program: when a small property outgrows the spreadsheet. Until then, keep it: a correct sheet beats a system nobody has configured.
What to do next
Tonight, add the three formula columns to the sheet you already keep and run the three checks over last month's rows. The ratios are simple arithmetic; what makes them worth reporting is the discipline of one rule per column, applied every night, and a correction log that lets anyone see what changed.
Frequently asked questions
Should occupancy rate be calculated on rooms or on beds?
This sheet is room-based: rooms sold divided by rooms available. Bed-based occupancy divides occupied beds by available beds and gives a different number, especially in family rooms and dorms. Choose one basis, name it in the sheet header, and never mix the two in one column; a reader comparing your figure with another source needs to know which basis each one uses.
Do complimentary rooms count in occupancy rate?
Yes. A complimentary room is occupied, so it belongs in rooms sold and in the numerator of occupancy. It earns no room revenue, so it lowers ADR. Keep comps and house use in their own column so you can report occupancy and ADR both with and without them, and state the rule above the table.
Why does my RevPAR not equal ADR multiplied by occupancy rate?
Three usual causes: rounding in the displayed cells, a different denominator between the two formulas, or day-use rooms counted in one place and not the other. Check that ADR and RevPAR divide by the same room revenue and that occupancy and RevPAR divide by the same rooms available. If the gap survives that, one formula is pointing at the wrong column.
How do I write these formulas in Turkish Excel?
Turkish Excel separates arguments with a semicolon instead of a comma, uses Turkish function names such as EĞER and TOPLA, and reads the decimal comma. The logic is identical: occupancy is rooms sold divided by rooms available, written =EĞER(B2=0;"";C2/B2). If a file is shared between machines with different regional settings, agree on one and keep it.
Can I use AVERAGE for the monthly ADR?
No. AVERAGE of daily ADRs treats a night with three rooms sold the same as a night with forty. Monthly ADR is total room revenue divided by total rooms sold, so add first and divide last. The same rule applies to occupancy and RevPAR, and to the year-to-date figures.