How to Create a Travel Expense Report in Excel

Creating a travel expense report is one of those tasks that feels like it should be simple, but can quickly become a mess of lost receipts and confusing calculations. Using Excel is a reliable way to get it organized, and this article will walk you through building a clean, professional, and reusable travel and expense (T&E) report from scratch. We’ll cover everything from structuring the basic layout to adding automated formulas and pro tips for making it easy to use.

First, Plan Your Expense Report Layout

Before you even open Excel, take a minute to think about the information you need to capture. A well-planned report is easier to build and much easier for you and your manager to understand. A complete travel expense report typically has four main sections.

1. General Report & Employee Information

This is the "who" and "what" of the report. It identifies the employee, the purpose of the trip, and the reporting period. Make sure you have fields for:

• Company Name

• Employee Name & ID

• Department or Team

• Report ID (a unique number for tracking)

• Purpose of Trip (e.g., "Q3 Client Meeting - New York")

• Travel Start Date & End Date

• Submission Date

2. Detailed Expense Breakdown

This is the core of your report, a table where you’ll list every single expense. Each row will represent one transaction. Consider these columns:

• Date: The date the expense occurred.

• Category: The type of expense (e.g., Lodging, Meals, Airfare, Ground Transportation). Using categories makes it easy to analyze spending later.

• Description: A brief note about the expense (e.g., "Dinner with Acme Corp team," "Taxi to airport").

• Vendor: Where the money was spent (e.g., Hilton Garden Inn, United Airlines).

• Amount: The cost of the item.

• Currency: Especially important for international travel.

• Payment Method: How it was paid for (e.g., Corporate Card, Personal Card, Cash).

• Receipt Number: A number you assign to the physical or digital receipt for easy cross-referencing.

3. Mileage Log

If you used a personal or company vehicle for travel, you'll need a separate section to track mileage for reimbursement. This table should include:

• Date

• Origin (Starting Point)

• Destination (Ending Point)

• Purpose of Trip

• Odometer Start & Odometer End (Optional but recommended)

• Total Miles

• Reimbursement Rate (e.g., the standard IRS rate)

• Total Reimbursement Amount

4. Totals and Approval

Finally, a section at the bottom to summarize everything and capture signatures. This includes:

• Grand Total for All Expenses

• Subtotal for Expenses Paid with a Corporate Card

• Total Amount to be Reimbursed to the Employee (Grand Total minus Corporate Card expenses)

• Allowance / Advance Received (if applicable)

• Final Amount Due

• Spaces for employee and manager signatures and dates.

Step-by-Step: Building Your Travel Expense Report in Excel

With a clear plan, you're ready to build your template in Excel. Open a clean workbook and let’s get started.

Step 1: Set Up the Header

Click on cell A1. This is where your header information will live. Merge cells to create clean titles and use columns A and B for labels and C and D for the actual information.

For example:

• Merge cells A1:F1 and type "Travel Expense Report" and make it bold.

• In A3, type "Employee Name:" In C3, type the employee's name.

• In A4, type "Department:" In C4, type the department name.

• Fill out the rest of the general information this way (Report ID, Purpose of Trip, Dates, etc.). This keeps everything neat and separated from the raw expense data.

Step 2: Create the Detailed Expense Table

Leave a few empty rows below your header section. In row 10, start creating the column headers for your detailed expense table that we planned earlier: Date, Category, Description, and so on.

Make these headers bold to help them stand out. Select the entire header row and click the "B" button on the Home tab. To turn this into a formal Excel Table, which makes sorting and filtering easier, click anywhere inside your data and press Ctrl + T (or Cmd + T on Mac). Make sure the "My table has headers" box is checked. Your table will now have nice formatting and filter dropdowns on each column.

Step 3: Add Formulas for Automatic Calculations

This is where Excel's power comes in. Manually adding up expenses is tedious and prone to errors. Let's automate it.

Total Expenses

At the bottom of your expense table, find the 'Amount' column. Let’s say your amounts are in column G, from G11 to G30. A few rows below your table, create a "Summary" section. In one cell, type the label "Grand Total Expenses". In the cell next to it, enter the SUM formula:

