Excel for hotel revenue managers on Mac: RevPAR, pickup and rates in Microsoft 365

What hotel revenue managers track in Excel on Mac

Many revenue managers keep a workbook next to their property management system and revenue management system. The research I found does not measure how many do this. Treat it as a common setup, not a fact about the whole industry. The sheet is where you test a rate idea before you change it.

The core sheet tracks the same few measures every day:

• Rooms sold and rooms available, from the night audit or your PMS report.
• Room revenue without taxes and fees, so ADR stays clean.
• Occupancy, which is rooms sold divided by rooms available.
• ADR, which is room revenue divided by rooms sold.
• RevPAR, which is room revenue divided by rooms available.
• Competitor rates from your rate shop, with the date you checked them.
• A short note on what changed and why.

Add the forward view once the daily numbers are clean. Pickup shows the rooms booked for future dates, which shows where demand is heading. The next sections cover both.

Set up the workbook so formulas keep working

Start with one row per day. Select the range and press ⌘T to turn it into a Table. A Table grows when you type under it. Formulas that use column names keep working when you add a column. Plain ranges do not do that.

Microsoft's October 2026 What's New in Excel post lists name tables at creation for Windows and Mac. Use the name field in the Create Table dialog. Call the table Daily_Rates or something similar. A clear name makes formulas readable a year later.

Keep a second sheet called Setup. Put the hotel name, room types, base rates and date format there. Point your formulas at that sheet instead of typing values into them. When a number changes, you edit it once.

RevPAR, ADR and occupancy formulas in Excel

ADR is room revenue divided by rooms sold. Occupancy is rooms sold divided by rooms available. RevPAR is room revenue divided by rooms available. Each one is a ratio, so each one can divide by zero.

RevPAR also equals ADR multiplied by occupancy. Use that as a check. If the two routes give different answers, one column points at the wrong range or the wrong row.

In a Table, the ADR formula reads like this: if rooms sold is zero, show a blank, otherwise divide room revenue by rooms sold. Written with structured references, it is =IF([@RoomsSold]=0,"",[@RoomRevenue]/[@RoomsSold]). Copy the pattern for RevPAR and occupancy, and change only the denominator.

Returning a blank instead of zero matters. A zero ADR on a closed night drags your average down. A blank stays out of the math, and AVERAGE skips it.

Mistakes that break the rate numbers

• Including taxes and fees in room revenue, which inflates ADR.
• Mixing stay dates and booking dates in one column.
• Typing a rate as text, so SUM and AVERAGE skip it without a warning.
• Copying a formula into blank rows and getting a divide-by-zero error down the whole sheet.
• Building a pivot on a fixed range, which misses every new row.
• Comparing a room count from one report with a count from another that handles rooms out of order differently.

Fix the source before you fix the formula. Most rate errors come from the data, not from the math.

Pickup reports: on the books versus last year and forecast

Pickup is the number of rooms booked since your last snapshot for a future date. Take the snapshot on the same weekday and time each week. Compare the rooms on the books for a date against the same date last year and against your forecast.

The free Roommaster template is a useful model. Its weekly summary compares each segment to this period, last year, forecast and variance. The segments it lists are Direct, OTA, Corporate and Group. Its setup section holds base rates by room type.

Its download buttons lead to a Book a Demo page in the research, and that page does not state Mac or Excel compatibility. Treat the template as a layout to copy. Test each formula on a week of sample data before you trust any total.

Build the pickup table as a Table with one row per segment. Give it columns for on the books, last year, forecast and variance. Write your variance sign rule in the Setup sheet. Forecast minus actual and actual minus forecast tell opposite stories, so pick one and keep it.

Segment mix with a PivotTable on Mac

Segment mix answers a simple question. Which channel brought the rooms? Select any cell in the Daily_Rates Table, then go to Insert > PivotTable. Use the Table name as the source, so the pivot can grow with the data.

Put segment in Rows and the date in Filters. Put rooms sold and room revenue in Values. Set rooms sold to Sum, not Count. Count counts rows, so it gives the wrong total when a row shows zero rooms.

The step-by-step version is in how to make a pivot table in Excel on Mac. Read it once, then build your own. The first build takes longer than the rest.

Keep the source from going stale

A pivot built on a fixed range misses new rows. A pivot built on a Table name grows with the data. If yours still shows old totals, read why a pivot does not refresh new rows. Then choose Data > Refresh All after every paste.

Where Lazur fits in the rate workflow

Lazur is a voice assistant for the Mac. Hold ⌥⌘ and ask about anything on your screen. Lazur looks at the screen only while you hold the keys, and it answers out loud. Ask where a button is, and Lazur draws a circle and a label around it.

Ask how to do something, and Lazur guides you one click at a time. It waits for each click. It does not click for you, and it does not edit your file.

Lazur fits best where you know the number you need but not the menu. A new analyst can ask where the PivotTable button is. You can ask why a cell shows an error while that cell is still on screen.

Errors that stop a rate review

