Excel for auto repair and body shops

Q: How do I calculate technician efficiency in Excel?

Divide hours flagged by hours clocked: =SUMIF(tech_column,"Marco",hours_column)/clocked_hours. Format it as a percentage. For the whole shop, divide total flagged by total clocked.

For shop owners, service writers and managers who want to know what a day in the bays really earned. Excel prices an estimate from labor hours and marked-up parts, works out your average repair order, shows each technician's efficiency from flagged and clocked hours, and tracks comebacks and parts that need reordering. Every formula below runs on one Tuesday of repair orders from a three-bay shop you can edit.

Independent shop owners
Service writers and advisors
Shop foremen and managers
Body shop estimators
Mobile mechanics

Sample data

Every result on this page comes from this sheet, calculated by the same engine the Cellwise app uses. Change a number and watch the formulas react.

	A	B	C	D	E	F	G	H
1	RO	Tech	Job	Billed hrs	Parts cost	Comeback	Shop settings
2	4821	Marco	Brakes	2.8	186.4	No	Labor rate	145
3	4822	Dee	Diagnostics	1.5	0	No	Parts markup on cost	0.5
4	4823	Sam	Maintenance	1.3	42.75	No	Tech pay per hour	32
5	4824	Marco	Suspension	4.8	412	No	Hours clocked today
6	4825	Dee	Electrical	2.2	138.9	No	Marco	8.5
7	4826	Sam	Brakes	2.6	164.2	No	Dee	9
8	4827	Marco	Maintenance	1.4	58.3	No	Sam	8
9	4828	Dee	Engine	4.5	690	No
10	4829	Sam	Maintenance	0.9	31.5	No
11	4830	Dee	Electrical	0	0	Yes

What Excel does for auto repair

Price a repair: labor hours times rate, plus marked-up parts

A repair line has two halves. Labor is the book (or flagged) hours times your door labor rate. Parts are your cost plus a markup. Keep them as separate numbers so you can see which half is carrying the job.

Round the parts price to cents as you go, the way it will print on the invoice, so the total on screen matches what the customer sees. Markup on cost is not the same as margin: 50% markup leaves a 33% margin on the parts.

=D5*H2+ROUND(E5*(1+H3),2)

Result: 1314

$1,314 for Marco's suspension job: 4.8 hours at $145 is $696 of labor, and $412 of parts at 50% markup is $618.

Work out your average repair order (ARO)

ARO is total sales divided by the number of repair orders. It is the number most shops watch week to week, because car count and ARO together are your sales.

The gotcha: do not count comebacks and other no-charge ROs in the denominator. A free warranty visit is a repair order in your system but not a sale, and counting it drags ARO down for the wrong reason. COUNTIF on the Comeback column counts only the paid ones.

=ROUND((SUM(D2:D11)*H2+SUM(E2:E11)*(1+H3))/COUNTIF(F2:F11,"No"),2)

Result: 641.79

$641.79 average over the 9 paying repair orders: $3,190 of labor and $2,586.08 of parts. Dividing by all 10 ROs would understate it at $577.61.

Measure technician efficiency

Technician efficiency is hours flagged (billed) divided by hours clocked. A flat-rate tech who flags 9 hours in an 8.5 hour day is running over 100%, which is what a good tech on familiar work looks like. Well under 100% means jobs are running long, the tech is waiting on parts, or the work is new to them.

Put each tech's name next to their clocked hours, then SUMIF the billed hours for that name. Pointing the formula at the name cell instead of typing "Marco" lets you copy it down for every tech.

=SUMIF(B2:B11,G6,D2:D11)/H6

Result: 1.0588235294 shows as 105.9% with Percent format

105.9% for Marco: 9 hours flagged on 8.5 clocked. Dee is at 91.1% because the free electrical comeback used her time and billed nothing. Sam, the apprentice, is at 60%.

See your effective labor rate

Your posted rate is $145, but you only collect it on hours you bill. Divide labor sales by the hours you paid techs to be on the clock and you get the effective labor rate: what each paid hour actually brought in.

It is the most honest single number for the shop floor. Comebacks, waiting on parts, and slow diagnostics all show up here even when the posted rate looks healthy.

=SUM(D2:D11)*H2/SUM(H6:H8)

Result: 125.09803922 shows as $125.10 with Currency format

$125.10 per paid hour: 22 hours billed at $145 is $3,190, earned over 25.5 hours on the clock. Shop efficiency overall is 86.3%.

Check the gross profit on a job

Gross profit on a job is labor sales minus what you paid the tech for those hours, plus the profit on the parts. With labor at $145 and tech pay of $32 an hour, every billed hour leaves $113 before rent, tools and the service writer.

This uses billed hours for the tech's time, which is right for flat-rate pay. If your techs are paid hourly and a job ran long, use the hours they actually spent on it instead.

=D9*(H2-H4)+E9*H3

Result: 853.5

