Split a restaurant bill with tip in a couple of cells. Add the tip to the total, divide by the number of people, and optionally round each share up so the tip is never short.
The example
A $120 bill, 18% tip, split 4 ways.
| A | B | |
|---|---|---|
| 1 | Bill | $120.00 |
| 2 | Tip 18% | $21.60 |
| 3 | Total | $141.60 |
| 4 | Each (of 4) | $35.40 |
The formula
Per-person share with tip:
How it works
Three steps folded into one formula:
- Add the tip by multiplying the bill by
(1 + tipRate)—1.18for 18%. - Divide by the number of people for each share.
- Want to tip on the pre-tax amount? Compute the tip on the subtotal, then add tax separately.
- To make sure the pooled tip is never short, round each share up with ROUNDUP (see the variation).
Round up so you never under-tip: =ROUNDUP(B1*(1+B2)/B3, 2) rounds each share up to the cent; with a few people that pads the tip by a few cents total — better than coming up short. Round to the dollar with ROUNDUP(…, 0) for easy cash splits.
Try it: interactive demo
Set bill, tip %, and people.
Variations
Round up each share
Never under-tip:
Tip on pre-tax
Tip the subtotal, add tax after:
Just the tip
Total gratuity:
Pitfalls & errors
Zero people divides by zero. Guard the headcount or ensure it’s at least 1.
Tip base. Tipping on the post-tax total tips a bit more than tipping on the subtotal. Decide which you mean.
Rounding direction. Plain ROUND can make the shares sum to slightly less than the total; ROUNDUP guarantees you cover it.
Practice workbook
Frequently asked questions
How do I split a bill with tip in Excel?
How do I make sure the tip isn't short when splitting?
Should I tip on the pre-tax or post-tax amount?
Stop fighting formulas. Learn them in a day.
This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.
See the Formulas & Functions Class