CRA Mileage Log Template Excel: What to Track and Why

How-to basics · Updated November 22, 2026

For a CRA mileage log template in Excel, create columns for the trip date, starting and ending odometer readings, distance, destination, and business purpose, then keep personal and business travel clearly identified. CRA does not require a particular spreadsheet or official template; your records need to be complete enough to support the vehicle expenses or employment expenses you report.

What a CRA mileage log is for

A mileage log, often called a vehicle logbook, records how much you drive for business or employment compared with total vehicle use. That distinction matters because qualifying vehicle expenses are generally limited to the portion related to earning business or employment income. A log helps explain the calculation and provides evidence if the Canada Revenue Agency asks how you arrived at it.

The rules and deductions depend on your situation. A self-employed person may claim the business-use portion of eligible vehicle expenses, subject to applicable limits and recordkeeping requirements. An employee generally needs to meet specific conditions to deduct employment expenses and may need a completed Form T2200 from their employer before claiming eligible costs on their return. A mileage log by itself does not make an expense deductible.

CRA does not prescribe Excel as the required format. A paper log, spreadsheet, or suitable electronic record can work if it is accurate, legible, available, and supported by records such as receipts. Keep the information organized by vehicle and tax year, and retain records for the required period; CRA generally says to keep tax records for six years, with exceptions in some circumstances.

Fields to include in an Excel template

Use one row for each trip, or for each distinct leg when a journey combines different purposes. A practical worksheet can include these columns:

  • Date: The day the trip took place.
  • Starting and ending odometer: Readings that allow the distance to be checked, rather than only a manually entered estimate.
  • Distance in kilometres: The difference between the two readings.
  • Starting point and destination: Specific locations help make the trip understandable later.
  • Purpose: A short business or employment reason, such as “site inspection” or “meeting with client.”
  • Trip classification: Mark the row as business or personal. If a journey has both kinds of travel, split it into separate rows where practical.
  • Client, project, or notes: Optional context that helps connect the trip to other supporting records.

Also keep annual odometer readings for the vehicle at the start and end of the period. They provide a way to compare logged travel with the vehicle’s total kilometres. If the vehicle was bought, sold, or used for only part of the year, note the relevant dates and readings instead of presenting the period as a full year.

Set up the worksheet and calculate kilometres

In a blank Excel workbook, enter the headings in row 1: A Date, B Start km, C End km, D Distance km, E Trip type, F From, G To, H Business purpose, and I Client or notes. Enter trips from row 2 down. Record odometer readings as numbers, and use a consistent label such as Business or Personal in column E.

With the starting odometer in B2 and ending odometer in C2, enter this in D2 to calculate distance and leave the cell blank if either reading is missing: =IF(OR(B2="",C2=""),"",C2-B2). For example, a trip starting at 12,540 km and ending at 12,568 km returns 28 km. Copy the formula down the column. A negative result usually means the readings were reversed or entered incorrectly, so investigate it rather than treating it as a valid distance.

To make the log easier to review, select the headings and trip rows and choose Insert > Table. On Windows, Ctrl+T also opens the Create Table dialog; on Mac, Command+T is the usual shortcut. A table carries formulas into new rows and provides filter buttons for dates, destinations, and trip types. If using Excel for the web, the Insert > Table command is available there too, though shortcuts can vary by browser and keyboard.

Calculate the business-use percentage

Keep annual odometer readings in a clearly labelled summary area. For example, put the opening reading in K2 and the closing reading in K3. With those cells containing 12,000 and 20,000 respectively, enter this formula in K4 to calculate total kilometres: =K3-K2. The result is 8,000 km.

With trip type in E2:E500 and calculated distance in D2:D500, enter this formula in K5 to total rows labelled Business: =SUMIF(E2:E500,"Business",D2:D500). If those rows add up to 2,000 km, K5 returns 2,000. Adjust the range to cover your actual log, and make sure the label in the formula matches the label used in the worksheet.

