Excel for the front office: five moves worth knowing

Some of the most useful tools I have ever built for a front desk were spreadsheets. Not dashboards, not software, just clean sheets that answered a question the PMS could not answer fast enough. At the Waikiki Beach Marriott, the sheet that changed how the team upsold was exactly that. The PMS could tell me a room had been upgraded, but it could not give the desk the quick, motivating daily view we needed. Excel could. That gap, between what the big system stores and what a manager needs to see at a glance, is where spreadsheet skills earn their place.

You do not need to be an Excel power user to work the front office well. You need a small set of moves you can do reliably, the same way every time, without thinking hard. I have watched agents waste an hour rebuilding a report by hand that a pivot table would have produced in thirty seconds. So here are the five moves I think every front office person should own, and why each one matters at the desk.

Why Excel still matters when you have a PMS

The honest answer is that a PMS and a spreadsheet are built for different jobs. The PMS is a transaction system. It is very good at holding reservations, room status, folios, and guest profiles, and it is the single source of truth for all of it. What it is not built for is a manager's quick read: upsell revenue this week by agent, arrivals by room type for tomorrow, a simple tracker the team updates during the shift. Those live more naturally in a sheet.

So Excel is not competing with the PMS. It sits next to it. You export data out of the system and shape it into the view you actually need. That relationship, big system for the record and a light sheet for the daily read, shows up all over the connected stack I describe in the systems that run a modern hotel. Excel is the flexible glue that fills the gaps the enterprise system leaves.

The best report is not the fanciest one. It is the one your team will actually open on a busy shift and trust.

Move one: the pivot table

If you learn only one thing on this list, learn pivot tables. A pivot table takes a long, raw list and turns it into a summary answer in seconds. You export a month of upsells from the system, drop the list into a sheet, and a pivot table tells you revenue by agent, by room type, or by day without you doing a single manual calculation. When new data comes in, you refresh, and the summary updates itself.

Here is the move in plain terms so it is not abstract. Say your export has one row per upsell with columns for agent, room-type move, and added revenue. You select the data, insert a pivot table, drag agent into the rows box and added revenue into the values box, and set it to sum. In two clicks you have every agent's total upsell revenue for the month, sorted highest to lowest. Drag room-type move into the columns box and now you can see which agent closes which upgrade. That is the whole skill, and it replaces an afternoon of manual tallying.

1. Export the dataPull one row per upsell with agent, room-type move, and added revenue.
2. Insert the pivotSelect the range and insert a pivot table.
3. Set the rowsDrag agent into the rows box.
4. Set the valuesDrag added revenue into the values box and set it to sum.
5. Read the answerEvery agent's monthly upsell revenue, sorted highest to lowest, in two clicks.

The reason this matters at the desk is that raw exports are unreadable. Nobody can look at eight hundred rows and see the story. A pivot table is how you get from eight hundred rows to the four numbers a manager actually cares about. Once you are comfortable building one, most of the reports you used to assemble by hand disappear, because the pivot does the assembling for you every time.

Move two: lookups that join your data

The second move is the lookup, whether you use the older approach or the newer functions your version offers. A lookup pulls a value from one table into another by matching on a shared key. Say you have a list of room numbers and a separate list of room types. A lookup joins them so your tracker shows the type next to every room without you typing it in by hand.

In practice, lookups are how you stitch two exports together. The PMS gives you one report with part of what you need and another report with the rest. A lookup on a common field, a confirmation number or a room number, merges them into one usable sheet. Picture an arrivals export that has confirmation numbers and rates but no loyalty tier, and a second export that has confirmation numbers and tiers. A lookup keyed on the confirmation number drops the tier next to every arrival in one formula. It sounds small. It is the difference between copying data across by eye, which is slow and error-prone, and joining it correctly in one formula you can trust. The one rule that saves you: the key you match on has to be truly unique, or the lookup returns the first match it finds and quietly gives you the wrong row.

Move three: conditional formatting that surfaces problems