This formula will automatically add up all the values in the specified range. If you used an Excel Table (from pressing Ctrl + T), you can use a structured reference which is even better. Simply click the cell where you want the total and type =SUM( and then select the entire 'Amount' column in your table with your mouse. Excel will write the formula for you, like:

This way, the formula automatically expands as you add new rows to your table.

Step 4: Build a Summary by Category

Seeing a breakdown of spending by category is incredibly useful. In your summary section, list out your primary expense categories (Lodging, Meals, Airfare, etc.). We'll use the SUMIF formula to total amounts for each one. The formula is: =SUMIF(range, criteria, sum_range).

• range: The column containing the categories (your 'Category' column).

• criteria: The specific text you're looking for (e.g., "Meals").

• sum_range: The column with the amounts (your 'Amount' column).

For example, if your report categories are in column B and your amounts are in column G, the formula to total all your meal expenses would be:

Do this for each category. It provides a crystal-clear overview of where the money went.

Step 5: Create the Mileage Tracking Table

Find some empty space below your main expense area to build the mileage log. Create columns for Date, From, To, Purpose, Miles, Rate, and Total.

You can input the standard reimbursement rate (e.g., $0.67 for 2024 in the US) in a dedicated cell or directly in the formula. If your 'Miles' are in column E and your 'Rate' is in F, the formula for the 'Total' column (G) in the first row of this table would be:

Drag the small green square at the bottom-right of the cell down to automatically apply this formula to all rows in your mileage log. Then, calculate the total reimbursement with a SUM formula at the bottom, just like you did for the general expenses.

Step 6: Calculate the Final Amount Due

In your summary section, you need to calculate the final reimbursement owed to the employee. This involves a few simple subtractions. Lay it out clearly:

• Grand Total Expenses: Your SUM formula from Step 3.

• Add: Total Mileage Reimbursement: The SUM from your mileage table.

• Less: Corporate Card Expenses: Use a SUMIF to total expenses paid with the corporate card. It's the same logic as the category summary: =SUMIF(H11:H30, "Corporate Card", G11:G30) assuming your Payment Method is in column H.

• Less: Cash Advance: A manually entered field if the employee received an advance.

• Amount Owed to Employee: Subtract the Corporate Card total and Cash Advance from the Grand Total.

Step 7: Format for a Professional Look

A clean report is an easy-to-read report.

• Currency Formatting: Select all cells with monetary values, right-click, choose "Format Cells," and select "Currency."

• Borders: Add borders around your tables and summary sections to create clear visual separation.

• Conditional Formatting: Highlight expenses over a certain amount (e.g., meals over $75) to draw attention to them. Select the 'Amount' column, go to Home > Conditional Formatting > Highlight Cell Rules > Greater Than.

• Data Validation: To ensure consistent categories, create a dropdown list. Go to the Data tab > Data Validation. In the "Allow" dropdown, choose "List". In the "Source" box, type your categories separated by commas (e.g., Lodging, Meals, Airfare). This prevents typos and keeps your SUMIF formulas working perfectly.

Bonus Tip: Save as a Reusable Excel Template

Once you are happy with your report, you can save it as an Excel Template file (.xltx). This way, you don't risk overwriting a completed report. Next time you need to file expenses, just open the template, and a fresh, clean copy will be ready for you to fill out.

To do this, go to File > Save As. In the "file type" dropdown menu, select "Excel Template (*.xltx)". Save it in your custom templates folder, and it will be available to you every time you open Excel.

Final Thoughts

Building a travel expense report in Excel gives you complete control and flexibility. By combining headers, tables, and powerful formulas like SUM and SUMIF, you can create a professional, accurate, and easy-to-understand document that saves you time and reduces manual errors every time you travel.

As your company grows, you might find yourself juggling dozens of these spreadsheets every month. That's when manual reporting becomes a bottleneck. We built Graphed to solve this by connecting all your data sources - from spreadsheets and accounting software like QuickBooks to tools like your CRM - in one central place. Instead of spending hours in Excel, you can use natural language to ask questions like, "Show me a dashboard of our total travel and expense spending by department this quarter" and get live, visual answers in seconds.