$853.50 gross profit on Dee's engine job: $508.50 from 4.5 labor hours and $345 from the $690 of parts at 50% markup.

Track your comeback rate

A comeback is a car that returns for the same problem. It costs you twice: the free labor and parts, and the bay time you could have sold. Mark each RO Yes or No and count the Yes rows against all ROs written.

One day is too small to judge. Track it by the month and by technician; most shops aim to keep comebacks in the low single digits as a percentage.

=COUNTIF(F2:F11,"Yes")/COUNTA(A2:A11)

Result: 0.1 shows as 10% with Percent format

10%: 1 comeback out of 10 ROs today. On a single day that is one car, so read it alongside the monthly figure.

Total sales by job type

Which kinds of work pay the bills? SUMIF the hours and the parts cost by job type, then apply your labor rate and markup. Brakes and maintenance bring cars in; diagnostics and engine work usually carry more labor per RO.

Use SUMIFS instead if you want a second condition, such as one tech's brake work only.

=SUMIF(C2:C11,"Brakes",D2:D11)*H2+SUMIF(C2:C11,"Brakes",E2:E11)*(1+H3)

Result: 1308.9

$1,308.90 from the two brake jobs: 5.4 labor hours ($783) and $350.60 of parts sold for $525.90.

Practice: 8 exercises

Use the sample data above. Type a formula and press Check: it is graded by a real spreadsheet engine, the way Cellwise grades lessons.

1How many hours were billed in total today?

Your formula for exercise 1

Show answer
=SUM(D2:D11) = 22. SUM adds every RO's billed hours: 22.
2How many maintenance jobs were written?

Your formula for exercise 2

Show answer
=COUNTIF(C2:C11,"Maintenance") = 3. COUNTIF counts the rows where Job is Maintenance: 3.
3What did the shop sell in parts today, at the markup in H3?

Your formula for exercise 3

Show answer
=SUM(E2:E11)*(1+H3) = 2586.075. $1,724.05 of parts cost at 50% markup sells for $2,586.08.
4What is Dee's efficiency? Her clocked hours are in H7.

Your formula for exercise 4

Show answer
=SUMIF(B2:B11,"Dee",D2:D11)/H7 = 0.91111111111. 8.2 hours flagged on 9 clocked is 91.1%; the free comeback billed nothing.
5What is Marco's average billed hours per RO?

Your formula for exercise 5

Show answer
=AVERAGEIF(B2:B11,"Marco",D2:D11) = 3. Marco flagged 9 hours over 3 ROs: 3 hours each.
6What is the efficiency of the whole shop, using the clocked hours in H6:H8?

Your formula for exercise 6

Show answer
=SUM(D2:D11)/SUM(H6:H8) = 0.86274509804. 22 / 25.5 is 86.3%. Divide the totals, not the average of each tech's %, so busier techs weigh more.
7What was the labor gross profit for the day: labor sales minus tech pay (H4) on all clocked hours?

Your formula for exercise 7

Show answer
=SUM(D2:D11)*H2-SUM(H6:H8)*H4 = 2374. $3,190 of labor sales minus $816 of tech pay leaves $2,374.
8Which RO number had the biggest ticket (labor at H2 plus parts at the H3 markup)?

Your formula for exercise 8

Show answer
=INDEX(A2:A11,MATCH(MAX(D2:D11*H2+E2:E11*(1+H3)),D2:D11*H2+E2:E11*(1+H3),0)) = 4828. RO 4828, Dee's engine job: $652.50 of labor and $1,035 of parts, $1,687.50 in all.

Free templates for auto repair

Guided project: Shop parts reorder list

Turn 30 days of parts usage into reorder points and an order list with quantities and cost.

6 steps in the Cellwise app. Each step checks your formula in a real grid, with hints when you get stuck. Part of Cellwise Plus.

Open guided projects

Guides for these formulas

FAQ

How do I calculate average repair order (ARO) in Excel?

Divide total sales (labor plus parts) by the number of paying repair orders: =SUM(sales)/COUNTIF(comeback_column,"No"). Leave warranty and comeback visits out of the count so they do not drag the average down.

How do I calculate technician efficiency in Excel?

Divide hours flagged by hours clocked: =SUMIF(tech_column,"Marco",hours_column)/clocked_hours. Format it as a percentage. For the whole shop, divide total flagged by total clocked.

How do I add a parts markup in Excel?

Multiply the cost by 1 plus the markup: =cost*(1+50%). For tiered markups, put a sorted matrix of cost breaks and markups on the sheet and look the markup up with =VLOOKUP(cost,matrix,2,TRUE).

How do I calculate comeback rate?

Count the repair orders marked as comebacks and divide by all repair orders written in the same period: =COUNTIF(F2:F200,"Yes")/COUNTA(A2:A200). Track it monthly, and by technician, so a single bad day does not mislead you.

Excel for other fields

Updated November 2, 2026