Car Service Garage Management System in Excel: Job Cards, Parts and Labour, GST Invoice, Service Reminders and Dashboard

By Ravindra Bagale · Last updated: 17 October 2026

← All Excel dashboards

Download the template: car-service-garage-management-system-template.xlsx: a 8-sheet system with example data and real formulas (Read me, Setup, Vehicles, Job lines, Job cards, Invoice, Report, Dashboard). Yellow cells are inputs you can type over; green cells hold formulas; blue rows are headers and totals. Replace the example data with your own and every report and the dashboard recalculate.

Made-up data: every name, amount, business and date on this page is made up for practice. Cities are used only to make the examples feel real.

1. The business story

Bagale Auto Care, Nashik, October 2026 (made-up data). Labour ₹600 an hour; GST on parts and labour are inputs (18% here; confirm the rate for each part and service). Service every 10,000 km or 6 months; reminder window 30 days.

Six job cards this month total ₹30,290.60 with GST. For a personal service log, see the car and bike service log; for fleets, the transport fleet system.

2. Build it sheet by sheet

Step 1. Setup and Vehicles

1. Setup B4 labour rate (₹600), B5 and B6 GST on parts and labour, B7 service interval in km (10,000), B8 in months (6), B9 reminder window (30 days).
2. Vehicles: Reg no, Owner, Type, Service before Oct. The reg no is the key used everywhere.

Step 2. Job cards and Job lines

1. Job cards: A5 ="JC-"&TEXT(ROW()-4,"000"), date, reg no, D5 owner with XLOOKUP, km in, mechanic.
2. Job lines: Job no, Type (Part or Labour), Description, Qty or hours, Rate. E7 shows a labour rate pulled from Setup.
3. F5 amount =D5*E5, G5 GST =ROUND(F5*IF(B5="Part",Setup!$B$5,Setup!$B$6),2), H5 key =A5&"-"&COUNTIF($A$5:A5,A5).

Step 3. Job card totals

1. G5 parts =SUMIFS('Job lines'!$F$5:$F$2000,'Job lines'!$A$5:$A$2000,A5,'Job lines'!$B$5:$B$2000,"Part") and H5 labour the same with "Labour" (SUMIFS).
2. I5 labour hours, J5 GST, K5 total =G5+H5+J5, L5 next service km =E5+Setup!$B$7.

Step 4. Vehicles: reminders

1. E5 last service: =MAX(D5,MAXIFS('Job cards'!$B$5:$B$500,'Job cards'!$C$5:$C$500,A5)) (MAXIFS).
2. F5 next due =EDATE(E5,Setup!$B$8) (dates), G5 days left =F5-Setup!$B$3.
3. H5: =IF(G5<0,"Overdue",IF(G5<=Setup!$B$9,"Call now","OK")). Colour it with conditional formatting.

Step 5. Invoice: print one job

1. Type a job no in B4; D4 finds the reg no and B8 the first line: =XLOOKUP($B$4&"-"&A8,'Job lines'!$H$5:$H$2000,'Job lines'!$C$5:$C$2000,"").
2. JC-003 lists engine oil, oil filter, air filter and 2.5 hours of service. Set the print area to A1:F19.

Step 6. Report and Dashboard

1. Report per mechanic: jobs, labour hours, labour, parts and total with GST.
2. KPI cards: jobs, billing, labour share =Report!D8/(Report!D8+Report!E8), average bill, vehicles to call and labour hours. Charts: billing by mechanic and parts vs labour.

3. Formulas used

Every formula above is written in the template, so you can click a cell and see it in the formula bar. All values on this page were recalculated in LibreOffice with zero errors.

4. Common mistakes

1. Totals typed on the job card. Let SUMIFS add the job lines; a typed total does not change when a line is added.
2. Last service from the register only. MAXIFS on Job cards picks up services done this month; MAX with the old date covers cars not yet seen.
3. Adding 180 days for 6 months. EDATE adds calendar months, so 25 April becomes 25 October.
4. Same reg no typed two ways. "MH15 AB 1234" and "MH15-AB-1234" do not match. Use one format, ideally from a drop-down.
5. Ignoring the km reminder. Next service km is on the job card; ask the km at every visit and service whichever comes first.

5. More real examples

Example 1: Two-wheeler workshop. Use a 3,000 km or 3-month interval and lower labour rates.

Example 2: Tyre and battery shop. Track battery warranty months with EDATE from the sale date.

Example 3: Fleet maintenance. Add a Company column on Vehicles and a company-wise report, as in the fleet system.

Quick revision cards

Englishमराठीहिंदी

Quick revision

Each card shows formulas from this page, each with an example and its answer from the template. Scan the QR code on a card to open this lesson. All names and numbers are made up for practice.

More cards: Ravi Sir's Cards.

6. Frequently asked questions

How do I find the next service date in Excel?

Take the last service date and add the interval with EDATE: =EDATE(E5,6).

How do I find a car's last service from many job cards?

MAXIFS on the job date where the reg no matches.

How do I split parts and labour on a job card?

SUMIFS on the job lines with the job no and the line type as conditions.

What GST applies to car service?

Rates differ by part and service. The template keeps them as inputs; confirm the current rates with your accountant.

How do I list cars to call?

Days left = next due − today; show "Call now" when it is between 0 and the window and "Overdue" below 0.

7. Quick quiz

Ten questions on this page. Sign in to save your score.