Create an Excel Fleet Mileage and Service Tracker in WPS Spreadsheet
Question details
The user needs a spreadsheet to track vehicle mileage, calculate upcoming service mileage, and show how many miles remain before each service is due.
- Product
- WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- A fleet manager wants to track multiple vehicles and service types, including oil changes, tire rotations, wiper replacements, battery replacements, and coolant changes, using vehicle-specific service intervals.
- Observed behavior
- The tracker needs to retrieve the correct service interval for each vehicle and service type, calculate the next service mileage, and subtract current mileage to display the remaining miles.
List every vehicle and service type you want to track, then confirm the service interval for each vehicle and service combination. Decide whether the starting mileage for a new vehicle should be its current mileage or its previous service mileage.
Build a lookup-based fleet mileage and service tracker
Use this method when different vehicles have different service intervals and you want the tracker to update calculations from one reference table.
Create a service interval table with a unique key for each vehicle and service type. In the tracker, retrieve the matching interval, add it to the previous service mileage, and subtract the current mileage to calculate the miles remaining.
In a new WPS Spreadsheet sheet, add headers for Vehicle, Service Type, Current Mileage, Last Service Mileage, Service Interval, Next Service Mileage, and Miles Remaining in cells A1:G1.
In columns J:M, create headers Lookup Key, Vehicle, Service Type, and Interval. In J2, enter =K2&"|"&L2, then fill the formula down for each vehicle and service combination. Enter intervals such as 5000 for an oil change or 60000 for a battery replacement in column M.
In E2, enter =IFERROR(VLOOKUP(A2&"|"&B2,$J$2:$M$100,4,FALSE),"") and press Enter. This looks up the interval using the vehicle and service type in A2 and B2.
In F2, enter =IF(E2="","",D2+E2). This adds the service interval to the last service mileage. For a vehicle that has not had the service before, enter its current mileage in the Last Service Mileage column.
In G2, enter =IF(F2="","",F2-C2), then copy E2:G2 down the tracker. Positive values show miles remaining, while zero or negative values indicate that service is due or overdue.
Use a direct multi-condition formula for a smaller tracker
Use this approach when the workbook contains a simple service table and you prefer not to create a combined lookup-key column.
Track fleet mileage and service schedules with WPS Spreadsheet
WPS Spreadsheet provides the lookup formulas, tables, conditional formatting, and Excel-compatible workbook support needed to build and maintain a fleet mileage and service tracker.
- 1. Start a spreadsheet: Open WPS Office, select Spreadsheet, and choose Blank to create the tracker.
- 2. Add tracker and lookup tables: Enter the vehicle mileage columns on the first sheet and create the vehicle, service type, and interval lookup table beside or on a second sheet.
- 3. Insert the formulas: Use VLOOKUP or SUMIFS for the service interval, then add formulas for Next Service Mileage and Miles Remaining.
- 4. Apply maintenance formatting: Select the Miles Remaining column and use Home > Conditional Formatting to highlight values at or below zero.
Frequently Asked Questions
How should I calculate the next service mileage for each vehicle?
Add the service interval to the vehicle's last service mileage. In the example tracker, use =IF(E2="","",D2+E2) in the Next Service Mileage column, where D2 is Last Service Mileage and E2 is the looked-up interval.
How can one tracker support different service intervals for different vehicles?
Create one row in the lookup table for each vehicle and service type combination. Use a combined key such as Vehicle|Service Type with VLOOKUP, or use SUMIFS with separate Vehicle and Service Type criteria.
What should I enter when a vehicle has never received a specific service?
Enter the vehicle's current mileage in the Last Service Mileage field for that service. The next service mileage will then be calculated from the current mileage plus the service interval.
How do I show whether service is due or overdue?
Subtract Current Mileage from Next Service Mileage with =IF(F2="","",F2-C2). A result of zero means service is due, and a negative result means the vehicle is overdue.
Why does the lookup formula return a blank value?
Check that the vehicle and service type in the tracker exactly match the lookup table, including spaces and spelling. Also confirm that the lookup range includes the interval column and that the combined key formula has been filled down.