With business kilometres in K5 and total kilometres in K4, enter this in K6 to calculate the business-use share: =IFERROR(K5/K4,0). In the example, 2,000 divided by 8,000 returns 0.25, or 25%. Format K6 as a percentage using Home > Number, then Percentage Style. This percentage is a recordkeeping calculation, not the final amount of a tax deduction: eligible expenses, special limits, and your circumstances still matter.

Keep the log credible and easy to check

Make entries close to the time of each trip. Reconstructing a year of travel from memory can leave missing destinations, vague purposes, and mileage that does not reconcile with odometer readings. Short descriptions are fine when they are specific: “delivery to Oak Street office” is more useful than “work.” Keep receipts and invoices separately, but use a client or project note when it helps connect a trip to them.

  • Check that each trip’s ending reading is not below its starting reading.
  • Compare summed trip distances with the opening and closing odometer readings. Explain gaps, such as personal trips that were not itemized, if your method requires them to be accounted for.
  • Do not count the same distance twice, and do not classify ordinary personal travel as business simply because it happened on a workday.
  • Keep a backup copy of the workbook and protect it from accidental edits. Save the original records and receipts, not just a percentage calculated in a summary cell.

A common point of confusion is commuting. Travel from home to a regular workplace is generally personal travel, not business kilometres merely because the employee is going to work. Travel between work locations or to a business destination may be different. The facts matter, so describe the actual trip and check the CRA rules that apply to your circumstances.

Full-year and simplified logbook approaches

A full logbook records the relevant trips throughout a complete year. CRA also describes a simplified logbook method for some people who have already kept a full logbook for a base year. In later years, a representative three-month sample may be used to estimate business use when the vehicle’s use remains consistent with the base year and the CRA conditions are met. The sample period should reflect typical use, and annual odometer readings are still important.

The simplified method is not permission to pick an unusually busy quarter and assume it represents the whole year. Compare the sample with the base-year business-use percentage and review CRA’s current guidance before relying on it. If business use changes substantially, the vehicle changes, or the sample is not representative, a full-year log may be the more dependable record. Keep the original base-year logbook and the supporting records for the later sample period.

Common spreadsheet errors and useful checks

One frequent error is mixing odometer readings and distances. The opening and closing readings measure all kilometres over a period; the trip rows describe particular journeys. The total of logged business distances should make sense alongside the annual total, but the two figures are not expected to match if the vehicle also made personal trips.

Another error is relying on a “business” label without recording why the trip qualified. A destination and a concise purpose make the entry more informative. Blank formula results should also be treated as incomplete entries, not as zero-kilometre trips. Before using the annual summary, filter the table for blank dates, blank purposes, and negative distances, then resolve them.

Excel’s formulas do arithmetic; they do not determine whether a trip or expense qualifies under tax rules. Avoid putting confidential personal or client information in a workbook that is shared broadly. Use a separate, access-controlled file if necessary, and keep a read-only backup of the version used to prepare records.

Frequently Asked Questions

Does CRA provide an official mileage log template?
CRA explains the records taxpayers should keep, but it does not require one official Excel template. You can build a spreadsheet or use another recordkeeping format if it captures the relevant trip details and supports the kilometres and expenses claimed.

Do I need to record every trip, including personal trips?
Your records need to establish total vehicle use and the business or employment portion. A detailed full logbook can record each trip and identify personal travel; at minimum, keep enough complete information to support both the business-use calculation and annual odometer totals. Follow the method and requirements that apply to your situation.

Can I claim a flat amount for each business kilometre?
Do not assume that a mileage log automatically gives you a per-kilometre deduction. Self-employed vehicle expense claims generally use eligible actual expenses apportioned to business use, subject to limits. A reasonable per-kilometre allowance paid by an employer has separate tax treatment and is not the same as an employee claiming vehicle expenses.

What if my odometer reading is wrong or missing?
Correct an error transparently using reliable supporting information, such as a service invoice or dated photo, and note the correction. Do not silently change a figure to force the totals to match. If a reading is unavailable, document how you reconstructed it and retain the evidence.

Can I use the same Excel log for two vehicles?
It is clearer to keep separate logs or clearly separated vehicle sections, with the vehicle identified and opening and closing readings recorded for each. A combined summary can be useful, but it should not obscure which trips and expenses belong to which vehicle.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.