Excel Challenge 41: Calculate Restaurant Bill Split & Tip

Challenge Scenario

In restaurant point-of-sale (POS) systems and dining hospitality management, service staff calculate gratuity tips, determine total bill charges, and evenly split the check among party guests.

Automating these calculations in a spreadsheet guarantees that tips are accurately accounted for and every guest pays their exact equal share without awkward rounding discrepancies.

As a Restaurant Cashier, you are balancing four dining table receipts at closing:

Tip (Column C): Calculate a standard 10% tip on Subtotal (Subtotal * 0.10).
Total Bill (Column D): Add the food Subtotal and Tip together (Subtotal + Tip).
Per Person (Column F): Divide Total Bill by the number of Guests seated at the table (Total Bill / Guests).
Total Shift Revenue (Cell D7): Sum all Total Bills across all tables using SUM(D2:D5).

Your Sheet Layout

Understanding how the restaurant billing sheet is organized:

Table Orders (Columns A & B): Identifies the table number (Table 1 to Table 4) and food/drink order subtotal ($60 to $220).
Gratuity & Total Charges (Columns C & D): Calculates 10% tip and total check amount.
Party Size & Bill Splitting (Columns E & F): Shows guest headcounts (2 to 4 guests) and calculates the individual share per person.
Shift Total Revenue (Cell D7): Grand total dining revenue across all tables ($561).

Cell Range	Column Name	Section	Formula / Approach	What It Does
`A2:B5`	Table ID, Subtotal	Table Orders	Table IDs and food subtotal amounts	Original dining bill order subtotals.
`C2:C5`	Tip (10%)	Calculation	`=B2 * 0.1`	Calculates 10% gratuity tip on subtotal.
`D2:D5`	Total Bill	Calculation	`=B2 + C2`	Adds food subtotal and gratuity tip together.
`E2:E5`	Guests	Party Size	Number of seated diners	Number of guests splitting the check.
`F2:F5`	Per Person	Calculation	`=D2 / E2`	Divides total bill evenly by number of guests.
`D7`	TOTAL BILLS REVENUE	Summary	`=SUM(D2:D5)`	Calculates total shift revenue across all tables ($561).

Challenge Objectives

Complete the objectives below in the live spreadsheet editor. Each objective card will turn green once your formula evaluates successfully.

Objective #1

Cell: C2-C5

In Column C (C2:C5), calculate 10% Tip using =B2*0.1.

Objective #2

Cell: D2-D5

In Column D (D2:D5), calculate Total Bill by adding Subtotal and Tip (=B2+C2).

Objective #3

Cell: F2-F5

In Column F (F2:F5), calculate Per Person amount by dividing Total Bill by Guests (=D2/E2).

Objective #4

Cell: D7

In cell D7, calculate total bills revenue using SUM(D2:D5).

Solution & Formula Breakdown

Let's examine how each arithmetic step is calculated row by row:

1. Calculating 10% Tip (C2:C5)

=B2 * 0.1

Multiply the Subtotal in Column B by 0.10:

Table 1: 80 * 0.10 = 8 → $8.00
Table 2: 150 * 0.10 = 15 → $15.00
Table 3: 220 * 0.10 = 22 → $22.00
Table 4: 60 * 0.10 = 6 → $6.00

2. Calculating Total Bill (D2:D5)

=B2 + C2

Add Subtotal and Tip together:

Table 1: 80 + 8 = 88 → $88.00
Table 2: 150 + 15 = 165 → $165.00
Table 3: 220 + 22 = 242 → $242.00
Table 4: 60 + 6 = 66 → $66.00

3. Splitting Evenly Per Person (F2:F5)

=D2 / E2

Divide Total Bill by the number of guests:

Table 1 (2 guests): 88 / 2 = 44 → $44.00 per person
Table 2 (3 guests): 165 / 3 = 55 → $55.00 per person
Table 3 (4 guests): 242 / 4 = 60.5 → $60.50 per person
Table 4 (2 guests): 66 / 2 = 33 → $33.00 per person

4. Shift Total Revenue Summary (Cell D7)

=SUM(D2:D5)

Adding all four table checks (88 + 165 + 242 + 66) gives the grand shift revenue of $561.00.

You can also calculate Total Bill directly in one formula using multiplication: =B2 * 1.10. Multiplying by 1.10 automatically includes 100% of the subtotal plus the 10% tip!