Using AI with Excel for hotel reports: formulas, pivot tables and checking the result
AI tools write formulas fast but cannot see your data or judge accuracy. This guide explains how to describe hotel exports safely and the three checks to verify every number.
Contents
Key takeaways
- An AI tool writes the formula from the description you give it, so the column names, the row grain and the rule you state decide whether the formula is right.
- Describe the columns and the result you expect, and use invented rows: guest names, identity numbers and card data never go into a public tool.
- ADR is room revenue divided by rooms sold, and occupancy is rooms sold divided by rooms available; state those definitions or the tool will guess.
- An average of daily ADRs is not the ADR for the period, and a pivot that averages a rate column is wrong even when every cell looks reasonable.
- Three checks catch most errors: a ten-row sample with a known answer, one row recomputed by hand, and a total tied back to the PMS report.
AI for Excel means using an AI chat tool, or the AI features inside spreadsheet software, to write a formula, lay out a pivot table or explain an export. It is good at structure and wording. It cannot see your data, so three checks decide whether the number is right.
The morning meeting needs ADR by segment. The revenue manager has the night audit export open in a spreadsheet, one row per reservation, and a formula that keeps returning a number nobody trusts. An AI tool can write that formula in seconds. The question is what to tell it, and what to check afterwards. This guide covers both: how to describe hotel exports without pasting guest data, which tasks AI handles well, and the checks that decide whether a number reaches the general manager.
What can AI actually do with a hotel Excel report?
It writes formulas, proposes pivot layouts, explains error messages and drafts chart specifications from a plain description. It does not see your file unless you paste it, and it cannot judge whether the result is plausible.
There are two places where AI meets a spreadsheet. The first is the AI features built into the spreadsheet software itself, which can read the open file. The second is a separate AI chat tool, which sees only what you paste or describe. The second is the common case in hotels, and it is the one that raises the data question.
Some tasks work well. Turning a described layout into a formula, explaining what an error message means, proposing a pivot layout for a stated question, writing the steps to clean a column, and suggesting a chart type for a comparison. Others fail. Anything that needs the actual numbers to judge, such as asking whether an ADR looks right. Anything that depends on your hotel's own definitions. Anything where the column meanings are ambiguous.
The mechanism is simple. The tool predicts text, and a formula is text with a grammar, so it can produce one. A correct number needs the right input data and the right definition, and both of those are yours. Use the tool to remove typing, not to remove thinking. The formula is the cheap part; deciding what the number should mean is the work.
Keep the tool's answer as a draft. Paste it into one cell, look at what it references, and only then copy it down the column. A relative reference that drifts by one row is invisible in the first cell and obvious in the total.
Where AI helps and where it does not
| Task | What to give the tool | What you still check |
|---|---|---|
| A single figure, such as ADR for a date range | Column headers, the grain, the rule | The numerator and denominator columns |
| A pivot layout for a question | The question, the fields, the filters | Field placement; summed or averaged values |
| An error message | The message and the formula | That the fix did not change the meaning |
| Cleaning steps for a column | Two example values, one bad | The cleaned column still matches the source |
| A chart for a comparison | The question and the categories | The axis, the units, the time order |
The same method applies to any writing you do for these tools; the repeatable prompt method for hotel work covers it in more detail.
What do hotel exports contain, and why does the grain decide the formula?
Every export has a grain, the thing one row represents, and the grain decides which formulas are valid. Most wrong hotel numbers come from a formula that assumed a different grain than the file had.
A night audit or daily revenue report has one row per business date and is already aggregated. It is good for trends and cannot give segment detail, because the detail was summed away before the export. A reservations export has one row per reservation, with arrival, departure, nights, rate, segment and agency. Room nights must be computed as nights multiplied by rooms; counting rows counts bookings, not room nights.
An in-house or room-night export has one row per room per night. Here counting rows does give room nights directly, which is why the same formula can be right in one file and wrong in another. An agency statement has one row per booking, keyed by a confirmation number, sometimes with the agency's own reference beside it. It is a list to match against, not a revenue report.
Before asking for anything, look for common defects: dates stored as text, merged header cells, a totals row sitting inside the data, trailing spaces in agency names, and numbers stored as text with a leading apostrophe. Each one breaks a formula silently.
Key pointOne row, one thing
Before you ask for any formula, write down what a single row of the export represents: one reservation, one room night, one business date or one booking on a statement. Every formula you ask for afterwards depends on that sentence.
Say the grain out loud before you write the request. If you cannot say what one row is, the tool cannot know either, and it will guess silently.
How do you ask for a formula without pasting guest data?
Describe the columns by header and type, state the grain, give the rule in words and the result you expect, and use invented rows instead of real ones. The tool needs the shape of the data, not the people in it.
The four-part request has, unsurprisingly, four parts. The layout: columns A to H with their headers and what type each holds. The grain: one row per reservation. The rule in plain words: room revenue divided by rooms sold, counting only rows whose status is checked out or in house. The expected result on a two-row example you invent.
Replace names with placeholders. A guest name is personal data and the formula never needs it; the header "Guest" is enough. Never paste guest names, identity or passport numbers, card numbers or card references, contact details, or anything taken from a document a guest signed. Under KVKK (kişisel verilerin korunması kanunu) that is personal data, and a public tool is not a place for it.
Aggregate first. If the question can be answered from monthly totals, ask with totals; a table of twelve rows carries far less exposure than a table of four thousand reservations. Header names matter more than cell addresses: if your export says "Oda Geliri" in one column and "Room Revenue" in another, fix the headers before you ask, or the tool will pick the wrong one.
Finally, follow the hotel's own rule on what staff may share with AI tools. The hotel AI policy question is covered in a separate guide; if no such rule exists, writing one is the first task, not the last.
The four-part request
ChecklistBefore you press send
- Column headers listed in order with their types
- The grain stated in one sentence
- The rule written in plain words
- One invented example row with the result you expect
- No guest names, identity numbers, card data or contact details anywhere in the message
- The date range and filters named
- The hotel's AI policy checked
Worked examples: ADR by segment, pickup against last week, agency statement matching
Three tasks cover most of the spreadsheet work in a hotel: a ratio per segment, a comparison between two snapshots, and a match between two lists. Each one has a different failure mode.
ADR by segment needs one row per reservation with segment, room revenue and room nights. ADR is room revenue divided by rooms sold, so the request must say which column is revenue and which column counts rooms. The check: total room revenue divided by total rooms sold across all segments must equal the overall ADR, and it will not equal the average of the segment ADRs. The formulas behind ADR, occupancy and RevPAR are worth having beside you while you write the request.
Pickup compares the rooms on the books for a future date now against the same future date a week ago. It needs two exports, each saved with the date it was taken. The tool can write the comparison formula; it cannot reconstruct last week's file, so the earlier snapshot has to exist before you ask. Both snapshots must come from the same report with the same filters. A snapshot taken with a different segment filter will show a pickup that is really a filter change. The day-by-day reading of a pickup report explains the interpretation once the numbers are right.
Agency statement matching joins two lists on confirmation number. The failures are text against number, leading zeros dropped, trailing spaces, and duplicate keys where one booking appears twice. Ask for a formula that flags "not found" explicitly rather than one that returns a blank, because a blank hides the problem. The check: matched rows plus unmatched rows must equal the number of rows in the statement. If the counts do not add up, the join is wrong, not the statement.
| Task | Columns needed | What the formula does | The check |
|---|---|---|---|
| ADR by segment | Segment, room revenue, room nights | Sums revenue and rooms per segment, divides | Overall ADR recomputed from the two grand totals must match |
| Pickup | Booking date, arrival date, rooms, snapshot date | Subtracts last week's rooms from this week's, per arrival date | Both snapshots from the same report and filters |
| Statement matching | Confirmation number on both lists, amount | Joins on the key, flags not found | Matched plus unmatched equals statement row count |
For all three, ask for the formula in one cell first, test it on the sample, and only then fill it down.
Can AI build the pivot table and the chart, and what should you check?
It can propose the layout and the steps; the risk sits in field placement and in the aggregation, not in the clicking. A pivot table will happily compute a number that means nothing.
Specify rows, columns, values and filters explicitly. "By segment" is not a specification; say which field goes on rows, which on columns, which is summed and which filters the report. Sum, count and average answer different questions. Room nights are a sum of a nights column, reservations are a count of rows, and ADR is a ratio of two sums. Ask for the ratio, not for an average of a rate column.
Sum, count or average
Blank rows and a totals row inside the source data inflate counts and double the grand total. Clean the range before the pivot, and refresh after replacing the data. Check that the source range covers every row: a pivot built on the first thousand rows of a four thousand row export is a plausible, wrong answer.
On charts, a line suits a time series, a bar suits a comparison between categories, and a pie chart suits nothing with more than a handful of slices. State the units on the axis; a chart without units is decoration.
Watch outAn average of averages is not the average
If a pivot shows ADR as an average of a rate column, the number is wrong even when every cell looks reasonable. Build it as total room revenue divided by total rooms sold for the same period, and compare that figure with the PMS report.
If you would rather ask plain-language questions of your own hotel data instead of building pivots, that is a different capability, covered in the guide to AI hotel reporting.
What are the three checks before a number reaches the GM?
Test on a small sample with a known answer, recompute one row by hand, and tie one total back to the PMS report. Each check catches a different class of error, and together they take minutes.
Sample test. Pick ten rows, work out the expected result yourself, and compare. Ten rows is enough to catch a wrong column, an off-by-one range or a filter that excluded the rows you meant to include.
One row by hand. Take a single reservation, add its room revenue and its nights with a calculator, and compare with what the formula produced for that row. This catches the errors that survive a total because they cancel out.
Total tie-out. Compare the spreadsheet total with the same figure in the PMS report for the same date range. If they differ, find the reason before explaining it away: cancelled and no-show rows, currency, out-of-order rooms, or a business date that runs past midnight.
Check the denominator. Occupancy uses rooms available, and rooms out of order change that number. A correct numerator over a wrong denominator is still a wrong occupancy. Check the date boundaries too: an export filtered on arrival date is not the same as one filtered on stay date, and the difference shows up only in the months where stays cross a month end.
Write the check down: file name, date range, who checked, and what the total was. Next month someone will ask why the number changed, and the note is the answer.
ChecklistBefore sharing
- Ten-row sample matches a hand-worked answer
- One row recomputed with a calculator matches the formula
- The spreadsheet total equals the PMS report total for the same range, or the difference is explained
- Rooms available excludes out-of-order rooms
- The export filter is on the same date field as the question
- The check is written into the file or the covering note
When should a recurring spreadsheet become a report in the system?
When the same export is rebuilt and rechecked every week, the report should be produced once, with a fixed definition, by the system that holds the data.
The signals are easy to spot. The file is saved with the date in its name. Someone keeps a personal copy that others ask for. The checking takes longer than the reading. Two people produce different numbers for the same question. Any one of these means the spreadsheet has become infrastructure.
A built-in report fixes the definition. ADR means the same thing every month, nobody retypes a formula, and a new starter gets the same number as the person who left. Keep the spreadsheet for what it is good at: one-off questions, comparisons across two sources, and the working file behind a decision that has not been made yet.
The honest trade: a built-in report answers the questions someone anticipated, while a spreadsheet answers the one you did not anticipate. Most hotels need both, and the mistake is using the spreadsheet for the first kind. If the export stays, keep one template with the formulas locked and a short change log at the top, so the next person can see what was changed and when. For a sense of what fixed, built-in reporting looks like, see the hotel operating system and its built-in reports.
NoteA report is a definition that survives the person who wrote it
If the definition lives only in one analyst's file, the hotel does not have a report; it has a habit.
What to do next
Take one report you rebuild this week, write down its grain and its definition in a single sentence, and run the three checks once, on paper if necessary. If the sentence and the checks hold, the spreadsheet is fine and you now have a template. If the same rebuild keeps returning, that sentence is the specification for a report the system should produce, and the spreadsheet goes back to being a working file.
Frequently asked questions
Is it safe to paste a PMS export into an AI chat tool?
Usually not as it comes out of the system. A reservations export carries guest names, identity or passport numbers and sometimes card references, which is personal data under KVKK (kişisel verilerin korunması kanunu). Strip or aggregate first, or describe the columns and use invented rows. The hotel's own AI policy decides, and if there is no policy, that is the first thing to write.
Can AI calculate ADR and occupancy correctly?
It can write a correct formula if you give it the definitions: ADR is room revenue divided by rooms sold, and occupancy is rooms sold divided by rooms available. It cannot know which of your columns is which, and it cannot tell whether the result is plausible. The formula is the tool's job; the definition and the plausibility check are yours.
Why does my AI-written formula give a different total from the PMS report?
Look at four things in order: the grain (rows counted as room nights when they are reservations), the date field used in the filter, the rows the export left out (cancelled, no-show, out of order), and currency or rounding. Find the reason before accepting it. A difference that is explained is a finding; a difference that is waved away is an error waiting to be repeated.
Does this replace the night audit?
No. The night audit closes the business day and produces the reference numbers; the spreadsheet sits above it and reshapes them. AI for Excel helps with the reshaping, the formula and the layout. It has no view of the business day and cannot close it.
Should I ask for a formula or a pivot table?
A formula for a single figure you will need again, a pivot table for exploring a question whose answer you do not yet know. Both need the same three checks, and both fail in the same way when the grain or the definition is wrong. Start with the pivot to see the shape, then fix the figure as a formula once you know what you are measuring.