How to Build a Travel Agency Pivot Table for Booking and Revenue Analysis

Most travel agency owners think they need a separate report for every business question — one for monthly revenue, one for booking volume by destination, one for commission tracking, one for cancellation rates. In practice, a single well-structured pivot table built from your raw booking log answers all of those questions and more, without duplicating data or rebuilding reports from scratch each week.

The misconception is that pivot tables are a reporting tool. They are a filtering and aggregation engine. Once your source data is structured correctly, one pivot table becomes a dashboard you can reconfigure in seconds — swap a row field, move a column field, and the entire view answers a different question. The setup work up front is modest. The payoff is that you stop making reports and start exploring data.

Step 1: Structure Your Source Data Before Anything Else

Before a single pivot table can be created, your booking log needs to follow one non-negotiable rule: every booking gets its own row, and every attribute of that booking gets its own column. No merged cells. No subtotal rows mixed into the data. No multiple bookings crammed into one cell.

A practical source layout looks like this:

Two data-entry mistakes wreck pivot tables more than any other. First, leaving blank cells in the Destination or Booking Date columns — a blank destination creates a “(blank)” group you then have to chase down. Second, storing dates as text like “March 14” instead of a real Excel date — that breaks chronological sorting and makes month-by-month grouping impossible. Fix both before creating the pivot table.

Step 2: Create the Pivot Table and Lay Out the Base Report

Select any cell inside your booking log, go to the Insert tab, and click PivotTable. Excel will suggest the full table range — verify it covers every row and column, then choose whether to place the pivot table on a new worksheet or alongside your data. A new worksheet is cleaner for this use case.

The base layout for a travel agency dashboard starts with three fields:

• Rows: Destination
• Columns: Booking Status (Confirmed, Cancelled, Pending)
• Values: Count of Booking ID

That single view answers the first question every agency asks: how many bookings do we have per destination, and how many of them fell through? Add Total Revenue to the Values area a second time, set it to Sum instead of Count, and you see revenue per destination side by side with booking counts.

From this starting point, nearly every other business question is a rearrangement of the same three field wells. That is the core skill — not building reports, but knowing which field to drag where.

Step 3: Analyze Revenue by Month and by Destination

The most common revenue question is straightforward: how much did we bring in each month, and which destinations drove that number?

Drag Booking Date into the Rows area, above Destination. Right-click any date in the pivot table, choose Group, and select Months (and Years if your data spans multiple years — without the Years checkbox, January of 2025 and January of 2026 collapse into the same row). Now your pivot table shows revenue per month per destination.

The critical setting here is the Base Field behavior. If you grouped by both Year and Month, Excel nests Year above Month. That is correct for most reporting. But if you want an uninterrupted chronological sequence across years — every month from January 2025 through December 2026 in one continuous list — uncheck Years and group by Months alone, ensuring your underlying date column includes the full year. A date stored as “2026-03-14” distinguishes March 2026 from March 2025. A date stored as “March 14” does not, and your monthly totals will silently combine different years.

For a year-over-year comparison, put Years in the Columns area and Months in the Rows area. Each month becomes a row, each year a column, and the intersection shows that month’s revenue for that year. This layout surfaces seasonal patterns immediately — you will see your high season spike in the same months across both years.

Step 4: Track Commission and Profitability Per Booking Channel

Revenue is what you collect. Commission is what you keep. Most agencies earn a percentage of each booking — typically 10 to 15 percent for flights, 12 to 18 percent for packages — but the percentage varies by supplier and channel. Online bookings through your website often carry a lower commission than phone bookings where an agent negotiated a custom package.

To analyze this, drag Booking Channel into the Rows area, replacing Destination. Add Commission Earned to the Values area as a Sum. Add Total Revenue to the Values area as a Sum as well. Now each channel shows both metrics side by side, and you can compute the effective commission rate by dividing one by the other.

Add Cost of Goods to the Values area. Your profit per channel is Total Revenue minus Cost of Goods minus any credit card processing fees — and if those fees are not in your source data yet, add a column for them now, before you build a habit of using this report. A pivot table cannot calculate what the data does not contain.

