# How to calculate a tip and split the bill in Excel

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)`.

**Excel:** `=ROUNDUP(B2*(1+$G$1)/C2,2)` (Every current version)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C | D | E | F | G |
| --- | --- | --- | --- | --- | --- | --- |
| Table | Bill | People | Each pays |  | Tip | 0.18 |
| T1 | 86.4 | 2 |  |  |  |  |
| T2 | 212.75 | 5 |  |  |  |  |
| T3 | 54.2 | 1 |  |  |  |  |
| T4 | 147.3 | 4 |  |  |  |  |
| T5 | 395.6 | 6 |  |  |  |  |

Formula in D2, filled to D6: `=ROUNDUP(B2*(1+$G$1)/C2,2)`

Result: $50.98; $50.21; $63.96; $43.46; $77.81

## 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.

## Other ways

- `=B2*(1+$G$1)/C2`: 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.

## 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)`.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/calculate-a-tip-and-split-the-bill