How to Build a Holiday Travel Itinerary in Excel (With Formulas, Color-Coding, and a Real Sample Plan)

Two days before a 10-day Italy trip, I had six browser tabs open: a flight confirmation, a hotel email, a Google Doc of “things to do,” a half-finished budget on my phone’s notes app, and two articles arguing about whether you need to reserve the Uffizi in advance (you do). Nothing talked to each other. I missed that a train I’d “planned” left from a different station than the one near my hotel.

That’s when I rebuilt the whole thing as a single Excel sheet — and I’ve used a version of it for every trip since. This is a hands-on tutorial to build your own holiday travel itinerary template in Excel, not just download one. By the end you’ll have a working planner with live formulas, color-coded days, and a budget that actually adds up.

Downloadable templates are fine, but they break the moment your trip doesn’t match the author’s assumptions. Building it yourself means you understand every cell — and you can bend it to a 3-day city break or a 3-week multi-country marathon.

Why build it instead of downloading one

• You control the structure. Most free templates assume one city, one currency, fixed hotel. Real trips aren’t that tidy.
• Formulas do the math. Running totals, per-day costs, and “days until departure” update themselves.
• It works offline. Cell signal in a Dolomites village or a Tokyo subway is not guaranteed. Excel on your phone (or a printed page) doesn’t care.
• It’s reusable. Save it as a template (.xltx) and your next trip starts in 30 seconds.

If you mostly want shared, real-time editing with travel companions, Google Sheets is the better tool — nearly every formula here works identically there. Use Excel if you want richer conditional formatting, offline reliability, and a desktop you trust.

What this won’t do (an honest disclaimer)

This planner organizes your research; it doesn’t replace it. Excel will not alert you to a museum that closed for renovation, a flight price that just dropped, a train strike, or the fact that your destination requires a visa or an ETIAS authorization. It has no live data feed. Treat it as a structured place to record what you’ve confirmed elsewhere — not as a tool that confirms anything for you. The most reliable cell in this whole workbook is the one where you paste a fact you personally verified.

The structure: five tabs, not one giant mess

The single biggest mistake people make is cramming everything onto one sheet. Split it into tabs:

Tab	Purpose
Dashboard	Trip summary, countdown, total budget vs. spent
Itinerary	The day-by-day, hour-by-hour plan
Budget	Every expense, categorized, with running total
Bookings	Confirmations: flights, hotels, trains, tickets
Packing	Checklist with checkboxes

Right-click the tab at the bottom → Rename for each. Right-click again → Tab Color so you can find them at a glance.

We’ll build the three that carry the weight — Itinerary, Budget, and Bookings — then wire up the Dashboard.

Short on time? The scaled-down 3-day version. Most trips are long weekends, and you don’t need all five tabs for one. For a 3-day city break, skip the Dashboard tab entirely (with three days, a countdown and a budget-vs-spent summary are overkill — you can see everything on one screen) and skip the Packing tab (a phone note is faster than a spreadsheet for a carry-on). Build only Itinerary, Budget, and Bookings. That trio takes about 20 minutes and covers everything that actually goes wrong on a short trip: a missed reservation, a misread train time, or a budget that quietly doubled. Everything below still applies — just ignore the Dashboard section.

Step 1: Build the Itinerary tab

Set up these column headers in row 1:

A: Day | B: Date | C: Time | D: Activity | E: Location | F: Category | G: Duration (hrs) | H: Cost | I: Booked? | J: Notes

Make the date column smart

You only want to type your start date once. In B2, type your departure date — say 2025-09-12. For the next entries on the same day, leave it blank or repeat it. When a new day starts, instead of retyping, reference the previous day:

=B2+1

Put that in the first cell of your next day. Now if your flight shifts by a day, you change one cell and every downstream date follows.

To auto-fill the Day column (Day 1, Day 2…), use:

="Day "&(B2-$B$2+1)

This subtracts your start date from the current row’s date and adds 1. Lock the start date with $B$2 (the dollar signs) so it doesn’t drift when you copy the formula down.

Format times so they sort correctly

Type times as 9:00 AM, 1:30 PM — real time values, not text like “morning.” Excel can then sort and calculate with them. Select column C → Format Cells (Ctrl+1) → Time.

Set up the Category dropdown

This is what makes color-coding possible. Click cell F2, go to Data → Data Validation → List, and in Source type:

Sightseeing,Food,Transport,Accommodation,Activity,Free Time

Drag that validation down the whole column. Now every category is consistent — no “Food” vs. “food” vs. “lunch” chaos that breaks your color rules and budget grouping.

Version note: In older Excel versions the menu may read Data → Validation rather than Data Validation, and the dialog tabs look slightly different — but the Allow: List option is the same. If you’re in Google Sheets, use Data → Data Validation → Add rule → Dropdown (from a list) and type the same comma-separated options.

Step 2: Color-code by category (the part people get wrong)

Manual cell coloring is a trap. The moment you reorder rows, your colors are wrong. Use Conditional Formatting so color follows the data.

1. Select your itinerary rows (e.g., A2:J100).
2. Home → Conditional Formatting → New Rule → Use a formula to determine which cells to format.
3. Enter:

=$F2="Transport"

4. Click Format → Fill, pick a soft blue, OK.
5. Repeat for each category with a different muted color:

Category	Suggested fill
Sightseeing	Light green
Food	Light orange
Transport	Light blue
Accommodation	Light purple
Activity	Light yellow
Free Time	Light grey

The $F2 (dollar on the column, not the row) is the trick: it locks the rule to column F while letting it apply across the whole row, so the entire line colors based on its category.

Insider tip: Keep fills pale. If you ever print or share, dark fills make text unreadable and burn through ink. Pale fills also let a second conditional rule — like a red border for “not booked” items — show through.

Step 3: A “Booked?” status that warns you

In column I, add a Data Validation dropdown with Yes,No,N/A. Then add a conditional format on those cells: a rule where =$I2="No" paints the cell red. Now anything unbooked screams at you from across the room. This single feature is why I haven’t missed a reservation since.

Step 4: Build the Budget tab

Headers in row 1:

A: Date | B: Item | C: Category | D: Currency | E: Local Amount | F: Exchange Rate | G: Cost (home currency) | H: Paid?

The currency formula

Traveling somewhere that doesn’t use your currency? Don’t do mental math at midnight. In G2:

=E2*F2

If a Florence dinner is €58 and your rate is 1.08 (USD per EUR), you’ll see $62.64 automatically. Put the rate in column F so you can update it as rates move — or just lock in one rate for the whole trip for simplicity.

Where to get the rate. For planning, use XE.com or Google’s rate (type “usd to eur” into Google) — both show the mid-market rate, which is fine for budgeting and close enough to reality. For reconciliation after the trip, ignore those: use your bank’s or card issuer’s posted rate from the actual statement, because that’s what you really paid (mid-market plus the issuer’s spread and any foreign-transaction fee). In short: XE or Google to plan, your statement to reconcile.

Trade-off: A single fixed rate is easier and accurate enough for planning. Do per-row rates only if you’re tracking actual card-statement amounts after the fact and want precision. For a plan, fixed is fine.

The running total

In a cell off to the side (say J1), label it Total, and in J2:

=SUM(G:G)

For category breakdowns, use SUMIF. In a small summary block:

=SUMIF(C:C,"Transport",G:G)

Copy that down for each category, changing the text. Now you can see at a glance that transport is eating 35% of the budget — usually the signal to rethink that rental car.

Budget vs. actual

Add a column for Budgeted and one for Actual, then a Variance:

=Budgeted-Actual

Conditional-format negative variances red. Overspending becomes visible the day it happens, not when you get home.

Step 5: Build the Bookings tab

This is your single source of truth for confirmation numbers — the tab you open at a hotel desk at midnight. Headers in row 1:

A: Type | B: Provider | C: Confirmation # | D: Date/Time | E: Cost

Add one calculated cell so totals are automatic. Off to the side, label G1 Total Booked, and in G2:

=SUM(E:E)

A worked example row:

Type	Provider	Confirmation #	Date/Time	Cost
Flight	ITA Airways	AB12CD	Sep 12, 10:05 AM	$310
Hotel	Hotel Davanzati	HD-558217	Sep 12–16	$620
Train	Trenitalia	TR-9X4Q	Sep 14, 9:30 AM	$12

Bold the confirmation number (column C). When you’re standing at a desk or a gate with a tired phone, you want it findable in two seconds — bolding makes it jump off the page faster than any formula will.

Step 6: Wire up the Dashboard

This is the page you’ll actually look at every morning. Set it up as a clean, scannable card by reserving the first few rows for an at-a-glance summary, then pulling numbers from the other tabs.

Use this layout in column B (label in column A):

Cell	Contents
Row 1	Trip name — e.g., Tuscany 2025
Row 2	Dates — Sep 12 – Sep 16, 2025
Row 3	Countdown cell — days until departure (formula below)
Row 4	Total trip cost
Row 5	Total spent
Row 6	Remaining
Row 7	Next unbooked item (a manual reminder you update)

A visual example of how that reads down the page: row 1 holds the trip name in a large bold font as a banner; row 2 shows the dates just beneath it in grey; row 3 is a big countdown cell (“47 days”) you can’t miss; rows 4–6 sit as a tidy three-line money block (cost, spent, remaining) with remaining color-coded green or red; row 7 is a one-line nudge like “Book Trattoria Mario.”

The formulas behind those cells:

• Total trip cost: =SUM(Budget!G:G)
• Total spent: =SUMIF(Budget!H:H,"Yes",Budget!G:G)
• Remaining: =YourBudget-TotalSpent
• Countdown to departure:

=Itinerary!B2-TODAY()

Format that cell as a Number (not a date) so it shows “47” days, not a date in 1900. Also make sure Itinerary!B2 is formatted as a Date, not stored as text — if your countdown looks like a date in 1900, that’s why. The TODAY() function updates every time you open the file — a little hit of motivation every morning.

Insider tip: Wrap formulas that might error in IFERROR so an empty cell shows a clean dash instead of #DIV/0!:

=IFERROR(SUM(Budget!G:G),"-")

