To invoice materials and labor in Excel, put them on the same invoice as separate lines. Price each material from what it cost you, with a markup (=ROUND(C3*(1+E3),2)) or a margin (=ROUND(C2/(1-E2),2)). Bill labor as hours times your rate. Add a Taxable column and charge tax only on the lines marked Yes, with =SUMIF(H2:H5,"Yes",G2:G5). Then keep your cost column off the copy the customer sees.
Most trade jobs mix the two: the parts you bought and the hours you worked. They are priced differently and often taxed differently, and the customer should see one clean total, never your cost or your markup. Here is the whole method on one example: a plumbing repair with two shutoff valves, two lengths of pipe, three hours of labor and a trip charge, at a 6.5% sales tax rate.
The table
One row per line on the invoice. Cost is what the item cost you (for labor, what an hour costs you), and the customer never sees it. Pricing says how you price the line, and Rate holds the markup, the margin or the fixed price. The tax rate, 6.5%, is in K1.
| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | Item | Qty | Cost | Pricing | Rate | Price | Amount | Taxable |
| 2 | Brass shutoff valve | 2 | 7.80 | Margin | 45% | 14.18 | 28.36 | Yes |
| 3 | 2in PVC pipe | 2 | 4.20 | Markup | 65% | 6.93 | 13.86 | Yes |
| 4 | Standard labor (hours) | 3 | 52.00 | Price | 85.00 | 85.00 | 255.00 | No |
| 5 | Trip charge | 1 | 18.00 | Price | 45.00 | 45.00 | 45.00 | No |
Step 1: Price materials from your cost
A markup is a percentage added on top of your cost. A margin is the share of the selling price you keep. Use whichever way you think about price, but put the right formula behind it:
Markup (F3)
=ROUND(C3*(1+E3),2)
$4.20 of pipe at a 65% markup sells for $6.93.
Margin (F2)
=ROUND(C2/(1-E2),2)
A $7.80 valve priced to keep a 45% margin sells for $14.18.
Markup is not margin
The same percentage gives a higher price as a margin: a 50% markup on a $10 part sells it for $15, and a 50% margin sells it for $20.
Step 2: Bill labor as hours times your rate
Put the hours in Qty and your hourly rate in Price, so labor is a line like any other. Every line’s amount is the same formula, filled down column G:
Amount (G2, filled down)
=B2*F2
Three hours of labor at $85 comes to $255.00.
Step 3: Charge tax only on the taxable lines
A tax formula on the whole subtotal taxes the labor too. Add up only the lines marked Yes, and tax that:
Subtotal (K2)
=SUM(G2:G5)
$342.22 for the whole job.
Taxable part (K3)
=SUMIF(H2:H5,"Yes",G2:G5)
$42.22: only the valves and the pipe.
Tax (K4)
=ROUND(K3*K1,2)
$2.74 at the 6.5% rate in K1.
Total (K5)
=K2+K4
$344.96, the amount on the invoice.
Without SUMIF
=SUMPRODUCT((H2:H5="Yes")*G2:G5) gives the same taxable part.
A discount, a different rate for one job and a tax-exempt customer each change the tax. They’re covered in how to charge sales tax on parts but not labor in Excel.
Step 4: Know what the job makes you
What the job cost you (K6)
=SUMPRODUCT(B2:B5,C2:C5)
$198.00: quantity times cost on every line.
Profit (K7)
=K2-K6
$144.22 on this job.
Margin (K8)
=K7/K2
42.1%: the share of the subtotal you keep.
Step 5: Keep your cost off the customer’s copy
Select the columns the customer should see (Item, Qty, Price, Amount and the totals), then choose Page Layout, Print Area, Set Print Area. Print or save that area as a PDF. The Cost, Pricing, Rate and Taxable columns stay in your file and never reach the customer.
Send the PDF, not the workbook
Hidden columns are one right-click from visible. If you email the .xlsx, your costs go with it. To stop anyone typing over the formulas in your own copy, see how to lock formulas and still allow data entry.
Common questions
Markup or margin: which should I use?
Use whichever way you think about price. Markup starts from cost (“I add 65%”). Margin starts from the price (“I keep 45% of what the customer pays”). The same percentage gives a higher price as a margin than as a markup: 50% on a $10 part is $15 as a markup and $20 as a margin.
Is labor taxable?
It depends on your state and the kind of work. Some states tax labor on some jobs and not on others, so check with your state’s revenue department or your accountant. The Taxable column lets you set it line by line, so the invoice follows whatever the rule is.
Should the customer see my markup?
Usually not: the invoice shows each line’s price, and the markup is inside it. Some contracts are cost-plus, where the markup is agreed up front. On those, show the cost and the markup as their own lines.
Materials and labor, without the formulas
EG Quote & Invoice does all five steps for you. It keeps each item’s cost and how you price it in a catalog, sets tax per line, shows your margin on your side of the screen only, and prints a customer’s copy with no cost and no markup. The video above shows it on this same job.
Invoice pages by trade: plumbing and general contractors.
See EG Quote & InvoiceWhen Formulas Aren’t Enough
These formulas cover one invoice. A job-costing system also needs:
- A price list that updates: change a supplier cost once and every new quote uses it
- Jobs across invoices: materials and hours added as the job runs, then billed in one go
- Progress billing: deposits and stage payments against one quoted total
- Reports: margin by customer, by trade or by month
A custom VBA system handles all of it inside Excel, built around the way you price and bill.
“Highly recommended, David was excellent throughout the entire process, taking the time to understand the brief and delivered exactly what was required.”
Bespoke Excel Client · Upwork
Need a Quoting and Invoicing Tool Built for Your Trade?
Tell us how you price materials and labor, and we’ll build a tool that handles it all.
Small fixes start at $69.
Every project gets a fixed-price quote before any work starts: the price you approve is the price you pay.