Numbers in a grid all look the same until you make the important ones stand out. Conditional formatting is the move that colors a cell automatically when it meets a rule: a balance over a threshold turns red, a completed task turns green, a room that is out of order gets flagged. You set the rule once, and from then on the sheet points at its own problems.

This is the move that turns a passive report into a working tool. A shift tracker that highlights every unresolved balance in red gets acted on. The same tracker in plain black text gets ignored, because nothing on it asks for attention. I use conditional formatting on nearly every operational sheet I build, because the goal is never just to store information. It is to make the thing that needs doing impossible to miss.

Move four: a clean tracker layout

The fourth move is not a formula at all. It is layout discipline, and it matters more than any function. A tracker the team will actually keep using has to be obvious: clear column headers, one row per thing, no merged cells that break your formulas, and a structure that makes the next entry easy to add. Most spreadsheets fail not because the math is wrong but because they are confusing to update, so people stop.

Here is what I hold to when I build a tracker:

• One row per record. One upsell, one guest, one task per row. Never cram two things into a line.
• Headers that explain themselves. If a new agent cannot read the column and know what goes in it, rename it.
• No decorative merged cells. They look tidy and they quietly break sorting, filtering, and formulas.
• Data entry at the top or clearly marked. Make the next empty row easy to find so updating takes seconds, not searching.
• Totals and summaries separated from raw entries. Keep the input area clean and let a pivot or a formula block do the summarizing.

That upsell sheet I built worked because it was easy to keep open and easy to update mid-shift, not because it was clever. Layout is what makes a tool survive contact with a real, busy desk. I walk through the full build of that kind of sheet in build an upsell tracker that works, and layout is most of what makes it stick.

Move five: getting clean data out of the PMS

Every move above assumes you have usable data to start with, and that is its own skill. PMS exports are rarely clean. They come with header rows in odd places, totals mixed into the data, merged fields, and formatting that fights you. Learning to take an export and quickly get it into a proper table, one clean header row and consistent columns, is the unglamorous move that makes everything else possible.

The habit is simple: before you analyze anything, get the data into a clean rectangle. Strip the decorative rows, split anything crammed into one column, make sure each column holds one kind of value, and confirm the totals were not accidentally pulled in as data. Do that first and your pivot tables and lookups just work. Skip it and every formula downstream inherits the mess. Ten minutes of cleaning at the start saves an hour of chasing wrong numbers later.

There is a trust dimension here too. The first time a manager catches a wrong number in your sheet, they stop believing all of it, and a report nobody believes is worthless no matter how much work went into it. Most wrong numbers do not come from bad formulas. They come from dirty data feeding good formulas: a total row that got counted twice, a text value where a number should be, a date the system exported as plain text so it will not sort. So I build one small habit into every sheet, which is to sanity-check the output against something I already know. If the pivot says last week's upsell revenue was a number that feels wrong, I trust the feeling and go find out why before I send it. Clean data and a quick gut check are what let a sheet earn the trust that makes people actually use it.

The five moves at a glance

• Pivot table. Turns a long export into a summary. Use it for revenue by agent, arrivals by room type, charges by day.
• Lookup. Joins two tables on a shared key. Use it to merge exports that each hold half of what you need.
• Conditional formatting. Colors cells by rule. Use it to make balances, overdue tasks, and out-of-order rooms impossible to miss.
• Clean layout. One row per record, self-explaining headers, no merged cells. Use it so the team keeps updating the sheet.
• Clean export. Get the raw data into one tidy rectangle first. Use it before every analysis so the formulas downstream can be trusted.

Protect the sheet from the people using it

Every operational sheet dies the same way. Someone types over a formula. Someone sorts one column without the others and scrambles every row apart from its data. Someone pastes a block of text where a number belongs and every downstream total breaks. None of that is carelessness. It is what happens when a busy team shares one file on a real shift. So the skill that comes after the five moves is building the sheet so a rushed agent cannot easily break it.

The moves I use are practical. Lock the formula cells and leave only the entry cells open, so nobody overwrites the math by accident. Put the data in a proper table so sorting keeps rows together instead of shredding them. Add validation on the columns that need it, a dropdown for status instead of free text, so the entries stay consistent enough for a pivot to read them. Keep one clean input area and let the summary live somewhere the team never types into. A sheet built this way survives contact with a busy night. A sheet that trusts everyone to be careful does not survive the first one.