The most common error in a rate sheet is #DIV/0, the divide-by-zero error. It means you divided by zero. The usual cause is rooms sold on a closed night, or a blank row copied down by mistake. Wrap the formula in IF, as shown earlier, so blank rows stay blank.

#N/A is a different problem. It usually means a lookup found no match. A room type name with a trailing space is a common cause. The guide to fixing VLOOKUP #N/A errors walks through the checks in order.

Ask Lazur to explain the error. Hold ⌥⌘, ask what the error means, and Lazur answers out loud while the cell is visible. You type the fix and read the result yourself.

Rate decision log and dictated notes

A rate log records what you changed, when, and why. Keep it on its own sheet. Use columns for date, room type, old rate, new rate, reason and result. Future you will need the reason more than the number.

Dictation speeds up notes. Hold fn and speak. Lazur types clean, formatted text at the cursor in any app. Save phrases you repeat as snippets. Add hotel names and abbreviations to your personal dictionary so they come out spelled the way you want.

Dictate short notes straight into a cell. Longer reasons read better in a cell with wrap on. Select the cell and use Home > Wrap Text on the ribbon.

Excel for Mac versions and sheet limits to check

Microsoft publishes release notes for Office for Mac. The latest build in the notes is 16.114.3, dated October 27, 2026. Releases came about weekly in 2026, starting with 16.105 on January 13, 2026. Check your build under Excel > About Excel.

Microsoft says macOS 14 Sonoma is required to receive updates for Microsoft 365 and Office apps. It says macOS 12 Monterey is required to install versions 16.78 or later. If your Mac is older, check your version before a big update.

A worksheet holds 1,048,576 rows and 16,384 columns. A cell holds 32,767 characters, and a formula holds 8,192. A daily rate sheet will not reach the row limit. A raw booking export can, so split large exports by month.

Microsoft's specifications page also lists Data Model memory limits. It describes 32-bit and 64-bit builds differently. Check which build you run before you build a large model on top of your rate data.

Templates to start from

Start from a template if you do not want to build the sheet from zero. Roommaster offers a free hotel revenue management template in PDF, Word and Excel. It has three parts: setup details, a daily rate and performance tracker, and a weekly pickup and segment summary.

The daily tracker covers rooms sold, occupancy, ADR, RevPAR, competitor rate and notes. The weekly summary covers Direct, OTA, Corporate and Group mix. Those columns match the measures in this guide, so you can compare them side by side.

Test any template on a week of real data before you trust it. Check that each total matches your PMS report. A template that looks right can still point at the wrong column.

What the time-saving claims actually show

Vendors make big claims about spreadsheets. Revenue Hub says hotel teams can spend up to 125 hours per month cleaning, formatting and moving data. It names no study, publisher or date. Treat that figure as a sales claim, not a measurement.

A LodgIQ contributed article says automated systems save an estimated 20 to 40 hours per month. The same article says over 80% of spreadsheets used in hotel operations contain errors. It cites only Research shows, with no study named.

The article also gives setup costs of $7,000 to $10,000 and ongoing fees of $6 to $10 per room per month. It cites no source for those figures. Ask vendors for written quotes before you compare them with your own sheet.

Use the claims as questions. How much time does your team spend on copy and paste each week? Time it for one week and write the answer down. That figure belongs to you, and it is the one that matters in a budget talk.

Edge cases: stay dates, booking dates and several hotels

Stay date and booking date are not the same thing. Pickup needs the booking date. ADR needs the stay date. Mix them in one column, and the sheet still calculates. The numbers just stop meaning anything.

A group of hotels should give each property its own Table. Stack the rows in one master Table with a property column. Keep currency in one column per row, and write the conversion rate in the Setup sheet.

Snapshot time matters too. A snapshot taken at a different local time changes the pickup number. Take it at the same local time every week, and write that time in the sheet header.

A weekly routine for pickup and rate review

1. Take the pickup snapshot on the same weekday and time.
2. Paste the new rows directly under the last row of the Daily_Rates Table.
3. Check that closed nights show blanks, not errors.
4. Refresh the segment PivotTable with Data > Refresh All.
5. Write one line in the rate log for each change you made.
6. Send the GM a short summary with the pickup table and one sentence on what moved.

Using Lazur for competitor checks and reminders

Ask Lazur to run research on competitor rates for a date you pick. Research runs in Lazur's own Chrome tabs, and it comes back with a summary and a report. Check those numbers against the source sites before you change a rate.

Ask Lazur to set a reminder for the next snapshot. Reminders go to Apple Reminders. A missed snapshot breaks the pickup trend, so a weekly reminder is worth setting.

Next steps for your first week

Build the Daily_Rates Table this week. Add the pickup summary next. Add the segment PivotTable once you have a full week of clean data. Keep the rate log running from the first day, even if the sheet is still rough.

If your reports overlap with finance, the financial analyst Excel guide covers budget and forecast cleanup. For a general Excel helper on the Mac, see Lazur for Excel. To try Lazur on your own screen, download the app. Plans and limits are on the pricing page.