A fully worked sample: 5 days in Tuscany

Here’s a real, copy-ready chunk you can drop into your Itinerary tab. All costs below are per person, based on two travelers sharing, mid-range, 2024–2025 ballpark. Lodging is tracked separately on the Budget tab. Adjust freely.

Day	Date	Time	Activity	Location	Category	Cost (per person)	Booked?
Day 1	Sep 12	2:00 PM	Land FCO, train to Florence	Rome → Florence	Transport	$55	Yes
Day 1	Sep 12	6:00 PM	Check in, walk to Duomo	Florence	Accommodation	—	Yes
Day 1	Sep 12	8:00 PM	Dinner, Trattoria Mario area	Florence	Food	$40	No
Day 2	Sep 13	9:00 AM	Uffizi Gallery (timed entry)	Florence	Sightseeing	$30	Yes
Day 2	Sep 13	1:00 PM	Lunch at Mercato Centrale	Florence	Food	$20	No
Day 2	Sep 13	4:00 PM	Climb Duomo dome (reserved)	Florence	Activity	$35	Yes
Day 3	Sep 14	9:30 AM	Train to Siena	Florence → Siena	Transport	$12	No
Day 3	Sep 14	11:00 AM	Piazza del Campo + Duomo	Siena	Sightseeing	$18	No
Day 3	Sep 14	7:00 PM	Back to Florence, free evening	Florence	Free Time	—	N/A
Day 4	Sep 15	8:00 AM	Pick up rental car	Florence	Transport	$60	Yes
Day 4	Sep 15	10:30 AM	Drive Chianti, San Gimignano	Tuscany	Activity	$25	No
Day 4	Sep 15	1:00 PM	Winery lunch, Greve in Chianti	Chianti	Food	$55	No
Day 5	Sep 16	9:00 AM	Pisa (tower photo, quick stop)	Pisa	Sightseeing	$20	No
Day 5	Sep 16	3:00 PM	Return car, evening flight prep	Florence	Transport	—	Yes

Add up the per-person costs above and the activities-and-transport total comes to $370 per person over five days (lodging not included — that’s on the Budget tab). Because it’s in Excel, you see that sum the moment you finish typing. Notice how the unbooked dinners and the Siena train (in red, in your real sheet) flag exactly what needs handling before you go.

Realistic timing note baked into this plan: I left a free evening on Day 3 on purpose. New planners over-schedule and burn out by day three. Leave genuine gaps.

Common mistakes (the non-obvious ones)

• Typing times as text. “Morning,” “afternoon,” and “after lunch” can’t be sorted or compared. Use real time values so the day stays in order when you add a forgotten 11 AM coffee.
• Hard-coding every date. When your flight moves, you’ll be retyping 18 cells. Use =previous+1 and change one.
• Coloring cells by hand. Reorder one row and the whole color scheme lies to you. Conditional formatting follows the data.
• No buffer time between activities. A museum that “closes at 6” plus a 4 PM entry plus a 20-minute walk doesn’t leave time for the gift shop or a bathroom. Pad transitions by 30–45 minutes in a city, more if you’re driving.
• Ignoring opening days. Many European museums and the Uffizi-tier sights close Mondays; lots of restaurants close Sunday or Monday. Put a note column entry: “CLOSED MON.” Excel won’t catch this for you — your research will.
• Forgetting time zones on flights. If your departure date in the Dashboard is in home time but your itinerary is in local time, your countdown can be off by a day across the dateline. Pick one convention and label it.
• One mega-row per day. Splitting each day into timed rows is what turns a vague wish-list into a plan you can actually follow.

Two finishing touches that signal you’ve done this before

• Freeze the header row. View → Freeze Panes → Freeze Top Row so headers stay visible as you scroll a 20-day trip.
• Save as .xltx template once it’s clean and empty: File → Save As → Excel Template. Every future trip starts pre-built — five tabs, formulas, and color rules already in place.

(The packing checklist, Plan-B notes, and walk-time mini-columns from earlier are worth adding too — but those bolt onto the spine you’ve already built.)

Should you sync it to your phone?

Yes — and here’s the honest trade-off. Excel’s mobile app handles viewing and light edits beautifully, but conditional formatting and complex formulas can feel cramped on a small screen. My workflow: build and edit on desktop, view on phone. Keep the file in OneDrive (or Google Drive if you went the Sheets route) so it’s always current and shareable with travel companions.

If your group wants to edit simultaneously — one person on hotels, another on restaurants — move the whole thing to Google Sheets. The formulas above (SUMIF, IFERROR, TODAY, date math, data validation, conditional formatting) all work there with minor menu differences.

Your actionable takeaway

Open a blank workbook right now and do just three things: create the tabs you need (all five for a long trip, or just Itinerary/Budget/Bookings for a weekend), build the Itinerary headers with the =B2+1 auto-date and the Category dropdown, and add the conditional-formatting color rule using =$F2="Transport". That core takes 15 minutes and is the spine of the whole planner — everything else bolts onto it later.

Build it once for your next trip, save it as a template, and you’ll never juggle six browser tabs at the airport again.