If you want the commission rate itself displayed as a percentage, right-click the Commission Earned field, choose Show Values As, then % of Grand Total. That answers a different question — what share of total commission came from each channel — which is valuable for negotiating supplier contracts.

Step 5: Look at Lead Time — Days Between Booking and Travel

A metric few agencies track but many should: lead time, the gap between when a customer books and when they travel. Short lead times (under 14 days) often mean last-minute bookings with higher margins but less planning certainty. Long lead times (60+ days) indicate organized travelers who may book add-ons.

To compute this in a pivot table, add a helper column to your source data first. Label it “Lead Time Days” and enter a formula in the first data row: =DATEDIF([@[Booking Date]], [@[Travel Date]], “d”). Copy that formula down the entire column. Every row now shows its own lead time in days.

Refresh your pivot table, then drag Lead Time Days into the Rows area. Right-click any value, choose Group, and set the grouping to start at 0, end at 120, and step by 15. You now see booking volume distributed across lead-time buckets. The pattern is measurable: if 40 percent of your bookings land in the 0–15 day bucket, your marketing is pulling in last-minute demand. If the 61–90 day bucket dominates, you have a predictable pipeline worth forecasting against.

Step 6: Use Filters to Answer Conditional Questions Without Building New Reports

The pivot table’s filter area — called Report Filters — turns a single report into dozens. Drag Destination Country into the Filters area. Now a dropdown at the top of the pivot table lets you show Japan-only data, or all countries except Japan, or any single country, without touching the row or column layout.

Combine two filters for sharper questions. Put Booking Status in the Filters area alongside Destination Country, then set Status to “Confirmed” and Country to “Japan.” The pivot table now answers: what does our confirmed Japanese booking revenue look like by month and channel? The report underneath never changes structure — you are only narrowing the population of rows it summarizes.

This is where the pivot table earns its keep. An Excel table with 5,000 booking rows can be filtered manually, but that takes time and invites mistakes. A pivot table with two report filters answers every two-variable question in under ten seconds.

Step 7: Handle Cancellations Without Losing Revenue Visibility

Cancellations are a fact of travel agency life. The industry average hovers around 8 to 12 percent of bookings, depending on destination and lead time. A pivot table that only shows confirmed bookings hides the scale of the problem — and the destinations where cancellations cluster.

Build a dedicated view: Destination in the Rows area, Booking Status in the Columns area, Count of Booking ID in the Values area. Sort the pivot table by the Cancelled column in descending order to see which destinations lose the most bookings. Destinations with cancellation rates above 15 percent deserve a conversation with your supplier — you may be over-promising availability, or the destination may have a chronic instability problem.

For the cancellation rate as a percentage, right-click the Count of Booking ID field, choose Show Values As, then % of Row Total. Each destination’s row now shows what percentage of its bookings were confirmed, cancelled, or pending. A destination with 20 percent cancellations and a 60-day average lead time has a different problem than one with 20 percent cancellations and a 5-day lead time — the first is a planning issue, the second is an operational one.

Step 8: Refresh and Rebuild When Your Data Grows

A pivot table does not update itself when you add new rows to the source data. Excel only refreshes what it already knows about unless you extend the source range.

If your booking log lives in an Excel Table (Insert → Table, or Ctrl+T), new rows are included automatically when you refresh. If your data sits in a plain range, you must update the pivot table’s source range manually every time — and forgetting to do that produces reports missing your newest bookings. Use an Excel Table from the start. It converts the range to a named structure that expands as you add rows, and your pivot table picks up the new data on every refresh.

Right-click the pivot table and choose Refresh, or press Alt+F5. Set the recommendation to refresh on file open (File → Options → Data → make data refreshable when opening a file) if you share the workbook with colleagues. A stale pivot table is worse than no pivot table — it makes confident decisions from incomplete data.

A Quick Reference for Common Travel Agency Pivot Questions

Most agencies never get past the first two questions on that table. The pivot table structure handles all five — and a dozen more variations — from the same source data. The limiting factor is not Excel. It is deciding which questions matter enough to look at regularly.

Start today by adding the Cost of Goods and Lead Time Days columns to your booking log, even if you don’t use them immediately. Future-you will be able to answer profit and lead-time questions without rebuilding a single report.