How to calculate a tip and split the bill in Excel
Formula (Excel and Google Sheets)
=ROUNDUP(B2*(1+$G$1)/C2,2)Add the tip to the bill and divide by the number of people, rounded up to the cent: =ROUNDUP(B2*(1+$G$1)/C2,2).
- Result on the example
- one result per row in
D2, filled down toD6 - Works in
- Every current version
- Google Sheets
- Same formula
See it work
The formula is already in D2, filled down to D6. Change any input and the result updates, or edit the formula to experiment.
How it works
B2*(1+$G$1) is the bill plus an 18% tip. Dividing by the number of people splits it, and ROUNDUP to 2 decimals rounds each share up to the next cent so the total never comes up short.
=ROUNDUP(B2*(1+$G$1)/C2,2)Result50.98
B2$86.40The value in B2.
$G$118%The value in G1.
1+$G$11.18The + operator adds.
B2*(1+$G$1101.952The * operator multiplies.
C22The value in C2.
B2*(1+$G$1)/C250.976The / operator divides.
ROUNDUP(B2*(1+$G$1)/C2,2)50.98ROUNDUP: Rounds away from zero.- number
B2*(1+$G$1)/C250.976 - num_digits
2
- number
In Google Sheets
The same formula works in Google Sheets: =ROUNDUP(B2*(1+$G$1)/C2,2). We ran it in Google Sheets mode on the same data and it returns the same results.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =B2*(1+$G$1)/C2 | $50.98, $50.21, $63.96…Different result | The exact share, without rounding. Shows fractions of a cent, such as 50.976. |
Common mistakes
- Tips are often calculated on the amount before tax. If the bill includes tax, use the pre-tax subtotal in the formula.
- Rounding each share down (or with ROUND) can leave the table a few cents short.
Try it yourself
Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.
Calculate a tip and split the bill
D2, work out what each person pays with the tip in G1, rounded up to the cent, then fill down to D6.Questions
How do I calculate just the tip?
Multiply the bill by the rate: =B2*$G$1.
How do I round each share up to the next dollar?
Use 0 decimals: =ROUNDUP(B2*(1+$G$1)/C2,0).
Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.