IFTA tracking spreadsheet: what columns you actually need, and what your old template is missing

Your IFTA tracking spreadsheet needs six core columns—date, start/end odometer, miles by state, gallons purchased, price per gallon, and state where purchased—but most templates drop the state-by-state mileage column, which is what auditors demand and what causes surcharge misses.

The six mandatory columns every IFTA spreadsheet must have

Date captures trip timing for your audit trail. Start and end odometer readings prove miles traveled and link to your fuel receipts. Miles by state (not total miles) separates your mileage across each jurisdiction—this is the column most templates omit and the one that auditors require. Gallons purchased is your fuel receipt data. Price per gallon supports documentation of your fuel costs. State where purchased identifies where fuel tax was actually paid versus where it was burned.

Arizona DOR requires separate miles per jurisdiction for taxable and nontaxable miles. Pennsylvania's IFTA-101 guidance explicitly states that miles traveled on each fuel trip must be recorded by jurisdiction. If your spreadsheet doesn't separate these, you can't reproduce the data an auditor will demand.

Why state-by-state mileage is the column that kills your audit

IFTA Inc. and state DMVs require miles traveled in each jurisdiction separately—not a fleet total. Auditors cross-check your mileage breakdown against fuel consumption using your average MPG. A single "Total Miles" column triggers surcharge-state miscalculations because you can't prove how many miles were burned in Kentucky, Virginia, New York, or New Mexico.

Iowa's record-keeping requirements mandate that jurisdiction miles be tracked separately for each vehicle on quarterly fleet summaries. Without this breakdown, you're reporting blind—and auditors assess penalties for inadequate records that exceed $10,000 per vehicle. When you run a Q2 audit and can't show the Missouri miles separate from the Arkansas miles, you can't explain why your fuel consumption per state doesn't match the expected rate. That's where auditors find the surcharge misses and assess back tax plus penalties.

Worked example: Q2 run across Missouri, Arkansas, Louisiana, Mississippi with the correct column structure

Here's a real Q2 2026 scenario with the six columns you need:

Total miles: 5,200. Total gallons: 840. Average MPG: 5,200 ÷ 840 = 6.19 MPG.

Now calculate fuel consumption per state using the fleet MPG:

Without the state-by-state mileage column, you'd have reported 5,200 total miles and 840 total gallons purchased, but auditors would have no way to verify that you allocated 1,400 miles to Missouri (not 1,600), 1,800 to Arkansas (not 1,400), and so on. The surcharge states depend on this exact breakdown.

What most spreadsheet templates are missing

Common template flaw: a combined "Total Miles" column instead of separate columns for miles in each jurisdiction. The consequence is you can't reproduce your mileage allocation if audited. Assessments for inadequate records may exceed $10,000 per vehicle.

Missing column: "State where purchased" (separate from "State where burned"). You might buy fuel in Oklahoma but burn it in Texas—and the two states have different rates and audit expectations. Missing tracking of bulk fuel withdrawals and tank inventory reconciliation also exposes you. If you pull 100 gallons from a bulk tank in Arkansas on the 15th, that withdrawal must be dated, documented, and tied to a specific vehicle. Minnesota requires the location of the bulk storage, date of withdrawal, quantity, fuel type, and vehicle identification.

Missing data: fuel type (diesel, gasoline, LPG, CNG, ethanol blends). Colorado requires separate totals for each fuel type by jurisdiction. DEF and reefer fuel are excluded from your IFTA return. Most templates also skip the receipt number or odometer-at-purchase column, which means you can't cross-reference your spreadsheet to your actual fuel receipts during an audit. Prepaid receipts and credit card statements without itemized purchase information do not meet documentation requirements.

How to add the surcharge-state trap column to catch Kentucky, Virginia, New York, New Mexico

Add a seventh column: "Surcharge State" (Y/N). Kentucky, Virginia, New York, and New Mexico calculate surcharges with zero tax-paid credit. Your fuel receipt shows a tax rate, but surcharge liability is calculated independently on top of or instead of the base tax. Kentucky's surcharge applies to all gallons consumed in Kentucky, regardless of where you bought them. If you buy 50 gallons in Ohio and burn them in Kentucky, you owe the surcharge on all 50 gallons. Without this column marked, you'll report tax paid in Kentucky but miss the surcharge calculation entirely—a $50–$100 miss per Q2 on a single truck running that corridor.

Visit IFTA Inc. for the complete list of jurisdictional exemptions and surcharge rules.

The MPG column that auditors actually verify

Calculate average fleet MPG quarterly: total miles ÷ total gallons, rounded to two decimal places. Use this single MPG to allocate fuel consumption across all states. This is the IFTA standard—not state-by-state MPG. Tennessee, Pennsylvania, and Iowa all specify that MPG must be rounded to two decimal places.

Discrepancies in MPG ratios (unusually high or low) trigger audit flags. If your fleet usually averages 6.2 MPG but Q2 shows 8.1 MPG, auditors will ask why. Your spreadsheet must show the calculation step-by-step: total miles, total gallons, resulting MPG, and then (state miles ÷ fleet MPG) for each state.

Receipt documentation: what column headers protect you in an audit

Add a column: "Receipt Number" or "Odometer at Purchase" to cross-reference your fuel receipt to your trip. Prepaid fuel receipts are not acceptable—you must get a receipt with the required information. O.T.R. receipts must identify the vehicle unit or plate number. Your spreadsheet must be able to reproduce any electronic data by unit and by trip for four years. If an auditor asks for the receipt for the 45-gallon purchase on April 25, you need to pull it in under 30 seconds.

Records must be maintained for a period of four years from the due date of your IFTA return.

Set up column formatting so you don't lose data during a four-year retention cycle

Use date format consistent with your fuel receipts (MM/DD/YYYY or YYYY-MM-DD). Lock in odometer readings with vehicle unit number so you can't accidentally swap data between trucks. Separate columns for state miles, not a combined field. Auditors need to isolate Louisiana miles from Mississippi miles instantly. Use formulas (not hardcoded numbers) for MPG and fuel consumption so changes update automatically. If you discover you missed a trip, the formulas recalculate; hardcoded numbers hide errors.

Store your quarterly IFTA rate lookup table on a separate sheet with the IFTA Inc. link and the date you accessed it. Include the effective quarter and rate source. Electronic data must be reproducible; avoid proprietary formats that won't open in four years. Use .xlsx or .csv, not a vendor's locked database. If you switch software in year two, your old records must still be readable.