Simple job costing in a spreadsheet - see if each job actually made money (Excel or Google Sheets)
You finish a job, the customer pays, and the money is in the bank. But did that job actually make money?
For a lot of trades and service businesses, the honest answer is "probably". Materials get bought on the way to site. A helper comes in for a day. A subcontractor sends a bill two weeks later. By then, nobody adds it all up against the job.
This guide shows you how to build simple job costing in Excel or Google Sheets. You log every cost against a job ID, and the sheet tells you the profit and margin for each job. Everything works in both apps unless noted. It takes about 20 minutes.
Want a quick answer for one job? Try the free Job Profit Calculator.
What you will build
- A Jobs tab with one row per job and its quoted price.
- A Costs tab with one row per cost: materials, labour, subcontract or other.
- SUMIFS formulas that total the costs for each job, split by type.
- Profit and margin % for every job.
- Colour rules that flag jobs with a low margin or a loss.
- A quote vs actual check, so you can see where your quotes are off.
If you do not track your quotes yet, start with our quote-to-job tracker. When a quote is won, it becomes a row on the Jobs tab here.
Step 1. Set up the Jobs tab
Create a tab called Jobs and add these headers in row 1:
| A | B | C | D | E | F | G | H | I | J | K | L | M | N |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| Job ID | Customer | Job | Quoted price | Estimated cost | Materials | Labour | Subcontract | Other | Total cost | Profit | Margin | Over estimate by | Quoted margin |
You only type into columns A to E. The rest are formulas.
Tips:
- Give every job a short, unique ID, like J-1001, J-1002. You will type it on every cost, so keep it short.
- Quoted price is what the customer pays. If the price changes, update it here.
- Estimated cost is what you expected the job to cost when you wrote the quote. If you did not work that out, leave it blank.
- The headers in F1 to I1 must be spelled exactly Materials, Labour, Subcontract and Other. The formulas use them.
Step 2. Set up the Costs tab
Create a tab called Costs with these headers in row 1:
| A | B | C | D | E | F | G |
|---|---|---|---|---|---|---|
| Date | Job ID | Type | Description | Hours | Rate | Amount |
One row per cost. A receipt from the builders' merchant is one row. A day of your helper's time is one row. A subcontractor's bill is one row.
Add a dropdown to column C so the types are always spelled the same way:
Google Sheets: select C2:C2000. Choose Data > Data validation > Add rule. Under criteria, choose Dropdown and enter: Materials, Labour, Subcontract, Other.
Excel: select C2:C2000. Choose Data > Data Validation. Under Allow, choose List. In Source, enter Materials,Labour,Subcontract,Other.
You can do the same for column B, with a dropdown from the list of job IDs (Jobs!A2:A500 in Google Sheets, =Jobs!$A$2:$A$500 in Excel). Then nobody can type a job ID that does not exist.
Step 3. Work out labour as hours × rate
For materials, subcontract and other costs, type the amount from the receipt or bill into Amount.
For labour, fill in Hours and Rate, and let the sheet work out the amount. In the Amount cell of a labour row, enter:
=E2*F2
Change the row number to match the row you are on. For example, if the labour row is row 5, use =E5*F5.
Which rate should you use? Use what that hour of labour costs you: a helper's hourly pay, plus any extra costs you pay on top, if you know them. For your own time, pick an hourly rate you think your time is worth. If you leave your own time out, every job will look more profitable than it really is.
Step 4. Total the costs for each job with SUMIFS
Go back to the Jobs tab. In F2, paste:
=SUMIFS(Costs!$G$2:$G$2000,Costs!$B$2:$B$2000,$A2,Costs!$C$2:$C$2000,F$1)
Copy it across to I2, then copy F2:I2 down for all your jobs.
How to read it: add up the amounts on the Costs tab, but only where the job ID matches this row's job, and the type matches the header at the top of this column.
The $ signs matter. $A2 keeps the job ID column fixed when you copy across. F$1 keeps the header row fixed when you copy down. That is why one formula fills all four columns: Materials, Labour, Subcontract and Other.
Now the total cost. In J2, paste:
=SUMIFS(Costs!$G$2:$G$2000,Costs!$B$2:$B$2000,$A2)
This adds up every cost for the job, whatever the type. Copy it down.
Normally J2 is the same as F2 + G2 + H2 + I2. If it is bigger, some cost rows for that job have a blank or misspelled type. The dropdown from Step 2 prevents that.
Want totals by type across all jobs, for example how much you spent on materials this year? Put this anywhere on the Jobs tab:
=SUMIFS(Costs!$G$2:$G$2000,Costs!$C$2:$C$2000,"Materials")
Change "Materials" to "Labour", "Subcontract" or "Other" for the other types.
Step 5. Profit and margin for each job
In K2 (Profit), paste:
=IF(D2="","",D2-J2)
This is the quoted price minus the total cost. It stays blank until the job has a price.
In L2 (Margin), paste:
=IFERROR(K2/D2,"")
Format column L as a percentage. Margin is profit as a share of the price. A margin of 25% means you keep 25p, or 25c, of every pound or dollar the customer pays, after the job's own costs.
IFERROR keeps the cell blank when the price is 0 or empty. That happens with warranty callbacks or free return visits. You cannot divide by zero, but the Profit column still shows the loss.
Copy both formulas down.
One thing to watch: a job with no costs logged yet shows a margin of 100%. That does not mean it was free to do. It means nobody has entered the costs yet.
Step 6. Flag low-margin jobs with colour
Pick a margin you are happy with. This example uses 20%. Change it to your own target.
Select A2:N500 on the Jobs tab and add a conditional formatting rule that uses this formula:
=AND($L2<>"",$L2<0.2)
Give it a light orange fill.
Then add a second rule for jobs that lost money, with a light red fill:
=AND($K2<>"",$K2<0)
Google Sheets: choose Format > Conditional formatting. Under Format rules, choose Custom formula is and paste the formula. Pick the fill colour and click Done. Click Add another rule for the second one. Put the loss rule at the top of the list, so it wins when both match.
Excel: choose Home > Conditional Formatting > New Rule, then Use a formula to determine which cells to format. Paste the formula, click Format, and pick a fill colour on the Fill tab. Do the same for the second rule. Then open Home > Conditional Formatting > Manage Rules and move the loss rule to the top.
The $ before L and K makes the whole row change colour, not just one cell. $L2<>"" stops blank rows from being coloured.
Step 7. Compare the quote with what really happened
This is where job costing pays off. You can see which of your quotes are off, and by how much.
In M2 (Over estimate by), paste:
=IF(E2="","",J2-E2)
A positive number means the job cost more than you expected. A negative number means it came in under. Blank means you did not enter an estimate.
In N2 (Quoted margin), paste:
=IFERROR((D2-E2)/D2,"")
This is the margin you planned for when you wrote the quote. Put it next to the real margin in column L. If the quoted margin was 40% and the real one is 18%, something went wrong between the quote and the job.
Format column N as a percentage and copy both formulas down.
Look across the cost types too. If materials are close to your estimates but labour keeps running over, your hours are the thing to fix. If you want to see the hours themselves, add a column with the labour hours for each job:
=SUMIFS(Costs!$E$2:$E$2000,Costs!$B$2:$B$2000,$A2,Costs!$C$2:$C$2000,"Labour")
Compare it with the hours you had in mind when you priced the job.
Step 8. Check that every cost lands on a job
A cost with a typo in the job ID does not show up on any job. These checks catch that.
On the Costs tab, put this in H2 and copy it down:
=IF(B2="","",IF(COUNTIF(Jobs!$A$2:$A$500,B2)=0,"Check ID",""))
It says Check ID when the job ID is not on the Jobs tab.
And somewhere on the Jobs tab, compare two totals:
=SUM(Costs!G2:G2000)-SUM(J2:J500)
If this is not 0, some cost rows are not counted against any job. Look for Check ID on the Costs tab to find them.
Using it day to day
- When you win a job: add a row on the Jobs tab with a job ID, the price and your estimated cost.
- When you spend money on a job: add a row on the Costs tab, with the job ID. Snap the receipt so you can find it later.
- At the end of each day or week: log the hours worked on each job as Labour rows.
- When a job is finished: check its margin. If it is orange or red, look at which cost type ran over.
- Once a month: sort the Jobs tab by margin and look at the bottom five. Is it the same kind of job each time?
After a few months you will know which jobs are worth chasing and which ones you should price higher. Your own numbers are more useful than any general advice.
What else could you automate?
Job costing is a good next step after tracking quotes. Our free Small Business Automation Check asks about your country, industry and daily tasks, and shows which admin jobs are easiest to automate next.