The version problem, and naming that saves you

The other quiet killer of a good sheet is versions. You have seen the file names: the tracker, then the tracker final, then the tracker final that somebody actually meant. The moment two copies exist, half the team is updating the wrong one, and the numbers quietly diverge until nobody trusts either. So I keep one file that is the source of truth, in one place everyone reaches the same way, and I resist the urge to email copies around. If the sheet has to be shared, share the location, not a copy.

Dating is the other half of this. I date what needs dating inside the sheet, so a number always carries the period it belongs to. A pivot that just says upsell revenue, with no date range attached, is a number waiting to be misread by whoever opens it next week. The small discipline of labeling every summary with its window is what lets a sheet get handed between shifts and managers without someone acting on last month's figure. Clean structure keeps the math right; clear naming and dating keep people from trusting the right math about the wrong week.

When a sheet is the right tool, and when it is not

I love a good spreadsheet, but I also know its limits. A sheet is the right tool when the job is simple, local, and likely to change: a tracker you tweak weekly, a report only your property needs, a quick view you want today without waiting on anyone. It stops being the right tool when you need real automation, many users editing at once, a permanent audit trail, or tight integration with other systems. That is when purpose-built software earns its cost.

Put plainly, the choice sorts by the job, not by which tool feels more serious.

Match the tool to the job, start with the sheet because it is free to change, and upgrade only when the job outgrows it.

The mistake goes both ways. Some teams buy software for a job a sheet would have handled better and cheaper. Others cling to a fragile spreadsheet long after they have outgrown it. The honest answer is to match the tool to the job and to reassess as the job grows. I lay out how I make that call in upsell tools: software vs a simple sheet. The short version is to start with the sheet, because it is fast and free to change, and upgrade only when the sheet genuinely stops keeping up.

Five moves, and the judgment to use them

None of this requires an advanced course. Pivot tables, lookups, conditional formatting, clean layout, and clean exports will carry you through almost everything the front office throws at a spreadsheet. What turns these five moves into real value is knowing which question is worth answering in a sheet at all, and building the answer so plainly that the team keeps using it after you walk away.

That is the part I care about most. A brilliant spreadsheet nobody opens is worth nothing. A plain one the whole desk trusts and updates every shift changes how the operation runs. So learn the five moves, keep your sheets honest and obvious, and remember that the point was never the spreadsheet. The point is a team that can see what matters at a glance and act on it before the guest ever feels the gap. Build for that, and Excel becomes one of the quietest, most reliable tools you own.

Questions from the desk

Why does the front office still need Excel with a PMS?

A PMS is built for transactions, not for a manager's quick daily read. Excel fills the gap: upsell tracking, a clean shift view, or any report the PMS pulls awkwardly. The best tool is the one your team will actually keep open.

What Excel skills matter most at the desk?

Five cover most of the work: pivot tables to summarize, lookups to join data, conditional formatting to make problems visible, a clean tracker layout, and getting clean data out of the PMS. Learn these and you replace a dozen manual reports.

What is a pivot table used for in a hotel?

It summarizes a long list into a quick answer: upsell revenue by agent, arrivals by room type, or charges by day. You drop raw exported data in and get a manager's view without rebuilding it by hand each time.

Should the desk build in Excel or buy software?

It depends on the job. A well-built sheet beats software for anything simple, local, and fast to change. Software earns its cost when you need automation, many users, or integration. Start with the sheet and upgrade only when it stops keeping up.

What is a lookup used for in a hotel spreadsheet?

A lookup pulls a value from one table into another by matching on a shared key, like a confirmation number or a room number. It is how you stitch two PMS exports into one usable sheet instead of copying data across by eye.

How do you keep a shared spreadsheet from breaking?

Lock the formula cells, put the data in a proper table so sorting keeps rows together, add dropdowns where entries must stay consistent, and keep one source-of-truth file instead of emailing copies. Build it so a rushed agent cannot easily break it.